Oracle block cleanout

cleanout 分为 2 钟，一种是 fast commit cleanout, 另一种是 delayed block cleanout. [@more@]

oracle有一个modified block list结构，用来记录每个transaction更改过的block，每个transaction大约可以记录10% buffer cache这么多的modified block。这部分block就是当发生commit的时候，oracle可以根据modified block list定位到那些块并做fast commit cleanout。如果一个transaction修改的块超过10% buffer cache,那么超过的块就执行delayed block cleanout。当做fast commit cleanout时，oracle不会清理 Row locks lb标志位，ITL lck标志位。

另一种情况是delayed block cleanout，当transaction还未commit或rollback时modified block已经被写回磁盘，当发生commit时oracle并不会把block重新读入做cleanout，而是把cleanout留到下一次对此块的dml或select。当delayed cleanout时候如果undo segment header的transaction table slot还没有被覆盖，那么可以找回该事务递交的exact scn，如果slot已经被覆盖，那么将会使用undo segment header中的control scn来做为upper bound scn。

下面有一些例子可以阐述几种类型的block cleanout

创建一张表，为了简单起见，每个block限制为一行数据

SQL 10G>create table test
2 pctfree 99
3 as
4 select rownum n1, rpad(rownum,200) v1
5 from all_objects
6 where rownum <= 1000
7 ;

Table created.

看一下第一行的file number,block number

SQL 10G>select dbms_rowid.ROWID_RELATIVE_FNO(rowid), dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from test where rownum=1;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 468

更新第一行并递交，这时候发生的是fast commit cleanout

SQL 10G>update test set n1=n1 where rownum=1;

1 row updated.

SQL 10G>commit;

Commit complete.

SQL 10G>alter system dump datafile 4 block 468;

System altered.

Itl Xid Uba Flag Lck Scn/Fsc0x02 0x0003.019.00000d40 0x008009d3.24dd.1a --U- 1 fsc 0x0000.102cdb84

tl: 207 fb: --H-FL-- lb: 0x2 cc: 2

fast commit cleanout并不清除lck,lb标志。

如果出现事务递交前modified block就被flush回硬盘，那么将发生delayed block cleanout。

更新记录:

SQL 10G>update test set n1=n1+1;

1000 rows updated.

flush buffer:

SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';

Session altered.

递交，这时不回去修改data block，只会修改undo segment header slot

SQL 10G>commit;

Commit complete.

SQL 10G>alter system dump datafile 4 block 468;

System altered.

通过查询来实现delayed block cleanout

SQL 10G>select count(*) from test where rownum=1;

COUNT(*)
----------
1

SQL 10G>alter system dump datafile 4 block 468;

System altered.

在做cleanout以前

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.027.00000d00 0x008000c0.208c.3c ---- 1 fsc 0x0000.00000000

tl: 207 fb: --H-FL-- lb: 0x1 cc: 2

在做cleanout以后

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.027.00000d00 0x008000c0.208c.3c C--- 0 scn 0x0005.102ce398

tl: 207 fb: --H-FL-- lb: 0x0 cc: 2

做了cleanout后lck,lb标志为都被清零，scn也是从undo segment header transaction table slot里面得到。如果slot被覆盖了，那么会把control scn拿来当作upperbound scn。

实验做到这里的时候产生一个疑问，如果在做delayed block cleanout之前undo tablespace被删除了怎么办，oracle从哪里找undo segment header呢？是不是cleanout会报错？带着疑问继续做实验。

SQL 10G>update test set n1=n1-1;

1000 rows updated.

SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';

Session altered.

SQL 10G>show parameter undo_tablespace

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1

SQL 10G>commit;

Commit complete.

切换undo tablespace

SQL 10G>alter system set undo_tablespace=undotbs2;

System altered.

删除旧的undo tablespace

SQL 10G>drop tablespace undotbs1 including contents;

Tablespace dropped.

SQL 10G>!rm /opt/oracle/oradata/dbtest/undotbs01.dbf

SQL 10G>alter system dump datafile 4 block 468;

System altered.

cleanout 并没有出错

SQL 10G>select count(*) from test;

COUNT(*)
----------
1000

SQL 10G>alter system dump datafile 4 block 468;

System altered.

发生cleanout以前

Itl Xid Uba Flag Lck Scn/Fsc
0x02 0x000a.015.00001906 0x008000b4.359d.2e ---- 1 fsc 0x0000.00000000

tl: 207 fb: --H-FL-- lb: 0x2 cc: 2

发生cleanout以后

Itl Xid Uba Flag Lck Scn/Fsc
0x02 0x000a.015.00001906 0x008000b4.359d.2e C-U- 0 scn 0x0005.102ce8f9
tl: 207 fb: --H-FL-- lb: 0x0 cc: 2

发现还是能取到upper bound scn,十分奇怪，试想是不是有基表保存已经被删除的undo segment信息，仔细一查，undo$出现了!!!

因为xid指向了0x000a的segment header，所以我们查询_SYSSMU10$

SQL 10G>select name,SCNBAS from undo$ where name='_SYSSMU10$';

NAME SCNBAS
------------------------------ ----------
_SYSSMU10$ 271378681

来转换一下upper bound scn

SQL 10G>select to_number('102ce8f9','xxxxxxxx') from dual;

TO_NUMBER('102CE8F9','XXXXXXXX')
--------------------------------
271378681

正是这个scn保存在undo$中被拿来当做upper bound scn.

来看一下undo$

SQL 10G>select header_file,header_block,tablespace_name from dba_segments where segment_name='UNDO$';

HEADER_FILE HEADER_BLOCK TABLESPACE_NAME
----------- ------------ ------------------------------
1 105 SYSTEM

果然是一个系统基表，oracle使用undo$来保证undo tablespace被删除后的delayed block cleanout。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/814/viewspace-911935/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/814/viewspace-911935/