不产生UNDO的情况
2024-07-21 02:34:16
供稿:网友
以下操作不产生UNDO或产生很少的undo1 Read-Only transaction2Direct Path 数据导入3对临时段操作,如排序等。其他情况,因为要保证一致读,都要产生必要的undo以下操作不产生UNDO1 Read-Only transaction SQL> set transaction read only;Transaction set.SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddrfrom v$session where sid=(select sid from v$mystat where rownum=1));no rows selectedSQL> insert into test select * from t;insert into test select * from t *ERROR at line 1:ORA-01456: may not perform insert/delete/update Operation inside a READ ONLYtransactionSQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddrfrom v$session where sid=(select sid from v$mystat where rownum=1));no rows selected2Direct Path 数据导入SQL> create table test as select * from t where 1=0;Table created.SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddrfrom v$session where sid=(select sid from v$mystat where rownum=1));no rows selectedSQL> insert /*+ append */ into test select * from t;58842 rows created.SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec ,used_ublk,used_urec from v$transaction where ses_addr=(select saddr from v$session where sid=(select sid from v$mystat where rownum=1)); XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC---------- ---------- ---------- ---------- ---------- ---------- ---------- USED_UBLK USED_UREC---------- ---------- 2 33 10273 0 0 0 0 1 1SQL> commit;Commit complete.SQL> insert into test select * from t;58842 rows created.SQL> select used_ublk,used_urec from v$transaction where ses_addr=(select saddrfrom v$session where sid=(select sid from v$mystat where rownum=1)); USED_UBLK USED_UREC---------- ---------- 27 1482
Append插入数据未提交时候,dump回滚段头Start dump data blocks tsn: 1 file#: 2 minblk 25 maxblk 25buffer tsn: 1 rdba: 0x00800019 (2/25)scn: 0x0000.00984d1c seq: 0x01 flg: 0x04 tail: 0x4d1c2601frmt: 0x02 chkval: 0xaf28 type: 0x26=KTU SMU HEADER BLOCK TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num ------------------------------------------------------------------------------------------------ 0x21 10 0x80 0x2821 0x0002 0x0000.00984d1c 0x00000000 0x0000.000.00000000 0x00000000 0x00000000End dump data blocks tsn: 1 file#: 2 minblk 25 maxblk 25 没有为事务分配undo block. 再Dump表头Start dump data blocks tsn: 9 file#: 9 minblk 395 maxblk 395buffer tsn: 9 rdba: 0x0240018b (9/395)scn: 0x0000.00984d1c seq: 0x01 flg: 0x00 tail: 0x4d1c2301frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 28 #blocks: 1664 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x0240018c ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x0240018c ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x02400189 Level 1 BMB for Low HWM block: 0x02400189 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x0240018a Last Level 1 BMB: 0x02400c8a Last Level II BMB: 0x0240018a Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 28 obj#: 30508 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x02400189 length: 8 0x024005d1 length: 8 0x024005d9 length: 8 0x024005e1 length: 8 0x024005e9 length: 8 0x024005f1 length: 8 0x024005f9 length: 8 0x02400601 length: 8 0x02400609 length: 8 0x02400611 length: 8 0x02400619 length: 8 0x02400621 length: 8 0x02400629 length: 8 0x02400631 length: 8 0x02400639 length: 8 0x02400641 length: 8 0x02400689 length: 128 0x02400709 length: 128 0x02400789 length: 128 0x02400809 length: 128 0x02400889 length: 128 0x02400909 length: 128 0x02400989 length: 128 0x02400a09 length: 128 0x02400a89 length: 128 0x02400b09 length: 128 0x02400c09 length: 128 0x02400c89 length: 128 Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x02400189 Data dba: 0x0240018c Extent 1 : L1 dba: 0x02400189 Data dba: 0x024005d1 Extent 2 : L1 dba: 0x024005d9 Data dba: 0x024005da Extent 3 : L1 dba: 0x024005d9 Data dba: 0x024005e1 Extent 4 : L1 dba: 0x024005e9 Data dba: 0x024005ea Extent 5 : L1 dba: 0x024005e9 Data dba: 0x024005f1 Extent 6 : L1 dba: 0x024005f9 Data dba: 0x024005fa Extent 7 : L1 dba: 0x024005f9 Data dba: 0x02400601 Extent 8 : L1 dba: 0x02400609 Data dba: 0x0240060a Extent 9 : L1 dba: 0x02400609 Data dba: 0x02400611
Extent 10 : L1 dba: 0x02400619 Data dba: 0x0240061a Extent 11 : L1 dba: 0x02400619 Data dba: 0x02400621 Extent 12 : L1 dba: 0x02400629 Data dba: 0x0240062a Extent 13 : L1 dba: 0x02400629 Data dba: 0x02400631 Extent 14 : L1 dba: 0x02400639 Data dba: 0x0240063a Extent 15 : L1 dba: 0x02400639 Data dba: 0x02400641 Extent 16 : L1 dba: 0x02400689 Data dba: 0x0240068b Extent 17 : L1 dba: 0x02400709 Data dba: 0x0240070b Extent 18 : L1 dba: 0x02400789 Data dba: 0x0240078b Extent 19 : L1 dba: 0x02400809 Data dba: 0x0240080b Extent 20 : L1 dba: 0x02400889 Data dba: 0x0240088b Extent 21 : L1 dba: 0x02400909 Data dba: 0x0240090b Extent 22 : L1 dba: 0x02400989 Data dba: 0x0240098b Extent 23 : L1 dba: 0x02400a09 Data dba: 0x02400a0b Extent 24 : L1 dba: 0x02400a89 Data dba: 0x02400a8b Extent 25 : L1 dba: 0x02400b09 Data dba: 0x02400b0b Extent 26 : L1 dba: 0x02400c09 Data dba: 0x02400c0b Extent 27 : L1 dba: 0x02400c89 Data dba: 0x02400c8b -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x0240018a End dump data blocks tsn: 9 file#: 9 minblk 395 maxblk 395 发现High Water Mark 并没有提高。 此时,在其他session执行 SQL> insert into test select * from t where rownum<10; 该insert 被锁住 察看锁信息SQL> select a.sid,b.sid,a.type,(select object_name from dba_objects where object_id=a.id1) object,a.lmode,b.request,a.block from v$lock a,v$Lock b where a.id1=b.id1 and a.id2=b.id2 and b.request>0 and a.block>0;Lock Session TYPE OBJECT LMODE REQUEST BLOCK
---------- ---------- -------------------- ---------- -------------------- ---------- ---------- 11 block 16 TM TEST 6 3 1 Direct Load Data的时候在表上加了绝对锁('Exclusive'),保证High Water Mark不被其他session修改;因High Water Mark不变,也就不会影响其他session一致读,也就不需要产生很多的undo。SQL> truncate table test;Table truncated.SQL> insert /*+ append */ into test select * from t where rownum<10;9 rows created.SQL> select * from test;select * from test *ERROR at line 1:ORA-12838: cannot read/modify an object after modifying it in parallelSQL> delete from test;delete from test *ERROR at line 1:ORA-12838: cannot read/modify an object after modifying it in parallel 同时这也是为什么当前session也无法对表进行查询和修改的原因。 3对临时段操作,如排序等。