不同insert操作產生的undo的測試

zhouwf0726發表於2019-02-27

對於http://space.itpub.net/756652/viewspace-242408 這個以前的一個測試地址,有人會問,如果批量insert只有1條或者少數幾條undo,那麼如何回滾所有資料?下面給出些測試說明這個問題。

 

SYS.YUDONG>conn zwf/zwf
Connected.

1 row selected.

SYS.YUDONG>select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

SPID
------------
2203

1 row selected.

ZWF.YUDONG>create table t(id number);

Table created.

ZWF.YUDONG>select owner,segment_name,file_id,block_id from dba_extents where wner='ZWF' and segment_name='T';

OWNER                          SEGMENT_NAME                                                                         FILE_ID   BLOCK_ID
------------------------------ --------------------------------------------------------------------------------- ---------- ----------
ZWF                            T                                                                                         20       3649

1 row selected.

 


session 1:

ZWF.YUDONG>insert into t values(1);

1 row created.

ZWF.YUDONG>select sys.dbms_rowid.ROWID_RELATIVE_FNO(rowid),sys.dbms_rowid.ROWID_BLOCK_NUMBER(rowid),id from t;

SYS.DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) SYS.DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)         ID
---------------------------------------- ---------------------------------------- ----------
                                      20                                     3650          1

1 row selected.

ZWF.YUDONG>select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,used_ublk,used_urec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC  USED_UBLK  USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        10         26      34383          2        734       1565         23          1          1

1 row selected.

 


Dump file /dumps-01/databases/yudong/udump/yudong_ora_2203.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0.2/A11db
System name: Linux
Node name: db-tfusn2cn-a-r1.amazon.com
Release: 2.4.21-37a6smp
Version: #1 SMP Thu May 25 13:59:02 PDT 2006
Machine: i686
Instance name: yudong
Redo thread mounted by this instance: 1
Oracle process number: 37
Unix process pid: 2203, image: oracle@db-tfusn2cn-a-r1.amazon.com (TNS V1-V3)

*** 2008-04-21 12:28:57.856
*** ACTION NAME:() 2008-04-21 12:28:57.856
*** MODULE NAME:(SQL*Plus) 2008-04-21 12:28:57.856
*** SERVICE NAME:(SYS$USERS) 2008-04-21 12:28:57.856
*** SESSION ID:(590.318) 2008-04-21 12:28:57.856
Start dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650
buffer tsn: 25 rdba: 0x05000e42 (20/3650)
scn: 0x0000.165af2c8 seq: 0x04 flg: 0x00 tail: 0xf2c80604
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200
D2E4200 0000A206 05000E42 165AF2C8 00040000  [....B.....Z.....]
D2E4210 00000000 00000001 0001189D 165AF2C8  [..............Z.]
D2E4220 00000000 00030002 00000000 001A000A  [................]
D2E4230 0000864F 008002DE 0017061D 00000001  [O...............]
D2E4240 00000000 00000000 00000000 00000000  [................]
D2E4250 00000000 00000000 00000000 00010100  [................]
D2E4260 0014FFFF 1F831F9A 00001F83 1F9A0001  [................]
D2E4270 00000FFF 00000000 00000000 00000000  [................]
D2E4280 00000000 00000000 00000000 00000000  [................]
        Repeat 250 times
D2E5230 00000000 00010000 00010001 00000001  [................]
D2E5240 00000000 00000001 0040000A 0040000A  [..........@...@.]
D2E5250 00000000 00000000 00000000 00000000  [................]
        Repeat 249 times
D2E61F0 00000000 012C0000 02C10201 F2C80604  [......,.........]
Block header dump:  0x05000e42
 Object id on Block? Y
 seg/obj: 0x1189d  csc: 0x00.165af2c8  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.01a.0000864f  0x008002de.061d.17  ----    1  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 
data_block_dump,data header at 0xd2e425c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0d2e425c
bdba: 0x05000e42
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 02
end_of_block_dump
End dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650

 


session 2:

ZWF.YUDONG>begin
for i in 1..10 loop
insert into t values(2);
end loop;
end;
/
  2    3    4    5    6
PL/SQL procedure successfully completed.

