Oracle block cleanout

[size=9pt]cleanout[/size][font=宋体][size=9pt]分为[/size][/font][size=9pt]2[/size][font=宋体][size=9pt]钟，一种是[/size][/font][size=9pt]fast commit cleanout,[/size][font=宋体][size=9pt]另一种是[/size][/font][size=9pt]delayed block cleanout. [/size]
[size=9pt]oracle[/size][font=宋体][size=9pt]有一个[/size][/font][size=9pt]modified block list[/size][font=宋体][size=9pt]结构，用来记录每个[/size][/font][size=9pt]transaction[/size][font=宋体][size=9pt]更改过的[/size][/font][size=9pt]block[/size][font=宋体][size=9pt]，每个[/size][/font][size=9pt]transaction[/size][font=宋体][size=9pt]大约可以记录[/size][/font][size=9pt]10% buffer cache[/size][font=宋体][size=9pt]这么多的[/size][/font][size=9pt]modified
block[/size][font=宋体][size=9pt]。这部分[/size][/font][size=9pt]block[/size][font=宋体][size=9pt]就是当发生[/size][/font][size=9pt]commit[/size][font=宋体][size=9pt]的时候，[/size][/font][size=9pt]oracle[/size][font=宋体][size=9pt]可以根据[/size][/font][size=9pt]modified block list[/size][font=宋体][size=9pt]定位到那些块并做[/size][/font][size=9pt]fast commit cleanout[/size][font=宋体][size=9pt]。如果一个[/size][/font][size=9pt]transaction[/size][font=宋体][size=9pt]修改的块超过[/size][/font][size=9pt]10% buffer cache,[/size][font=宋体][size=9pt]那么超过的块就执行[/size][/font][size=9pt]delayed block cleanout[/size][font=宋体][size=9pt]。当做[/size][/font][size=9pt]fast commit cleanout[/size][font=宋体][size=9pt]时，[/size][/font][size=9pt]oracle[/size][font=宋体][size=9pt]不会清理[/size][/font][size=9pt] Row locks lb[/size][font=宋体][size=9pt]标志位，[/size][/font][size=9pt]ITL lck[/size][font=宋体][size=9pt]标志位。[/size][/font][size=9pt] [/size]
[font=宋体][size=9pt]另一种情况是[/size][/font][size=9pt]delayed
block cleanout[/size][font=宋体][size=9pt]，当[/size][/font][size=9pt]transaction[/size][font=宋体][size=9pt]还未[/size][/font][size=9pt]commit[/size][font=宋体][size=9pt]或[/size][/font][size=9pt]rollback[/size][font=宋体][size=9pt]时[/size][/font][size=9pt]modified block[/size][font=宋体][size=9pt]已经被写回磁盘，当发生[/size][/font][size=9pt]commit[/size][font=宋体][size=9pt]时[/size][/font][size=9pt]oracle[/size][font=宋体][size=9pt]并不会把[/size][/font][size=9pt]block[/size][font=宋体][size=9pt]重新读入做[/size][/font][size=9pt]cleanout[/size][font=宋体][size=9pt]，而是把[/size][/font][size=9pt]cleanout[/size][font=宋体][size=9pt]留到下一次对此块的[/size][/font][size=9pt]dml[/size][font=宋体][size=9pt]或[/size][/font][size=9pt]select[/size][font=宋体][size=9pt]。当[/size][/font][size=9pt]delayed cleanout[/size][font=宋体][size=9pt]时候如果[/size][/font][size=9pt]undo segment header[/size][font=宋体][size=9pt]的[/size][/font][size=9pt]transaction table slot[/size][font=宋体][size=9pt]还没有被覆盖，那么可以找回该事务递交的[/size][/font][size=9pt]exact scn[/size][font=宋体][size=9pt]，如果[/size][/font][size=9pt]slot[/size][font=宋体][size=9pt]已经被覆盖，那么将会使用[/size][/font][size=9pt]undo segment header[/size][font=宋体][size=9pt]中的[/size][/font][size=9pt]control scn[/size][font=宋体][size=9pt]来做为[/size][/font][size=9pt]upper bound scn[/size][font=宋体][size=9pt]。[/size][/font][size=9pt][/size]
[size=9pt] [/size]
[size=9pt] [/size]
[font=宋体][size=9pt]下面有一些例子可以阐述几种类型的[/size][/font][size=9pt]block cleanout[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]创建一张表，为了简单起见，每个[/size][/font][size=9pt]block[/size][font=宋体][size=9pt]限制为一行数据[/size][/font][size=9pt][/size]
[size=9pt] [/size]
[size=9pt]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  ;[/size]
[size=9pt]Table created.[/size]
[size=9pt] [/size]
[size=9pt] [/size]
[font=宋体][size=9pt]看一下第一行的[/size][/font][size=9pt]file number,block number[/size]
[size=9pt] [/size]
[align=left][align=left][size=9pt]SQL 10G>select dbms_rowid.ROWID_RELATIVE_FNO(rowid), dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
from test where rownum=1;[/size][/align][/align][size=9pt]DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                  468[/size]
[size=9pt] [/size]
[size=9pt] [/size]
[font=宋体][size=9pt]更新第一行并递交，这时候发生的是[/size][/font][size=9pt]fast commit cleanout[/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>update test set n1=n1 where rownum=1;[/size]
[size=9pt]1 row updated.[/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>commit;[/size]
[size=9pt]Commit complete.[/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>alter system dump datafile 4 block 468;[/size]
[size=9pt]System altered.[/size]
[size=9pt] [/size]
[size=9pt] [/size]
[size=9pt]Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
[/size][size=9pt]0x02 [/size][size=9pt]  0x0003.019.00000d40  0x008009d3.24dd.1a  --U-    [/size][size=9pt]1[/size][size=9pt]  fsc 0x0000.102cdb84

tl: 207 fb: --H-FL-- lb: [/size][size=9pt]0x2[/size][size=9pt]  cc: 2[/size]
[size=9pt]   [/size]
[size=9pt] [/size]
[size=9pt]fast commit cleanout[/size][font=宋体][size=9pt]并不清除[/size][/font][size=9pt]lck,lb[/size][font=宋体][size=9pt]标志。[/size][/font][size=9pt][/size]
[size=9pt] [/size]
[size=9pt] [/size]
[font=宋体][size=9pt]如果出现事务递交前[/size][/font][size=9pt]modified block[/size][font=宋体][size=9pt]就被[/size][/font][size=9pt]flush[/size][font=宋体][size=9pt]回硬盘，那么将发生[/size][/font][size=9pt]delayed block cleanout[/size][font=宋体][size=9pt]。[/size][/font][size=9pt][/size]
[size=9pt] [/size]
[font=宋体][size=9pt]更新记录[/size][/font][size=9pt]:[/size]
[size=9pt]SQL 10G>update test set n1=n1+1;[/size]
[size=9pt]1000 rows updated.[/size]
[size=9pt] [/size]
[size=9pt]flush buffer:[/size]
[size=9pt]SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';[/size]
[size=9pt]Session altered.[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]递交，这时不回去修改[/size][/font][size=9pt]data block[/size][font=宋体][size=9pt]，只会修改[/size][/font][size=9pt]undo segment header slot[/size]
[size=9pt]SQL 10G>commit;[/size]
[size=9pt]Commit complete.[/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>alter system dump datafile 4 block 468;[/size]
[size=9pt]System altered.[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]通过查询来实现[/size][/font][size=9pt]delayed block cleanout[/size]
[size=9pt]SQL 10G>select count(*) from test where rownum=1;[/size]
[size=9pt]  COUNT(*)
----------
         1[/size]
[size=9pt] [/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>alter system dump datafile 4 block 468;[/size]
[size=9pt]System altered.[/size]
[font=宋体][size=9pt]在做[/size][/font][size=9pt]cleanout[/size][font=宋体][size=9pt]以前[/size][/font][size=9pt][/size]
[size=9pt]Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.027.00000d00  0x008000c0.208c.3c  ----    [/size][size=9pt]1[/size][size=9pt]  fsc 0x0000.00000000

tl: 207 fb: --H-FL-- [/size][size=9pt]lb: 0x1 [/size][size=9pt] cc: 2[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]在做[/size][/font][size=9pt]cleanout[/size][font=宋体][size=9pt]以后[/size][/font][size=9pt][/size]
[size=9pt]Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.027.00000d00  0x008000c0.208c.3c  [/size][size=9pt]C---    0 [/size][size=9pt] scn 0x0005.102ce398

tl: 207 fb: --H-FL-- [/size][size=9pt]lb: 0x0[/size][size=9pt]  cc: 2[/size]
[size=9pt] [/size]
[size=9pt] [/size]
[font=宋体][size=9pt]做了[/size][/font][size=9pt]cleanout[/size][font=宋体][size=9pt]后[/size][/font][size=9pt]lck,lb[/size][font=宋体][size=9pt]标志为都被清零，[/size][/font][size=9pt]scn[/size][font=宋体][size=9pt]也是从[/size][/font][size=9pt]undo segment header transaction table slot[/size][font=宋体][size=9pt]里面得到。如果[/size][/font][size=9pt]slot[/size][font=宋体][size=9pt]被覆盖了，那么会把[/size][/font][size=9pt]control scn[/size][font=宋体][size=9pt]拿来当作[/size][/font][size=9pt]upper bound scn[/size][font=宋体][size=9pt]。[/size][/font][size=9pt][/size]
[size=9pt] [/size]
[size=9pt] [/size]
[size=9pt] [/size]
[font=宋体][size=9pt]实验做到这里的时候产生一个疑问，如果在做[/size][/font][size=9pt]delayed block cleanout[/size][font=宋体][size=9pt]之前[/size][/font][size=9pt]undo tablespace[/size][font=宋体][size=9pt]被删除了怎么办，[/size][/font][size=9pt]oracle[/size][font=宋体][size=9pt]从哪里找[/size][/font][size=9pt]undo segment header[/size][font=宋体][size=9pt]呢？是不是[/size][/font][size=9pt]cleanout[/size][font=宋体][size=9pt]会报错？带着疑问继续做实验。[/size][/font][size=9pt][/size]
[size=9pt] [/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>update test set n1=n1-1;[/size]
[size=9pt]1000 rows updated.[/size]
[size=9pt] [/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';[/size]
[size=9pt]Session altered.[/size]
[size=9pt] [/size]
[size=9pt] [/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>show parameter undo_tablespace[/size]
[size=9pt]NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1[/size]
[size=9pt]
SQL 10G>commit;[/size]
[size=9pt]Commit complete.[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]切换[/size][/font][size=9pt]undo tablespace[/size]
[size=9pt]SQL 10G>alter system set undo_tablespace=undotbs2;[/size]
[size=9pt]System altered.[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]删除旧的[/size][/font][size=9pt]undo tablespace[/size]
[size=9pt]SQL 10G>drop tablespace undotbs1 including contents;[/size]
[size=9pt]Tablespace dropped.[/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>!rm /opt/oracle/oradata/dbtest/undotbs01.dbf[/size]
[size=9pt] [/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>alter system dump datafile 4 block 468;[/size]
[size=9pt]System altered.[/size]
[size=9pt] [/size]
[size=9pt]cleanout [/size][font=宋体][size=9pt]并没有出错[/size][/font][size=9pt][/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>select count(*) from test;[/size]
[size=9pt]  COUNT(*)
----------
      1000[/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>alter system dump datafile 4 block 468;[/size]
[size=9pt]System altered.[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]发生[/size][/font][size=9pt]cleanout[/size][font=宋体][size=9pt]以前[/size][/font][size=9pt][/size]
[size=9pt] [/size]
[size=9pt]Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x02   0x000a.015.00001906  0x008000b4.359d.2e  ----    1  fsc 0x0000.00000000[/size]
[size=9pt]tl: 207 fb: --H-FL-- lb: 0x2  cc: 2[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]发生[/size][/font][size=9pt]cleanout[/size][font=宋体][size=9pt]以后[/size][/font][size=9pt][/size]
[size=9pt] [/size]
[size=9pt]Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x02   0x000a.015.00001906  0x008000b4.359d.2e  [/size][size=9pt]C-U-[/size]
[size=9pt] 0[/size][size=9pt]  scn[/size][size=9pt] 0x0005.102ce8f9
[/size][size=9pt]
tl: 207 fb: --H-FL-- [/size][size=9pt]lb: 0x0  [/size][size=9pt]cc: 2[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]发现还是能取到[/size][/font][size=9pt]upper bound scn,[/size][font=宋体][size=9pt]十分奇怪，试想是不是有基表保存已经被删除的[/size][/font][size=9pt]undo segment[/size][font=宋体][size=9pt]信息，仔细一查，[/size][/font][size=9pt]undo$[/size][font=宋体][size=9pt]出现了[/size][/font][size=9pt]!!![/size]
[size=9pt] [/size]
[font=宋体][size=9pt]因为[/size][/font][size=9pt]xid[/size][font=宋体][size=9pt]指向了[/size][/font][size=9pt]0x000a[/size][font=宋体][size=9pt]的[/size][/font][size=9pt]segment header[/size][font=宋体][size=9pt]，所以我们查询[/size][/font][size=9pt]_SYSSMU10$[/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>select name,SCNBAS from undo$ where name='_SYSSMU10$';[/size]
[size=9pt]NAME                               SCNBAS
------------------------------ ----------
_SYSSMU10$                      [/size][size=9pt]271378681[/size][size=9pt][/size]
[size=9pt] [/size]
[size=9pt] [/size]
[size=9pt] [/size]
[font=宋体][size=9pt]来转换一下[/size][/font][size=9pt]upper bound scn[/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>select to_number('102ce8f9','xxxxxxxx') from dual;[/size]
[size=9pt]TO_NUMBER('102CE8F9','XXXXXXXX')
--------------------------------
                      [/size][size=9pt] 271378681[/size][size=9pt][/size]
[size=9pt] [/size]
[size=9pt] [/size]
[font=宋体][size=9pt]正是这个[/size][/font][size=9pt]scn[/size][font=宋体][size=9pt]保存在[/size][/font][size=9pt]undo$[/size][font=宋体][size=9pt]中被拿来当做[/size][/font][size=9pt]upper bound scn.[/size]
[size=9pt] [/size]
[size=9pt] [/size]
[font=宋体][size=9pt]来看一下[/size][/font][size=9pt]undo$[/size]
[size=9pt] [/size]
[size=9pt]SQL 10G>select header_file,header_block,tablespace_name from dba_segments where segment_name='UNDO$';[/size]
[size=9pt]HEADER_FILE HEADER_BLOCK TABLESPACE_NAME
----------- ------------ ------------------------------
          1          105 SYSTEM[/size]
[size=9pt] [/size]
[font=宋体][size=9pt]果然是一个系统基表，[/size][/font][size=9pt]oracle[/size][font=宋体][size=9pt]使用[/size][/font][size=9pt]undo$[/size][font=宋体][size=9pt]来保证[/size][/font][size=9pt]undo tablespace[/size][font=宋体][size=9pt]被删除后的[/size][/font][size=9pt]delayed block cleanout[/size][font=宋体][size=9pt]。[/size][/font][size=9pt][/size]

晕，颜色都没有了，把附件弄上来。

这是一个ORACLE高人写的东西，不错。

看贴要回帖,呵呵

好帖就要顶!谢谢楼主!

感谢楼主 顶!

很好，受益了，谢谢