oracle 延迟块清楚,oracle 块延迟清除(delayed block cleanout) 理解

为了保证事务的回退和满足多用户的CR，oracle引入了undo机制，由于undo是循环使用的，在一个事务完成过程中，它与redo相互配合，其中undo在一次事务中需要完成以下工作：

(1)Transaction开始前回滚段获取一个ITL(事务槽)，分配空间，记录事务信息

(2)Transaction提交后，redo完成记录，同时还清除回滚段的事务信息包括行级锁，ITL信息(commit标志，SCN等)

清除这些事务段的信息的过程就叫做块清除，在完成块清除时,我们本事务修改的数据块就会存在两种可能(1)所有的数据块还保存在buffercache中，(2)部分数据块或者是全部数据块由于LRU管理已经被刷出了buffer cache。oracle为了考虑到块清除的成本，以及性能，会作以下两种方式的块清除处理：

(1)快速块清除(fast block cleanout),当事务修改的数据库全部保存在buffer cache并且修改数据块的数据量没有超过cache buffer的10%，快速清除事务信息。

(2)延迟块清除(delayed block cleanout)当修改的数据块的阀值超过10%或者本次事务相关的数据块已经被刷出了buffer cache，oracle会下次访问此block时再清除事务信息。

下面通过一个实验测试，来熟悉一下delayed block cleanout的处理

SQL> select*fromv$version ;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE10.2.0.1.0Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

SQL> showparameterundo;

NAMETYPEVALUE

------------------------------------ ----------- ------------------------------

undo_managementstringAUTO

undo_retentioninteger900

undo_tablespacestringUNDOTBS1

SQL> conn gabriel/gabriel

Connected.

SQL> createtable test_delayedasselect *fromuser_objects;

Table created.

SQL> select count(1)fromtest_delayed;

COUNT(1)

----------

3

SQL> update test_delayedset object_id=1 where object_name='TEST_REDO';

1 row updated.

SQL> update test_delayedset object_id=2 where object_name='TEST_DELAYED';

1 row updated.

SQL>-----不提交

查询回滚段信息:

SQL> col segment_namefor a20;

select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS

SQL> tablespace_name from dba_rollback_segs;

2

OWNERSEGMENT_NAMESEGMENT_IDFILE_IDBLOCK_ID TABLESPACE_NAME

------ -------------------- ---------- ---------- ---------- ----------------

SYSSYSTEM019 ONLINE

PUBLIC _SYSSMU1$129 ONLINE

PUBLIC _SYSSMU2$2225 ONLINE

PUBLIC _SYSSMU3$3241 ONLINE

PUBLIC _SYSSMU4$4257 ONLINE

PUBLIC _SYSSMU5$5273 ONLINE

PUBLIC _SYSSMU6$6289 ONLINE

PUBLIC _SYSSMU7$72105 ONLINE

PUBLIC _SYSSMU8$82121 ONLINE

PUBLIC _SYSSMU9$92137 ONLINE

PUBLIC _SYSSMU10$102153 ONLINE

11 rows selected.

查询事务信息

SQL>selectxidusn,xidslot,xidsqn,ubablk,ubafil,ubarec fromv$transaction;

XIDUSNXIDSLOTXIDSQNUBABLKUBAFILUBAREC

---------- ---------- ---------- ---------- ---------- ----------

6404279927

查询该活动事务所在的回滚段

SQL> select *fromv$rollnamewhere usn = &usn;

Enter value for usn: 6

old1: select *fromv$rollnamewhere usn = &usn

new1: select *fromv$rollnamewhere usn = 6

USN NAME

---------- ------------------------------

6 _SYSSMU6$

查询test_delayed对象所在的fileid和blockid由于数据对象还存在buffer

SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,

2a.state,decode(bitand(flag,1), 0, 'N', 'Y') DIRTY

3from x$bh a,dba_extents b

4where b.RELATIVE_FNO = a.dbarfil

5and b.BLOCK_ID <= a.dbablk and b.block_id + b.blocks > a.dbablk

6and b.owner='GABRIEL' and b.segment_name='TEST_DELAYED';

SEGMENT_NAMEFILE#DBARFILDBABLKCLASSSTATE D

-------------------- ---------- ---------- ---------- ---------- ---------- -

TEST_DELAYED882811 N

TEST_DELAYED882813 N

TEST_DELAYED882813 N

TEST_DELAYED882741 N

TEST_DELAYED882743 N

由上可知：x$bh.class= 4表示为segment headerx$bh.state =3为前镜像块，因此file#=8

Dbablk=28为数据块

SQL> alter systemdump datafile 8 block 28;

System altered.

