【IT168 服务器学院】以下操作不产生UNDO或产生很少的undo
1 Read-Only transaction
2Direct Path 数据导入
3对临时段操作，如排序等。
其他情况，因为要保证一致读，都要产生必要的undo
以下操作不产生UNDO
1 Read-Only transaction
SQL> set transaction read only;
Transaction set.
SQL> select 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));
no rows selected
SQL> 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 ONLY
transaction
SQL> select 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));
no rows selected
2Direct 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 saddr
from v$session where sid=(select sid from v$mystat where rownum=1));
no rows selected
SQL> insert /*+ append */ into test select * from t;
58842 rows created.
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec ,used_ublk,used_ur
ec from v$transaction where ses_addr=(select saddr from v$session where sid=(sel
ect 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 1
SQL> 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 saddr
from 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 25
buffer tsn: 1 rdba: 0x00800019 (2/25)
scn: 0x0000.00984d1c seq: 0x01 flg: 0x04 tail: 0x4d1c2601
frmt: 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 0x00000000
End 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 395
buffer tsn: 9 rdba: 0x0240018b (9/395)
scn: 0x0000.00984d1c seq: 0x01 flg: 0x00 tail: 0x4d1c2301
frmt: 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 parallel
SQL> delete from test;
delete from test *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
同时这也是为什么当前session也无法对表进行查询和修改的原因。
3对临时段操作，如排序等。