ZWF.YUDONG>select sys.dbms_rowid.ROWID_RELATIVE_FNO(rowid),sys.dbms_rowid.ROWID_BLOCK_NUMBER(rowid),id from t;

SYS.DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) SYS.DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)         ID
---------------------------------------- ---------------------------------------- ----------
                                      20                                     3650          2
                                      20                                     3650          2
                                      20                                     3650          2
                                      20                                     3650          2
                                      20                                     3650          2
                                      20                                     3650          2
                                      20                                     3650          2
                                      20                                     3650          2
                                      20                                     3650          2
                                      20                                     3650          2

10 rows selected.

ZWF.YUDONG>select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,used_ublk,used_urec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC  USED_UBLK  USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         8          6      33665          2      25033       1922         32          1         10
        10         26      34383          2        734       1565         23          1          1

2 rows selected.


*** 2008-04-21 12:34:45.815
Start dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650
buffer tsn: 25 rdba: 0x05000e42 (20/3650)
scn: 0x0000.165af62b seq: 0x01 flg: 0x00 tail: 0xf62b0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200
D2E4200 0000A206 05000E42 165AF62B 00010000  [....B...+.Z.....]
D2E4210 00000000 00000001 0001189D 165AF62B  [............+.Z.]
D2E4220 00000000 00030002 00000000 001A000A  [................]
D2E4230 0000864F 008002DE 0017061D 00000001  [O...............]
D2E4240 00000000 00060008 00008381 008061C9  [.............a..]
D2E4250 00200782 0000000A 00000000 000B0100  [.. .............]
D2E4260 0028FFFF 1F151F5E 00001F15 1F9A000B  [..(.^...........]
D2E4270 1F8E1F94 1F821F88 1F761F7C 1F6A1F70  [........|.v.p.j.]
D2E4280 1F5E1F64 00000000 00000000 00000000  [d.^.............]
D2E4290 00000000 00000000 00000000 00000000  [................]
        Repeat 249 times
D2E5230 00000000 00010000 00010001 00000001  [................]
D2E5240 00000000 00000001 0040000A 0040000A  [..........@...@.]
D2E5250 00000000 00000000 00000000 00000000  [................]
        Repeat 245 times
D2E61B0 00000000 00000000 022C0000 03C10201  [..........,.....]
D2E61C0 0201022C 022C03C1 03C10201 0201022C  [,.....,.....,...]
D2E61D0 022C03C1 03C10201 0201022C 022C03C1  [..,.....,.....,.]
D2E61E0 03C10201 0201022C 022C03C1 03C10201  [....,.....,.....]
D2E61F0 0201022C 012C03C1 02C10201 F62B0601  [,.....,.......+.]
Block header dump:  0x05000e42
 Object id on Block? Y
 seg/obj: 0x1189d  csc: 0x00.165af62b  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.01a.0000864f  0x008002de.061d.17  ----    1  fsc 0x0000.00000000
0x02   0x0008.006.00008381  0x008061c9.0782.20  ----   10  fsc 0x0000.00000000
 
data_block_dump,data header at 0xd2e425c
===============
tsiz: 0x1fa0
hsiz: 0x28
pbl: 0x0d2e425c
bdba: 0x05000e42
     76543210
flag=--------
ntab=1
nrow=11
frre=-1
fsbo=0x28
fseo=0x1f5e
avsp=0x1f15
tosp=0x1f15
0xe:pti[0] nrow=11 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
0x16:pri[2] offs=0x1f8e
0x18:pri[3] offs=0x1f88
0x1a:pri[4] offs=0x1f82
0x1c:pri[5] offs=0x1f7c
0x1e:pri[6] offs=0x1f76
0x20:pri[7] offs=0x1f70
0x22:pri[8] offs=0x1f6a
0x24:pri[9] offs=0x1f64
0x26:pri[10] offs=0x1f5e
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 02
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 2, @0x1f8e
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 3, @0x1f88
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 4, @0x1f82
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 5, @0x1f7c
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 6, @0x1f76
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 7, @0x1f70
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 8, @0x1f6a
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 9, @0x1f64
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 10, @0x1f5e
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
end_of_block_dump
End dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650


session 3:

ZWF.YUDONG>insert into t select 3 from dual connect by rownum<=10;

10 rows created.

ZWF.YUDONG>select sys.dbms_rowid.ROWID_RELATIVE_FNO(rowid),sys.dbms_rowid.ROWID_BLOCK_NUMBER(rowid),id from t;

SYS.DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) SYS.DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)         ID
---------------------------------------- ---------------------------------------- ----------
                                      20                                     3650          3
                                      20                                     3650          3
                                      20                                     3650          3
                                      20                                     3650          3
                                      20                                     3650          3
                                      20                                     3650          3
                                      20                                     3650          3
                                      20                                     3650          3
                                      20                                     3650          3
                                      20                                     3650          3