SQL> alter systemdump undo header '_SYSSMU6$';

System altered.

SQL> alter systemdump datafile 2 block 99;

System altered.

SQL> @gettrname.sql

TRACE_FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/admin/gabriel/udump/gabriel_ora_4756.trc

以下为trace文件中的截图部分

Block header dump:0x0200001c

Object id on Block? Y

seg/obj: 0xcf1acsc: 0x00.f4707itc: 3flg: Etyp: 1 - DATA

brn: 0bdba: 0x2000019 ver: 0x01 opc: 0

inc: 0exflg: 0

ItlXidUbaFlagLckScn/Fsc

0x01 0xffff.000.000000000x00000000.0000.00C---0scn 0x0000.000f4707

0x020x0006.028.000001ab0x00800063.0156.07----2fsc 0x0004.00000000

0x030x0000.000.000000000x00000000.0000.00----0fsc 0x0000.00000000

---事务信息存在

tab 0, row 1, @0x1cf8

tl: 76 fb: --H-FL-- lb: 0x2cc: 12

col0: [ 9]54 45 53 54 5f 52 45 44 4f

col1: *NULL*

col2: [ 2]c1 02

col3: [ 4]c3 06 1d 2d

col4: [ 5]54 41 42 4c 45

col5: [ 7]78 6f 06 17 0d 0c 23

col6: [ 7]78 6f 06 17 0d 0c 23

col7: [19]32 30 31 31 2d 30 36 2d 32 33 3a 31 32 3a 31 31 3a 33 34

col8: [ 5]56 41 4c 49 44

col9: [ 1]4e

col 10: [ 1]4e

col 11: [ 1]4e

tab 0, row 2, @0x1ca9

tl: 79 fb: --H-FL-- lb: 0x2cc: 12

col0: [12]54 45 53 54 5f 44 45 4c 41 59 45 44

col1: *NULL*

col2: [ 2]c1 03

col3: [ 4]c3 06 1f 13

col4: [ 5]54 41 42 4c 45

col5: [ 7]78 6f 08 14 16 02 35

col6: [ 7]78 6f 08 14 16 02 35

col7: [19]32 30 31 31 2d 30 38 2d 32 30 3a 32 31 3a 30 31 3a 35 32

col8: [ 5]56 41 4c 49 44

col9: [ 1]4e

col 10: [ 1]4e

col 11: [ 1]4e

end_of_block_dump

End dump data blocks tsn: 9 file#: 8 minblk 28 maxblk 28

*** 2011-08-20 23:25:38.403

---锁信息也存在

再来仔细看看undo segment header信息：

TRN TBL::

indexstate cflagswrap#uelscndbaparent-xidnubstmt_numcmt

------------------------------------------------------------------------------------------------

0x28100x800x01ab0x00020x0000.000f59ac0x008000630x0000.000.000000000x000000010x000000000

0x28转化为十进制为40刚好为前面查询的事务槽，state状态为10表示活动事务，dba 0x00800063转化为2进制0000 0000 1000 0000 0000 0000 0110 0011根据dba的转换2号文件的64+32+2+1 =99

接下来我们看看undo段的转存信息(限于篇幅，只截取了部分关键信息)

Start dump data blocks tsn: 1 file#: 2 minblk 99 maxblk 99

buffer tsn: 1 rdba: 0x00800063 (2/99)

scn: 0x0000.000f59c1 seq: 0x01 flg: 0x04 tail: 0x59c10201

frmt: 0x02 chkval: 0xa7ae type: 0x02=KTU UNDO BLOCK

Hex dump of block: st=0, typ_found=1

…….

UNDO BLK:

xid: 0x0006.028.000001abseq: 0x156 cnt: 0x7irb: 0x7icl: 0x0flg: 0x0000

Rec OffsetRec OffsetRec OffsetRec OffsetRec Offset

---------------------------------------------------------------------------

0x01 0x1ecc0x02 0x1e000x03 0x1d1c0x04 0x1c800x05 0x1c04

0x06 0x1ad00x07 0x19c4

*-----------------------------

* Rec #0x1slt: 0x06objn: 49948(0x0000c31c)objd: 49948tblspc: 2(0x00000002)

*Layer:11 (Row)opc: 1rci 0x00

Undo type:Regular undoLast buffer split:No

Temp Object:No

Tablespace Undo:No

rdba: 0x00800062

*-----------------------------

KDO undo record:

KTB Redo

op: 0x02ver: 0x01

op: Cuba: 0x00800062.0156.1e

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000bdba: 0x00c05be8hdba: 0x00c05be3

itli: 2ispac: 0maxfr: 4858

tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 0

