这是一个delay block cleanout的最基本的测试,slot被覆盖的情况再讨论。
[@more@]session 1:
SQL> insert into tt select * from tt;
已创建180096行。
SQL> select xidusn from v$transaction;
XIDUSN
----------
37
SQL> select usn,name from v$rollname where usn=37;
USN NAME
---------- ------------------------------
37 _SYSSMU37$
session 2
--analyze table是ddl命令,在session 1中执行此命令会commit事务
SQL> analyze table tt compute statistics;
表已分析。
--验证一下事务还在
SQL> select xidusn from v$transaction;
XIDUSN
----------
37
SQL> select blocks,empty_blocks from dba_tables where table_name='TT';
BLOCKS EMPTY_BLOCKS
---------- ------------
4540 68
SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_nam
e='TT';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 4 9 8
1 4 17 8
2 4 25 8
3 4 33 8
4 4 41 8
5 4 49 8
6 4 57 8
7 4 65 8
8 4 73 8
9 4 81 8
10 4 89 8
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
11 4 97 8
12 4 105 8
13 4 113 8
14 4 121 8
15 4 129 8
16 4 137 128
17 4 265 128
18 4 393 128
19 4 521 128
20 4 649 128
21 4 777 128
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
22 4 905 128
23 4 1033 128
24 4 1161 128
25 4 1289 128
26 4 1417 128
27 4 1545 128
28 4 1673 128
29 4 1801 128
30 4 1929 128
31 4 2057 128
32 4 2185 128
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
33 4 2313 128
34 4 2441 128
35 4 2569 128
36 4 2697 128
37 4 2825 128
38 4 2953 128
39 4 3081 128
40 4 3209 128
41 4 3337 128
42 4 3465 128
43 4 3593 128
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
44 4 3721 128
45 4 3849 128
46 4 3977 128
47 4 4105 128
48 4 4233 128
49 4 4361 128
50 4 4489 128
已选择51行。
--后面的68个block是empty的,我们从4489开始去4495验证delay block cleanout
此时事务没有提交,所以4495block的itl 0x01中没有提交信息(从flag,lck,Scn/Fsn都可以看出来)
SQL> alter system dump datafile 4 block 4495;
系统已更改。
SQL>
--=============================
Start dump data blocks tsn: 4 file#: 4 minblk 4495 maxblk 4495
buffer tsn: 4 rdba: 0x0100118f (4/4495)
scn: 0x0000.001c7d1f seq: 0x01 flg: 0x04 tail: 0x7d1f0601
frmt: 0x02 chkval: 0x9785 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08732200 to 0x08734200
.............省去无关信息
Block header dump: 0x0100118f
Object id on Block? Y
seg/obj: 0x2e0a csc: 0x00.1c7d1f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1001189 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0025.00d.00000009 0x02000148.0010.01 ---- 77 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x8732264
===============
tsiz: 0x1f98
hsiz: 0xac
pbl: 0x08732264
bdba: 0x0100118f
76543210
flag=--------
ntab=1
nrow=77
frre=-1
fsbo=0xac
fseo=0x3dd
avsp=0x331
tosp=0x331
0xe:pti[0] nrow=77 offs=0
0x12:pri[0] offs=0x10f9
--=================================
--接下来我们dump上面事务使用的slot信息,slot中stat=10表示是事务是活动的,slot编号为0x0d
SQL> alter system dump undo header '_SYSSMU37$';
系统已更改。
SQL>
--=================================
Undo Segment: _SYSSMU37$ (37)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 12 #blocks: 95
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x02000150 ext#: 10 blk#: 7 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 10
Unlocked
Map Header:: next 0x00000000 #extents: 12 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0200009a length: 7
0x020000c9 length: 8
0x02000109 length: 8
0x02000111 length: 8
0x02000119 length: 8
0x02000121 length: 8
0x02000129 length: 8
0x02000131 length: 8
0x02000139 length: 8
0x02000141 length: 8
0x02000149 length: 8
0x02000151 length: 8
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1235880926
Extent Number:1 Commit Time: 1235880926
Extent Number:2 Commit Time: 0
Extent Number:3 Commit Time: 0
Extent Number:4 Commit Time: 0
Extent Number:5 Commit Time: 0
Extent Number:6 Commit Time: 0
Extent Number:7 Commit Time: 0
Extent Number:8 Commit Time: 0
Extent Number:9 Commit Time: 0
Extent Number:10 Commit Time: 0
Extent Number:11 Commit Time: 0
TRN CTL:: seq: 0x0011 chd: 0x000e ctl: 0x000a inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x020000ca.0008.0e scn: 0x0000.001c6388
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0008.0d ext: 0x1 spc: 0x1888
uba: 0x00000000.0007.0d ext: 0x0 spc: 0xed4
uba: 0x00000000.0006.04 ext: 0x3 spc: 0x1d8a
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub
stmt_num cmt
-----------------------------------------------------------------------------------------
-------
0x0c 9 0x00 0x0009 0x000b 0x0000.001c6aea 0x020000ca 0x0000.000.00000000
0x00000001 0x00000000 1235881268
0x0d 10 0x80 0x0009 0x0001 0x0000.001c6bba 0x02000150 0x0000.000.00000000
0x0000004f 0x00000000 0
--=============================================
--提交事务
SQL> commit;
提交完成。
--事务提交之后再次dump undo来看事务使用的slot信息,此时发现slot(0x0d)的state=9,说明事务
已经提交,并且提交时的scn是:001c7f56
SQL> alter system dump undo header '_SYSSMU37$';
系统已更改。
SQL>
--===============================================
Undo Segment: _SYSSMU37$ (37)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 12 #blocks: 95
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x02000150 ext#: 10 blk#: 7 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 10
Unlocked
Map Header:: next 0x00000000 #extents: 12 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0200009a length: 7
0x020000c9 length: 8
0x02000109 length: 8
0x02000111 length: 8
0x02000119 length: 8
0x02000121 length: 8
0x02000129 length: 8
0x02000131 length: 8
0x02000139 length: 8
0x02000141 length: 8
0x02000149 length: 8
0x02000151 length: 8
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1235880926
Extent Number:1 Commit Time: 1235882405
Extent Number:2 Commit Time: 1235882405
Extent Number:3 Commit Time: 1235882405
Extent Number:4 Commit Time: 1235882405
Extent Number:5 Commit Time: 1235882405
Extent Number:6 Commit Time: 1235882405
Extent Number:7 Commit Time: 1235882405
Extent Number:8 Commit Time: 1235882405
Extent Number:9 Commit Time: 1235882405
Extent Number:10 Commit Time: 1235882405
Extent Number:11 Commit Time: 0
TRN CTL:: seq: 0x0011 chd: 0x000e ctl: 0x000d inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x020000ca.0008.0e scn: 0x0000.001c6388
Version: 0x01
FREE BLOCK POOL::
uba: 0x02000150.0011.21 ext: 0xa spc: 0xd08
uba: 0x00000000.0007.0d ext: 0x0 spc: 0xed4
uba: 0x00000000.0006.04 ext: 0x3 spc: 0x1d8a
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub
stmt_num cmt
-----------------------------------------------------------------------------------------
-------
0x0c 9 0x00 0x0009 0x000b 0x0000.001c6aea 0x020000ca 0x0000.000.00000000
0x00000001 0x00000000 1235881268
0x0d 9 0x00 0x0009 0xffff 0x0000.001c7f56 0x02000150 0x0000.000.00000000
0x0000004f 0x00000000 1235882405
--===============================================
--这时候我们再来dump 4495 block来看一下oracle是如何进行delay block cleanout的呢?
我们发现尽管事务已经提交,但是4495 blockitl中显示的状态依然是没有提交时的状态,那么到底什么
时候来清除这个block的事务状态呢?要下一次访问该block时清除,这就是delay block cleanout,这样
做的目的当然是针对大事务加快提交的速度。
SQL> alter system dump datafile 4 block 4495;
系统已更改。
SQL>
--====================================
Start dump data blocks tsn: 4 file#: 4 minblk 4495 maxblk 4495
buffer tsn: 4 rdba: 0x0100118f (4/4495)
scn: 0x0000.001c7d1f seq: 0x01 flg: 0x04 tail: 0x7d1f0601
frmt: 0x02 chkval: 0x9785 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08732200 to 0x08734200
...............省去无关信息
Block header dump: 0x0100118f
Object id on Block? Y
seg/obj: 0x2e0a csc: 0x00.1c7d1f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1001189 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0025.00d.00000009 0x02000148.0010.01 ---- 77 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x8732264
===============
tsiz: 0x1f98
hsiz: 0xac
pbl: 0x08732264
bdba: 0x0100118f
76543210
flag=--------
ntab=1
nrow=77
frre=-1
fsbo=0xac
fseo=0x3dd
avsp=0x331
tosp=0x331
--====================================
--我们来访问一下tt表,由于是count操作,所以一定会scan到4495 block,同时我们观察select操作产
生了redo,这些redo的产生其实主要是清除row的lb(lock byte),当然也要对itl进行修改
SQL> set autotrace on stat
SQL> select count(*) from tt;
COUNT(*)
----------
360192
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5713 consistent gets
0 physical reads
84788 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> alter system dump undo header '_SYSSMU37$';
系统已更改。
SQL>
--============================================
Undo Segment: _SYSSMU37$ (37)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 12 #blocks: 95
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x02000150 ext#: 10 blk#: 7 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 10
Unlocked
Map Header:: next 0x00000000 #extents: 12 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0200009a length: 7
0x020000c9 length: 8
0x02000109 length: 8
0x02000111 length: 8
0x02000119 length: 8
0x02000121 length: 8
0x02000129 length: 8
0x02000131 length: 8
0x02000139 length: 8
0x02000141 length: 8
0x02000149 length: 8
0x02000151 length: 8
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1235880926
Extent Number:1 Commit Time: 1235882405
Extent Number:2 Commit Time: 1235882405
Extent Number:3 Commit Time: 1235882405
Extent Number:4 Commit Time: 1235882405
Extent Number:5 Commit Time: 1235882405
Extent Number:6 Commit Time: 1235882405
Extent Number:7 Commit Time: 1235882405
Extent Number:8 Commit Time: 1235882405
Extent Number:9 Commit Time: 1235882405
Extent Number:10 Commit Time: 1235883019
Extent Number:11 Commit Time: 0
TRN CTL:: seq: 0x0011 chd: 0x0013 ctl: 0x0014 inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x02000150.0011.2a scn: 0x0000.001c646b
Version: 0x01
FREE BLOCK POOL::
uba: 0x02000150.0011.2b ext: 0xa spc: 0x7c0
uba: 0x00000000.0007.0d ext: 0x0 spc: 0xed4
uba: 0x00000000.0006.04 ext: 0x3 spc: 0x1d8a
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub
stmt_num cmt
-----------------------------------------------------------------------------------------
-------
0x0c 9 0x00 0x0009 0x000b 0x0000.001c6aea 0x020000ca 0x0000.000.00000000
0x00000001 0x00000000 1235881268
0x0d 9 0x00 0x0009 0x000e 0x0000.001c7f56 0x02000150 0x0000.000.00000000
0x0000004f 0x00000000 1235882405
--====================================================
--delay block cleanout之后,我们发现itl中flag为C,LCK=0,最主要是Scn/Fsc=001c7f56,这个scn是
从哪儿读出来的呢?正是来自undo slot=0x0d的scn:001c7f56,这个scn正是事务提交时的scn,如果此
时slot被覆盖的话,那么情况将更加复杂。
SQL> alter system dump datafile 4 block 4495;
系统已更改。
SQL>
--============================
Start dump data blocks tsn: 4 file#: 4 minblk 4495 maxblk 4495
buffer tsn: 4 rdba: 0x0100118f (4/4495)
scn: 0x0000.001c8060 seq: 0x01 flg: 0x00 tail: 0x80600601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08102200 to 0x08104200
..................省去无关信息
Block header dump: 0x0100118f
Object id on Block? Y
seg/obj: 0x2e0a csc: 0x00.1c8060 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1001189 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0025.00d.00000009 0x02000148.0010.01 C--- 0 scn 0x0000.001c7f56
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x8102264
===============
tsiz: 0x1f98
hsiz: 0xac
pbl: 0x08102264
bdba: 0x0100118f
76543210
flag=--------
ntab=1
nrow=77
frre=-1
fsbo=0xac
fseo=0x3dd
avsp=0x331
tosp=0x331
--============================
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1020023/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1020023/