ITPub博客

首页 > 数据库 > Oracle > Oracle block cleanout

Oracle block cleanout

原创 Oracle 作者:rwq_ 时间:2007-04-22 20:38:41 0 删除 编辑
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还未commitrollbackmodified block已经被写回磁盘,当发生commitoracle并不会把block重新读入做cleanout,而是把cleanout留到下一次对此块的dmlselect。当delayed cleanout时候如果undo segment headertransaction 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

做了cleanoutlck,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指向了0x000asegment 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    36
  • 访问量
    637454