ncol: 12 nnew: 2 size: 0

col9: [ 1]80

col 10: [ 1]80

*-----------------------------

* Rec #0x2slt: 0x06objn: 49871(0x0000c2cf)objd: 49871tblspc: 2(0x00000002)

*Layer:10 (Index)opc: 22rci 0x01

Undo type:Regular undoLast buffer split:No

Temp Object:No

Tablespace Undo:No

rdba: 0x00000000

*-----------------------------

index undo for leaf key operations

KTB Redo

op: 0x04ver: 0x01

op: Litl: xid:0x0003.02a.000001b9 uba: 0x008000d7.0174.23

………..

*-----------------------------

* Rec #0x6slt: 0x28objn: 53018(0x0000cf1a)objd: 53018tblspc: 9(0x00000009)

*Layer:11 (Row)opc: 1rci 0x00

Undo type:Regular undoBegin transLast buffer split:No

Temp Object:No

Tablespace Undo:No

rdba: 0x00000000

*-----------------------------

uba: 0x00800063.0156.04 ctl max scn: 0x0000.000f5354 prv tx scn: 0x0000.000f535c

txn start scn: scn: 0x0000.000f59ac logon user: 64

prev brb: 8388871 prev bcl: 0

KDO undo record:

KTB Redo

op: 0x03ver: 0x01

op: Z

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000bdba: 0x0200001chdba: 0x0200001b

itli: 2ispac: 0maxfr: 4858

tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 183

ncol: 12 nnew: 1 size: 2

col2: [ 4]c3 06 1d 2d

*-----------------------------

* Rec #0x7slt: 0x28objn: 53018(0x0000cf1a)objd: 53018tblspc: 9(0x00000009)

*Layer:11 (Row)opc: 1rci 0x06

Undo type:Regular undoLast buffer split:No

Temp Object:No

Tablespace Undo:No

rdba: 0x00000000

*-----------------------------

KDO undo record:

KTB Redo

op: 0x02ver: 0x01

op: Cuba: 0x00800063.0156.06

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000bdba: 0x0200001chdba: 0x0200001b

itli: 2ispac: 0maxfr: 4858

tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0

ncol: 12 nnew: 1 size: 2

col2: [ 4]c3 06 1f 13

End dump data blocks tsn: 1 file#: 2 minblk 99 maxblk 99

---irb: 0x7最近未提交事务的起始回滚点，回滚段信息偏移量的最后偏移地址刚好相等

--rci 0x06代表undochain下一偏移量地址

上面dump信息是整个事务在没有commit的情况下产生，下面我们flush一下buffer_cache将buffer cache中的前数据块写入dbfile，然后将事务commit，我们再认真比对dump信息，

SQL> alter systemflush buffer_cache;

System altered.

------在事务窗体进行以下操作：

SQL> show user

USER is "GABRIEL"

SQL> commit;

Commit complete.

重复以上dump操作

…….

SQL> alter systemdump datafile 2 block 99;

System altered.

SQL> @gettrname.sql

TRACE_FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/admin/gabriel/udump/gabriel_ora_4963.trc

先看看数据块的dump信息

Block header dump:0x0200001c

Object id on Block? Y

seg/obj: 0xcf1acsc: 0x00.f4707itc: 3flg: Etyp: 1 - DATA

brn: 0bdba: 0x2000019 ver: 0x01 opc: 0

inc: 0exflg: 0

ItlXidUbaFlagLckScn/Fsc

0x010xffff.000.000000000x00000000.0000.00C---0scn 0x0000.000f4707

0x020x0006.028.000001ab0x00800063.0156.07----2fsc 0x0004.00000000

0x030x0000.000.000000000x00000000.0000.00----0fsc 0x0000.00000000

tab 0, row 1, @0x1cf8

tl: 76 fb: --H-FL-- lb: 0x2cc: 12

col0: [ 9]54 45 53 54 5f 52 45 44 4f

col1: *NULL*

col2: [ 2]c1 02

col3: [ 4]c3 06 1d 2d

col4: [ 5]54 41 42 4c 45

col5: [ 7]78 6f 06 17 0d 0c 23

col6: [ 7]78 6f 06 17 0d 0c 23

col7: [19]32 30 31 31 2d 30 36 2d 32 33 3a 31 32 3a 31 31 3a 33 34

col8: [ 5]56 41 4c 49 44

col9: [ 1]4e

col 10: [ 1]4e

col 11: [ 1]4e

tab 0, row 2, @0x1ca9

tl: 79 fb: --H-FL-- lb: 0x2cc: 12

col0: [12]54 45 53 54 5f 44 45 4c 41 59 45 44