10 rows selected.

ZWF.YUDONG>select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,used_ublk,used_urec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC  USED_UBLK  USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         3         47     110659          2      21319       7704         23          1          1
         8          6      33665          2      25033       1922         32          1         10
        10         26      34383          2        734       1565         23          1          1

3 rows selected.


*** 2008-04-21 12:36:52.655
Start dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650
buffer tsn: 25 rdba: 0x05000e42 (20/3650)
scn: 0x0000.165af7fa seq: 0x01 flg: 0x00 tail: 0xf7fa0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200
D2E4200 0000A206 05000E42 165AF7FA 00010000  [....B.....Z.....]
D2E4210 00000000 00000001 0001189D 165AF7FA  [..............Z.]
D2E4220 00000000 00030003 00000000 001A000A  [................]
D2E4230 0000864F 008002DE 0017061D 00000001  [O...............]
D2E4240 00000000 00060008 00008381 008061C9  [.............a..]
D2E4250 00200782 0000000A 00000000 002F0003  [.. .........../.]
D2E4260 0001B043 00805347 00171E18 0000000A  [C...GS..........]
D2E4270 00000000 00150100 003CFFFF 1E8F1F0A  [..........<.....>D2E4280 00001E8F 1F820015 1F761F7C 1F6A1F70  [........|.v.p.j.]
D2E4290 1F5E1F64 1F521F58 1F461F4C 1F101F0A  [d.^.X.R.L.F.....]
D2E42A0 1F1C1F16 1F281F22 1F341F2E 1F401F3A  [....".(...4.:.@.]
D2E42B0 00000000 00000000 00000000 00000000  [................]
        Repeat 247 times
D2E5230 00000000 00010000 00010001 00000001  [................]
D2E5240 00000000 00000001 0040000A 0040000A  [..........@...@.]
D2E5250 00000000 00000000 00000000 00000000  [................]
        Repeat 241 times
D2E6170 00000000 00000000 00000000 032C0000  [..............,.]
D2E6180 04C10201 0201032C 032C04C1 04C10201  [....,.....,.....]
D2E6190 0201032C 032C04C1 04C10201 0201032C  [,.....,.....,...]
D2E61A0 032C04C1 04C10201 0201032C 032C04C1  [..,.....,.....,.]
D2E61B0 04C10201 0201032C 022C04C1 03C10201  [....,.....,.....]
D2E61C0 0201022C 022C03C1 03C10201 0201022C  [,.....,.....,...]
D2E61D0 022C03C1 03C10201 0201022C 022C03C1  [..,.....,.....,.]
D2E61E0 03C10201 0201022C 022C03C1 03C10201  [....,.....,.....]
D2E61F0 0201022C 012C03C1 02C10201 F7FA0601  [,.....,.........]
Block header dump:  0x05000e42
 Object id on Block? Y
 seg/obj: 0x1189d  csc: 0x00.165af7fa  itc: 3  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.01a.0000864f  0x008002de.061d.17  ----    1  fsc 0x0000.00000000
0x02   0x0008.006.00008381  0x008061c9.0782.20  ----   10  fsc 0x0000.00000000
0x03   0x0003.02f.0001b043  0x00805347.1e18.17  ----   10  fsc 0x0000.00000000
 
data_block_dump,data header at 0xd2e4274
===============
tsiz: 0x1f88
hsiz: 0x3c
pbl: 0x0d2e4274
bdba: 0x05000e42
     76543210
flag=--------
ntab=1
nrow=21
frre=-1
fsbo=0x3c
fseo=0x1f0a
avsp=0x1e8f
tosp=0x1e8f
0xe:pti[0] nrow=21 offs=0
0x12:pri[0] offs=0x1f82
0x14:pri[1] offs=0x1f7c
0x16:pri[2] offs=0x1f76
0x18:pri[3] offs=0x1f70
0x1a:pri[4] offs=0x1f6a
0x1c:pri[5] offs=0x1f64
0x1e:pri[6] offs=0x1f5e
0x20:pri[7] offs=0x1f58
0x22:pri[8] offs=0x1f52
0x24:pri[9] offs=0x1f4c
0x26:pri[10] offs=0x1f46
0x28:pri[11] offs=0x1f0a
0x2a:pri[12] offs=0x1f10
0x2c:pri[13] offs=0x1f16
0x2e:pri[14] offs=0x1f1c
0x30:pri[15] offs=0x1f22
0x32:pri[16] offs=0x1f28
0x34:pri[17] offs=0x1f2e
0x36:pri[18] offs=0x1f34
0x38:pri[19] offs=0x1f3a
0x3a:pri[20] offs=0x1f40
block_row_dump:
tab 0, row 0, @0x1f82
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 02
tab 0, row 1, @0x1f7c
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 2, @0x1f76
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 3, @0x1f70
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 4, @0x1f6a
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 5, @0x1f64
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 6, @0x1f5e
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 7, @0x1f58
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 8, @0x1f52
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 9, @0x1f4c
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 10, @0x1f46
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 03
tab 0, row 11, @0x1f0a
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
tab 0, row 12, @0x1f10
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
tab 0, row 13, @0x1f16
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
tab 0, row 14, @0x1f1c
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
tab 0, row 15, @0x1f22
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
tab 0, row 16, @0x1f28
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
tab 0, row 17, @0x1f2e
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
tab 0, row 18, @0x1f34
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
tab 0, row 19, @0x1f3a
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
tab 0, row 20, @0x1f40
tl: 6 fb: --H-FL-- lb: 0x3  cc: 1
col  0: [ 2]  c1 04
end_of_block_dump
End dump data blocks tsn: 25 file#: 20 minblk 3650 maxblk 3650

 

 

我們主要分析itl中對應的transaction 8 和 3 的undo record的差異:


 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.01a.0000864f  0x008002de.061d.17  ----    1  fsc 0x0000.00000000
0x02   0x0008.006.00008381  0x008061c9.0782.20  ----   10  fsc 0x0000.00000000
0x03   0x0003.02f.0001b043  0x00805347.1e18.17  ----   10  fsc 0x0000.00000000

ZWF.YUDONG>select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,used_ublk,used_urec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC  USED_UBLK  USED_UREC
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         3         47     110659          2      21319       7704         23          1          1
         8          6      33665          2      25033       1922         32          1         10
        10         26      34383          2        734       1565         23          1          1

3 rows selected.


ZWF.YUDONG>select * from v$rollname where usn in (3,8);

       USN NAME
---------- ------------------------------
         3 _SYSSMU3$
         8 _SYSSMU8$

2 rows selected.

ZWF.YUDONG>alter system dump undo header '_SYSSMU3$';

System altered.

ZWF.YUDONG>alter system dump undo header '_SYSSMU8$';

System altered.

 

********************************************************************************
Undo Segment:  _SYSSMU8$ (8)
********************************************************************************
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 16     #blocks: 1807 
                  last map  0x00000000  #maps: 0      offset: 4080 
      Highwater::  0x008061c9  ext#: 7      blk#: 64     ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 0    
  mapblk  0x00000000  offset: 7    
                   Unlocked
     Map Header:: next  0x00000000  #extents: 16   obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0080007a  length: 7    
   0x00803899  length: 8    
   0x00800689  length: 128  
   0x00805889  length: 128  
   0x00803489  length: 128  
   0x00801709  length: 128  
   0x00803a89  length: 128  
   0x00806189  length: 128  
   0x00804509  length: 128  
   0x00803b89  length: 128  
   0x00802c89  length: 128  
   0x00804709  length: 128  
   0x00802589  length: 128  
   0x00803e89  length: 128  
   0x00803f09  length: 128  
   0x00804b89  length: 128  
 
 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1208693578
 Extent Number:1  Commit Time: 1208694673
 Extent Number:2  Commit Time: 1208712469
 Extent Number:3  Commit Time: 1208730814
 Extent Number:4  Commit Time: 1208743237
 Extent Number:5  Commit Time: 1208761498
 Extent Number:6  Commit Time: 1208776740
 Extent Number:7  Commit Time: 1208608139
 Extent Number:8  Commit Time: 1208625144
 Extent Number:9  Commit Time: 1208641460
 Extent Number:10  Commit Time: 1208658434
 Extent Number:11  Commit Time: 1208667658
 Extent Number:12  Commit Time: 1208667664
 Extent Number:13  Commit Time: 1208667666
 Extent Number:14  Commit Time: 1208678288
 Extent Number:15  Commit Time: 1208692832
 
  TRN CTL:: seq: 0x0782 chd: 0x002b ctl: 0x0027 inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x008061c9.0782.17 scn: 0x0000.165af28d
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.0782.16 ext: 0x7  spc: 0x13b6 
    uba: 0x00000000.0782.28 ext: 0x7  spc: 0x10fe 
    uba: 0x00000000.0781.28 ext: 0x6  spc: 0x27e  
    uba: 0x00000000.01cb.22 ext: 0x7  spc: 0x123a 
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0    
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x837e  0x001c  0x0000.165af51f  0x008061c8  0x0000.000.00000000  0x00000001   0x00000000  1208781120
   0x01    9    0x00  0x8380  0x000b  0x0000.165af4f7  0x008061c8  0x0000.000.00000000  0x00000001   0x00000000  1208781105
   0x02    9    0x00  0x8382  0x0016  0x0000.165af2b1  0x008061c7  0x0000.000.00000000  0x00000001   0x00000000  1208780900
   0x03    9    0x00  0x8381  0x002e  0x0000.165af398  0x008061c7  0x0000.000.00000000  0x00000001   0x00000000  1208780981
   0x04    9    0x00  0x8381  0x000c  0x0000.165af4b4  0x008061c8  0x0000.000.00000000  0x00000001   0x00000000  1208781080
   0x05    9    0x00  0x8383  0x0014  0x0000.165af422  0x008061c8  0x0000.000.00000000  0x00000001   0x00000000  1208781034
   0x06   10    0x80  0x8381  0x0007  0x0000.165af607  0x008061c9  0x0000.000.00000000  0x00000001   0x00000000  0            --狀態為10的為當前活動事務
   0x07    9    0x00  0x8382  0x0027  0x0000.165af5ee  0x008061c9  0x0000.000.00000000  0x00000001   0x00000000  1208781194
   0x08    9    0x00  0x8383  0x0011  0x0000.165af4d4  0x008061c8  0x0000.000.00000000  0x00000001   0x00000000  1208781094
   。。。。。。
   0x2e    9    0x00  0x8382  0x0013  0x0000.165af3a0  0x008061c7  0x0000.000.00000000  0x00000001   0x00000000  1208780984
   0x2f    9    0x00  0x8380  0x0000  0x0000.165af515  0x008061c8  0x0000.000.00000000  0x00000001   0x00000000  1208781115

********************************************************************************
Undo Segment:  _SYSSMU3$ (3)
********************************************************************************
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 56     #blocks: 2607 
                  last map  0x00000000  #maps: 0      offset: 4080 
      Highwater::  0x00805347  ext#: 55     blk#: 62     ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 0    
  mapblk  0x00000000  offset: 55   
                   Unlocked
     Map Header:: next  0x00000000  #extents: 56   obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0080002a  length: 7    
   0x00800a89  length: 8    
   0x00801609  length: 128  
   0x00802509  length: 128  
   0x00802089  length: 128  
   0x00802209  length: 128  
   0x00804c89  length: 128  
   0x00800a09  length: 128  
   0x00804389  length: 128  
   0x00806209  length: 128  
   0x00805989  length: 128  
   0x00805a09  length: 128  
   0x00805c89  length: 128  
   0x00806309  length: 128  
   0x00800041  length: 8    
   0x00800051  length: 8    
   ......   
   0x00801c91  length: 8    
   0x00801c99  length: 8    
   0x00801ca1  length: 8    
   0x00805289  length: 128  
   0x00800e09  length: 128  
   0x00802009  length: 128  
   0x00803a09  length: 128  
   0x00802c09  length: 128  
   0x00805309  length: 128  
 
 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1208589051
 Extent Number:1  Commit Time: 1208590410
 Extent Number:2  Commit Time: 1208604784
 。。。。。。
 Extent Number:53  Commit Time: 1208752899
 Extent Number:54  Commit Time: 1208768590
 Extent Number:55  Commit Time: 1208768590
 
  TRN CTL:: seq: 0x1e18 chd: 0x002d ctl: 0x001b inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00805347.1e18.17 scn: 0x0000.165af574
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.1e18.16 ext: 0x37 spc: 0x138e 
    uba: 0x00000000.1e17.02 ext: 0x36 spc: 0x1f06 
    uba: 0x00000000.1e18.07 ext: 0x37 spc: 0x1d00 
    uba: 0x00000000.1c21.0d ext: 0x3  spc: 0x1750 
    uba: 0x00000000.004f.51 ext: 0x4f spc: 0x474  
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x1b04a  0x002e  0x0000.165af6a1  0x00805346  0x0000.000.00000000  0x00000001   0x00000000  1208781255
   ......
   0x2d    9    0x00  0x1b044  0x0020  0x0000.165af58d  0x00805345  0x0000.000.00000000  0x00000001   0x00000000  1208781160
   0x2e    9    0x00  0x1b042  0x0022  0x0000.165af6bf  0x00805346  0x0000.000.00000000  0x00000001   0x00000000  1208781265
   0x2f   10    0x80  0x1b043  0x0037  0x0000.165af7e8  0x00805347  0x0000.000.00000000  0x00000001   0x00000000  0  --狀態為10的為當前活動事務


根據dba dump下undo的block:

ZWF.YUDONG>declare
  2     v_file number;
  3     v_block number;
  4  begin
  5     v_file := dbms_utility.data_block_address_file(to_number('008061c9','xxxxxxxx'));
  6     v_block := dbms_utility.data_block_address_block(to_number('008061c9','xxxxxxxx'));
  7     dbms_output.put_line('file id: '||v_file);
  8     dbms_output.put_line('block id: '||v_block);
  9     end;
 10  /
file id: 2
block id: 25033

PL/SQL procedure successfully completed.

ZWF.YUDONG>declare
  2     v_file number;
  3     v_block number;
  4  begin
  5     v_file := dbms_utility.data_block_address_file(to_number('00805347','xxxxxxxx'));
  6     v_block := dbms_utility.data_block_address_block(to_number('00805347','xxxxxxxx'));
  7     dbms_output.put_line('file id: '||v_file);
  8     dbms_output.put_line('block id: '||v_block);
  9     end;
 10  /
file id: 2
block id: 21319

PL/SQL procedure successfully completed.


ZWF.YUDONG>alter system dump datafile 2 block 25033;

System altered.

ZWF.YUDONG>alter system dump datafile 2 block 21319;

System altered.

 


*** 2008-04-21 12:56:09.821
Start dump data blocks tsn: 1 file#: 2 minblk 25033 maxblk 25033
buffer tsn: 1 rdba: 0x008061c9 (2/25033)
scn: 0x0000.165af607 seq: 0x0a flg: 0x00 tail: 0xf607020a
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200

。。。。。。
 
********************************************************************************
UNDO BLK: 
xid: 0x0008.006.00008381  seq: 0x782 cnt: 0x20  irb: 0x20  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f20     0x02 0x1e5c     0x03 0x1df8     0x04 0x1da4     0x05 0x1d08    
0x06 0x1c9c     0x07 0x1ba4     0x08 0x1b3c     0x09 0x1ae4     0x0a 0x1a20    
0x0b 0x195c     0x0c 0x1910     0x0d 0x18a4     0x0e 0x1844     0x0f 0x17d8    
0x10 0x170c     0x11 0x16a4     0x12 0x164c     0x13 0x15e8     0x14 0x1594    
0x15 0x14bc     0x16 0x13f4     0x17 0x1370     0x18 0x1314     0x19 0x12b8    
0x1a 0x125c     0x1b 0x1200     0x1c 0x11a4     0x1d 0x1148     0x1e 0x10ec    
0x1f 0x1090     0x20 0x1034    
 
*-----------------------------
* Rec #0x17  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
uba: 0x008061c9.0782.16 ctl max scn: 0x0000.165af274 prv tx scn: 0x0000.165af28d
txn start scn: scn: 0x0000.165af607 logon user: 67
 prev brb: 8413639 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 1(0x1)
 
*-----------------------------
* Rec #0x18  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x17  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008061c9.0782.17
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 2(0x2)
 
*-----------------------------
* Rec #0x19  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x18  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008061c9.0782.18
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 3(0x3)
 
*-----------------------------
* Rec #0x1a  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x19  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008061c9.0782.19
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 4(0x4)
 
*-----------------------------
* Rec #0x1b  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x1a  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008061c9.0782.1a
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 5(0x5)
 
*-----------------------------
* Rec #0x1c  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x1b  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008061c9.0782.1b
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 6(0x6)
 
*-----------------------------
* Rec #0x1d  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x1c  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008061c9.0782.1c
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 7(0x7)
 
*-----------------------------
* Rec #0x1e  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x1d  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008061c9.0782.1d
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 8(0x8)
 
*-----------------------------
* Rec #0x1f  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x1e  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008061c9.0782.1e
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 9(0x9)
 
*-----------------------------
* Rec #0x20  slt: 0x06  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x1f  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008061c9.0782.1f
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 10(0xa)
 
End dump data blocks tsn: 1 file#: 2 minblk 25033 maxblk 25033

 

 


*** 2008-04-21 12:55:57.552
Start dump data blocks tsn: 1 file#: 2 minblk 21319 maxblk 21319
buffer tsn: 1 rdba: 0x00805347 (2/21319)
scn: 0x0000.165af7e8 seq: 0x01 flg: 0x00 tail: 0xf7e80201
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D2E4200 to 0x0D2E6200

。。。。。。
 
********************************************************************************
UNDO BLK: 
xid: 0x0003.02f.0001b043  seq: 0x1e18 cnt: 0x17  irb: 0x17  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1ef0     0x02 0x1e88     0x03 0x1e30     0x04 0x1d6c     0x05 0x1ca4    
0x06 0x1bac     0x07 0x1b44     0x08 0x1aec     0x09 0x1aa0     0x0a 0x1a34    
0x0b 0x19d4     0x0c 0x1968     0x0d 0x189c     0x0e 0x1834     0x0f 0x17dc    
0x10 0x1778     0x11 0x1724     0x12 0x164c     0x13 0x159c     0x14 0x1500    
0x15 0x1494     0x16 0x13cc     0x17 0x1330    
 
*-----------------------------
* Rec #0x17  slt: 0x2f  objn: 71837(0x0001189d)  objd: 71837  tblspc: 25(0x00000019)
*       Layer:  11 (Row)   opc: 1   rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
uba: 0x00805347.1e18.16 ctl max scn: 0x0000.165af56c prv tx scn: 0x0000.165af574
txn start scn: scn: 0x0000.165af7e8 logon user: 67
 prev brb: 8409925 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
op: Z
KDO Op code: QMD row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x05000e42  hdba: 0x05000e41
itli: 3  ispac: 0  maxfr: 4863
tabn: 0 lock: 0 nrow: 10
slot[0]: 11
slot[1]: 12
slot[2]: 13
slot[3]: 14
slot[4]: 15
slot[5]: 16
slot[6]: 17
slot[7]: 18
slot[8]: 19
slot[9]: 20
 
End dump data blocks tsn: 1 file#: 2 minblk 21319 maxblk 21319

 

對於批量insert,oracle儲存在undo中為一個record或者少數幾個(如果資料跨越多個undoblock),其中有多個slot槽儲存相應的資料;
而每個單獨的insert操作,oracle均在undo中有一個對應的record,所以insert from table,bulk insert等操作都會有比較好的效能。

 

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/756652/viewspace-246351/,如需轉載,請註明出處,否則將追究法律責任。

相關文章