Oracle clean out.,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/Fsc0x020x0003.019.00000d40 0x008009d3.24dd.1a --U-1fsc 0x0000.102cdb84

tl: 207 fb: --H-FL-- lb:0x2cc: 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 ----1fsc 0x0000.00000000

tl: 207 fb: --H-FL--lb: 0x1cc: 2

在做cleanout以后

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0004.027.00000d00 0x008000c0.208c.3cC--- 0scn 0x0005.102ce398

tl: 207 fb: --H-FL--lb: 0x0cc: 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.2eC-U-0scn0x0005.102ce8f9tl: 207 fb: --H-FL--lb: 0x0cc: 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。