col1: *NULL*

col2: [ 2]c1 03

col3: [ 4]c3 06 1f 13

col4: [ 5]54 41 42 4c 45

col5: [ 7]78 6f 08 14 16 02 35

col6: [ 7]78 6f 08 14 16 02 35

col7: [19]32 30 31 31 2d 30 38 2d 32 30 3a 32 31 3a 30 31 3a 35 32

col8: [ 5]56 41 4c 49 44

col9: [ 1]4e

col 10: [ 1]4e

col 11: [ 1]4e

end_of_block_dump

End dump data blocks tsn: 9 file#: 8 minblk 28 maxblk 28

*** 2011-08-21 00:18:07.820

可以看出数据块的信息与commit之前的内容基本吻合

再来仔细看看undo segment header信息：

0x2790x000x01ab0x00150x0000.000f56550x008000620x0000.000.000000000x000000020x000000001313850640

0x2890x000x01ab0xffff0x0000.000f6a640x008000630x0000.000.000000000x000000010x000000001313857062

0x2990x000x01aa0x000a0x0000.000f54e40x008001080x0000.000.000000000x000000010x000000001313849990

-----事务已经提交

接下来我们看看undo段的转存信息(限于篇幅，只截取了部分关键信息)

*** 2011-08-21 00:18:20.252

Start dump data blocks tsn: 1 file#: 2 minblk 99 maxblk 99

buffer tsn: 1 rdba: 0x00800063 (2/99)

scn: 0x0000.000f59c1 seq: 0x01 flg: 0x04 tail: 0x59c10201

frmt: 0x02 chkval: 0xa7ae type: 0x02=KTU UNDO BLOCK

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x0D50A600 to 0x0D50C600

UNDO BLK:

xid: 0x0006.028.000001abseq: 0x156 cnt: 0x7irb: 0x7icl: 0x0flg: 0x0000

Rec OffsetRec OffsetRec OffsetRec OffsetRec Offset

---------------------------------------------------------------------------

0x01 0x1ecc0x02 0x1e000x03 0x1d1c0x04 0x1c800x05 0x1c04

0x06 0x1ad00x07 0x19c4

*-----------------------------

* Rec #0x1slt: 0x06objn: 49948(0x0000c31c)objd: 49948tblspc: 2(0x00000002)

*Layer:11 (Row)opc: 1rci 0x00

Undo type:Regular undoLast buffer split:No

Temp Object:No

Tablespace Undo:No

rdba: 0x00800062

*-----------------------------

KDO undo record:

KTB Redo

op: 0x02ver: 0x01

op: Cuba: 0x00800062.0156.1e

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000bdba: 0x00c05be8hdba: 0x00c05be3

itli: 2ispac: 0maxfr: 4858

tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 0

ncol: 12 nnew: 2 size: 0

col9: [ 1]80

col 10: [ 1]80

*-----------------------------

* Rec #0x2slt: 0x06objn: 49871(0x0000c2cf)objd: 49871tblspc: 2(0x00000002)

*Layer:10 (Index)opc: 22rci 0x01

Undo type:Regular undoLast buffer split:No

Temp Object:No

Tablespace Undo:No

rdba: 0x00000000

*-----------------------------

index undo for leaf key operations

KTB Redo

op: 0x04ver: 0x01

op: Litl: xid:0x0003.02a.000001b9 uba: 0x008000d7.0174.23

flg: C---lkc:0scn: 0x0000.000f5659

Dump kdilk : itl=2, kdxlkflg=0xc1 sdc=0 indexid=0xc059ab block=0x00c059b0

*-----------------------------

* Rec #0x6slt: 0x28objn: 53018(0x0000cf1a)objd: 53018tblspc: 9(0x00000009)

*Layer:11 (Row)opc: 1rci 0x00

Undo type:Regular undoBegin transLast buffer split:No

Temp Object:No

Tablespace Undo:No

rdba: 0x00000000

*-----------------------------

uba: 0x00800063.0156.04 ctl max scn: 0x0000.000f5354 prv tx scn: 0x0000.000f535c

txn start scn: scn: 0x0000.000f59ac logon user: 64

prev brb: 8388871 prev bcl: 0

KDO undo record:

KTB Redo

op: 0x03ver: 0x01

op: Z

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000bdba: 0x0200001chdba: 0x0200001b

itli: 2ispac: 0maxfr: 4858

tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 183

ncol: 12 nnew: 1 size: 2

col2: [ 4]c3 06 1d 2d

*-----------------------------

* Rec #0x7slt: 0x28objn: 53018(0x0000cf1a)objd: 53018tblspc: 9(0x00000009)

*Layer:11 (Row)opc: 1rci 0x06

Undo type:Regular undoLast buffer split:No

Temp Object:No

Tablespace Undo:No

rdba: 0x00000000

*-----------------------------

KDO undo record:

KTB Redo

op: 0x02ver: 0x01

op: Cuba: 0x00800063.0156.06

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000bdba: 0x0200001chdba: 0x0200001b

itli: 2ispac: 0maxfr: 4858

tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0

ncol: 12 nnew: 1 size: 2

col2: [ 4]c3 06 1f 13

由两次的dump对比可以得出：块延迟清除只是更改了undo segment header的事务信息状态，数据块与undo块信息均保持不变

--

SQL> set echo off

SQL> set autotrace on

SQL> select *from gabriel.test_delayed; ---将数据块再次缓存在cache buffer

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

178recursive calls

0db block gets

27consistent gets

7physical reads

72redo size

1364bytes sent via SQL*Net to client

385bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

4sorts (memory)

0sorts (disk)

3rows processed

再次转存数据块信息

SQL> alter systemdump datafile 8 block 28;

System altered.

SQL> @gettrname.sql

TRACE_FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/admin/gabriel/udump/gabriel_ora_5017.trc

再次观察数据块的dump信息

*** 2011-08-21 00:50:23.744

*** SERVICE NAME:(SYS$USERS) 2011-08-21 00:50:23.743

*** SESSION ID:(152.73) 2011-08-21 00:50:23.743

Start dump data blocks tsn: 9 file#: 8 minblk 28 maxblk 28

buffer tsn: 9 rdba: 0x0200001c (8/28)

Block header dump:0x0200001c

Object id on Block? Y

seg/obj: 0xcf1acsc: 0x00.f6e54itc: 3flg: Etyp: 1 - DATA

brn: 0 bdba: 0x2000019 ver: 0x01 opc: 0

inc: 0exflg: 0

ItlXidUbaFlagLckScn/Fsc

0x010xffff.000.000000000x00000000.0000.00C---0scn 0x0000.000f4707

0x020x0006.028.000001ab0x00800063.0156.07 C---0scn 0x0000.000f6a64

0x030x0000.000.000000000x00000000.0000.00----0fsc 0x0000.00000000

---事务信息清除

block_row_dump:

tab 0, row 0, @0x1f1d

tl: 99 fb: --H-FL-- lb: 0x0cc: 12

col0: [30]

42 49 4e 24 70 6c 6f 37 43 4f 51 50 35 34 54 67 51 4b 6a 41 62 67 41 4d 4a

51 3d 3d 24 30

col1: *NULL*

col2: [ 4]c3 06 1d 19

col3: [ 4]c3 06 1d 19

col4: [ 5]54 41 42 4c 45

col5: [ 7]78 6f 04 1a 05 1c 21

col6: [ 7]78 6f 06 17 0d 08 3c

col7: [19]32 30 31 31 2d 30 36 2d 32 33 3a 31 32 3a 30 37 3a 35 39

col8: [ 5]56 41 4c 49 44

col9: [ 1]4e

col 10: [ 1]4e

col 11: [ 1]4e

tab 0, row 1, @0x1cf8

tl: 76 fb: --H-FL-- lb: 0x0cc: 12

col0: [ 9]54 45 53 54 5f 52 45 44 4f

col 1: *NULL*

col2: [ 2]c1 02

col3: [ 4]c3 06 1d 2d

col4: [ 5]54 41 42 4c 45

col5: [ 7]78 6f 06 17 0d 0c 23

col6: [ 7]78 6f 06 17 0d 0c 23

col7: [19]32 30 31 31 2d 30 36 2d 32 33 3a 31 32 3a 31 31 3a 33 34

col8: [ 5]56 41 4c 49 44

col9: [ 1]4e

col 10: [ 1]4e

col 11: [ 1]4e

tab 0, row 2, @0x1ca9

tl: 79 fb: --H-FL-- lb: 0x0cc: 12

col0: [12]54 45 53 54 5f 44 45 4c 41 59 45 44

col1: *NULL*

col2: [ 2]c1 03

col3: [ 4]c3 06 1f 13

col4: [ 5]54 41 42 4c 45

col5: [ 7]78 6f 08 14 16 02 35

col6: [ 7]78 6f 08 14 16 02 35

---锁信息已经清除

总结整个blockdelaye cleanout过程

(1)本次事务相关的数据块已经被刷出了buffer cache，当本次事务提交后，事务相关的data block ,undo block上的事务信息，锁信息不会被清除。

(2)当data block再次进入buffer cache，oracle在读取次数据块时作事务信息锁信息的清除处理