ITPub博客

首页 > 数据库 > Oracle > 11g里的延迟块清除

11g里的延迟块清除

原创 Oracle 作者:oliseh 时间:2014-11-04 00:16:33 0 删除 编辑

 


实验目的:oracle 11gR2 里的delayed block cleanout特性研究

 

基础知识:

Fast commit cleanout

   DML语句后,在被修改的blockbuffer cache写回到磁盘之前执行commit。这种情况下,更新block scnblock itl事务槽上的scn,以及undo segment header事务表相应槽位上的scn,这三个scn是一致的,并且更新Itl槽位上的Flag--U-的形式。Fast commit cleanout发生后不会清理Itl Lckrow data里的lb字段信息。

 

Delayed block cleanout

   DML语句后,在被修改的blockbuffer cache写回到磁盘之后执行commit。更新的只是undo segment header 事务表相应槽位上的scnItl lck标志,而并未做Itl里其它字段的更新,等待下次DML(多个itl slot)或者select语句(单个itl slot)访问此block的时候再对Itl FlagItl lckItl scn/fsc字段作更新。实测下来11gdelayed block cleanout的行为在以下两种场景中略有不同:

(1)     使用到data block中前两个预分配的Itl slottransaction按照delayed block cleanout的条件(DML->flush buffer_cache->commit)去模拟,其cleanout的行为仅由访问该blockDML语句触发,select语句并不会触发,这一个特点与fast commit cleanout有些相似:更新DML语句所使用的slot上的Itl FlagItl lckItl scn(block scn的值相同),同时对另一个曾经被已提交事务占用的slot进行cleanout操作(加上C---标志)Lewis把此现象称作:delayed logging block cleanout,以下是他对此现象的描述:

let’s go back to a block that was subject to commit cleanout. If you query this block you can see the effects of the committed transaction, but since the commit cleanout wrote the commit SCN into its ITL slot you know when the transaction committed and generally don’t need to do anything to tidy the block up as you read it. However if you want to update the block you become responsible for finishing the cleanout of the block – in fact you may even want to re-use that ITL entry and update some of the rows which are still showing a lock byte. So, at this point, you complete the block cleanout, and generate redo that describes not only the changed you are now making but also the earlier commit cleanout.  This process is known as ‘delayed logging’ block cleanout – because the redo log for the commit cleanout has been delayed until this moment

This process is known as ‘delayed logging’ block cleanout – because the redo log for the commit cleanout has been delayed until this moment.This process is known as ‘delayed logging’ block cleanout – because the redo log for the commit cleanout has been delayed until this moment.

(2)     使用到自动分配的Itl slottransaction是严格按照delayed block cleanout的本义执行的:commit后不更新Itl中的Flag Scn/Fsc这两个字段值,等到下一次DMLselect访问该block之后并且进行了flush buffer_cache操作后才进行更新,同时更新的还有block scncsc等字段

 

实验步骤:

####实验使用scott.emp表,先对表的block做一个dump,得到初始状态

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

/oracle/app/oracle/diag/rdbms/zwcsdb/zwcsdb/trace/zwcsdb_ora_30605382.trc   --初始dump

 

scn: 0x0b78.2772cef5 csc: 0xb78.2772cef3

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01[C1]    0x0001.014.00020128  0x00c00179.4506.17  --U-    1  fsc 0x0000.2772cef5 

0x02   0x000b.01b.00020e1b  0x00c00620.29ed.22  C---    0  scn 0x0b78.2772ce94

 

####fast commit cleanout 1st time,占用了0x02,因为该slot已经被cleanout

update scott.emp set ename='SMITH' where ename='SMITHHHHHH';

 

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

zwcsdb_ora_15597762.trc   -- fast commit cleanout /scn: 0x0b78.2776cebb csc: 0xb78.2772cef3

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0001.014.00020128  0x00c00179.4506.17  --U-    1  fsc 0x0000.2772cef5

0x02   0x000b.01d.000210c5  0x00c012f9.2ae6.04  --U-    1  fsc 0x0005.2776cebb 

 

####fast commit cleanout 2nd time,占用了0x01,其实此时0x02也可以被占用,因为0x02transaction也已经提交了,可见oracle会根据scn/fsc选择更早释放出的slot,同时对另一个已经释放出来的slot进行cleanoutcsc已经较上面推进了

update scott.emp set ename='ALLEN' where ename='ALLENNNN';

 

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

zwcsdb_ora_22151348.trc  -- fast commit cleanout /scn: 0x0b78.2776cf18 csc[C2] : 0xb78.2776cf15

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01[C3]    0x000b.01c.000210d5  0x00c012f9.2ae6.05  --U-    1  fsc 0x0003.2776cf18 

0x02   0x000b.01d.000210c5  0x00c012f9.2ae6.04  C---    0  scn 0x0b78.2776cebb

 

####delayed block cleanout 1st time,更新了itl scn block scn(两者值相等),但csc未更新,没有发生block cleanout,重用了slot 0x02FlagC---改成了--U-,

 

update scott.emp set ename='JONESSSS' where ename='JONES';

 

alter system flush buffer_cache;

 

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

zwcsdb_ora_32506056.trc  --delayed block cleanout after flush buffer_cache

 

scn: 0x0b78.2776d0a2 csc: 0xb78.2776cf15

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000b.01c.000210d5  0x00c012f9.2ae6.05  --U-    1  fsc 0x0003.2776cf18

0x02[C4]    0x000b.01b.000210de  0x00c012fa.2ae6.16  --U-    1  fsc 0x0000.2776d0a2

 

####delayed block cleanout 2nd time,使用slot 0x01cleanout slot 0x02csc值比dml commitscn稍小一些,可推断cleanoutdml commit所引发的

update scott.emp set ename='MARTINNNNN' where ename='MARTIN';

 

alter system flush buffer_cache;

 

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

zwcsdb_ora_15007812.trc  --delay block cleanout 2nd time plus flush buffer_cache /scn: 0x0b78.2776d2a0 csc: 0xb78.2776d29b

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01[C5]    0x000b.01f.00020f19  0x00c0074f.2ae7.0a  --U-    1  fsc 0x0000.2776d2a0 

0x02   0x000b.01b.000210de  0x00c012fa.2ae6.16  C---    0  scn 0x0b78.2776d0a2 

 

 

阶段结论:在使用block里系统预分配的两个slot的情况下,delayed block cleanoutfast commit cleanout两种方式基本无区别,它们的行为都是:占用Itl的两个slot里更早释放出来的那个slot->更新FlagLckScn/Fsc->cleanout剩余的已经commitslot(仅当两个slotFlag初始状态都为--U-的情况下才会做此操作,若两个slot的初始状态一个是--U-、一个是C---的情况下不会做此操作)->推进block scncsc


 

####模拟将预分配的两个slot都占满的情况

session 1

update scott.emp set ename='BLAKEEEE' where ename='BLAKE';

 

session 2:

update scott.emp set ename='CLARKKKK' where ename='CLARK';

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

zwcsdb_ora_22151216.trc --- scn: 0x0b78.2776d748 csc: 0xb78.2776d747

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.000.00040f70  0x00c0060d.558b.1b  ----    1  fsc 0x0000.00000000

0x02   0x000b.019.00020f8d  0x00c00751.2ae7.1d  ----    1  fsc 0x0000.00000000

 

####开启第3session进行fast commit cleanout,新开出了第3slotcsc比上一轮推进了,可以推断发生了cleanout但是没有看到Itl Flag字段的C标志

 

session 3:

update scott.emp set ename='SCOTTTTT' where ename='SCOTT';

 

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

 

zwcsdb_ora_25559182.trc  --- scn: 0x0b78.2776d7f1 csc: 0xb78.2776d7ec

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.000.00040f70  0x00c0060d.558b.1b  ----    1  fsc 0x0000.00000000

0x02   0x000b.019.00020f8d  0x00c00751.2ae7.1d  ----    1  fsc 0x0000.00000000

0x03   0x0006.01d.0001b2f5  0x00c000d3.41fa.1e  --U-    1  fsc 0x0000.2776d7f1

 

####开启第4session进行fast commit cleanout,重用第3slot,又发生了cleanout,因为csc又推进了

session 4:

update scott.emp set ename='KINGGGG' where ename='KING';

 

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

zwcsdb_ora_24772802.trc  --- scn: 0x0b78.2776d86a csc: 0xb78.2776d864

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.000.00040f70  0x00c0060d.558b.1b  ----    1  fsc 0x0000.00000000

0x02   0x000b.019.00020f8d  0x00c00751.2ae7.1d  ----    1  fsc 0x0000.00000000

0x03   0x0009.01f.0002e43a  0x00c0253c.3571.1a  --U-    1  fsc 0x0000.2776d86a

 

####开启第5session模拟delayed block cleanout,发现竟然出现了所谓的delayed block cleanout现象,对于下面的0x03槽位,commitFlagscn/fsc标志位没有更改

session 5:

update scott.emp set ename='TURNERRRR' where ename='TURNER';

 

alter system flush buffer_cache;

 

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

zwcsdb_ora_11534458.trc  --- scn: 0x0b78.2776d9cf csc: 0xb78.2776d9ca

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.000.00040f70  0x00c0060d.558b.1b  ----    1  fsc 0x0000.00000000

0x02   0x000b.019.00020f8d  0x00c00751.2ae7.1d  ----    1  fsc 0x0000.00000000

0x03   0x0009.001.0002d621  0x00c0160f.3574.04  ----    1  fsc 0x0000.00000000

 

 

####另开一个sessionscott.empselect发现出现了redosize

 

SQL> set autotrace on stat;

SQL> select * from scott.emp;

 

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

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

    DEPTNO

----------

      7369 SMITH      CLERK           7902 17-DEC-80           800

        20

 

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300

        30

 

      7521 WARDDD     SALESMAN        7698 22-FEB-81          1250        500

        30

 

 

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

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

    DEPTNO

----------

      7566 JONESSSS   MANAGER         7839 02-APR-81          2975

        20

 

      7654 MARTINNNNN SALESMAN        7698 28-SEP-81          1250       1400

        30

 

      7698 BLAKE      MANAGER         7839 01-MAY-81          2850

        30

 

 

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

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

    DEPTNO

----------

      7782 CLARK      MANAGER         7839 09-JUN-81          2450

        10

 

      7788 SCOTTTTT   ANALYST         7566 19-APR-87          3000

        20

 

      7839 KINGGGG    PRESIDENT            17-NOV-81          5000

        10

 

 

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

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

    DEPTNO

----------

      7844 TURNERRRR  SALESMAN        7698 08-SEP-81          1500          0

        30

 

      7876 ADAMS      CLERK           7788 23-MAY-87          1100

        20

 

      7900 JAMES      CLERK           7698 03-DEC-81           950

        30

 

 

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

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

    DEPTNO

----------

      7902 FORD       ANALYST         7566 03-DEC-81          3000

        20

 

      7934 MILLER     CLERK           7782 23-JAN-82          1300

        10

 

 

14 rows selected.

 

 

Statistics

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

          0  recursive calls

          0  db block gets

         17  consistent gets

         12  physical reads

        116  redo size

       1648  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

 

####紧接着对block再次做dumpdump内容和上一次一样

 

####第二次select * from scott.emp上还是有redo size发生,此时再次dumpdump内容还是没有变化, 接着再n次的select * from scott.emp,每次都伴随有Redo size

 

####n[C6] dump block发现delayed block cleanout终于如期而至,且此时scncsc一致,Flag标志变为C---

zwcsdb_ora_8978604.trc(第三次dump的内容) --scn: 0x0b78.2776db60 csc: 0xb78.2776db60

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.000.00040f70  0x00c0060d.558b.1b  ----    1  fsc 0x0000.00000000

0x02   0x000b.019.00020f8d  0x00c00751.2ae7.1d  ----    1  fsc 0x0000.00000000

0x03   0x0009.001.0002d621  0x00c0160f.3574.04  C---    0  scn 0x0b78.2776d9d2

 

####再模拟一次delayed block cleanout

 

update scott.emp set ename='JAMESSSS' where ename='JAMES';

 

alter system flush buffer_cache;

 

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

zwcsdb_ora_27263036.trc  -- scn: 0x0b78.2776de44 csc: 0xb78.2776de41

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.000.00040f70  0x00c0060d.558b.1b  ----    1  fsc 0x0000.00000000

0x02   0x000b.019.00020f8d  0x00c00751.2ae7.1d  ----    1  fsc 0x0000.00000000

0x03   0x0009.012.0002ddd5  0x00c01616.3574.18  ----    1  fsc 0x0000.00000000

 

####select + flush buffer_cache触发delayed block cleanout

select * from scott.emp;

Statistics

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

          0  recursive calls

          0  db block gets

         17  consistent gets

         15  physical reads

        116  redo size

       1654  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

 

Alter system flush buffer_cache

 

scn: 0x0b78.2776de63 csc: 0xb78.2776de63    

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.000.00040f70  0x00c0060d.558b.1b  ----    1  fsc 0x0000.00000000

0x02   0x000b.019.00020f8d  0x00c00751.2ae7.1d  ----    1  fsc 0x0000.00000000

0x03   0x0009.012.0002ddd5  0x00c01616.3574.18  C---    0  scn 0x0b78.2776de47 

 

####最后再进行一次fast commit cleanoutcsc比上一次有所推进,明显发生了delayed block cleanout,但cscblock scn值不等

update scott.emp set ename='FORDDDD' where ename='FORD';

 

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 4 block 151;

 

select * from v$diag_info;

 

zwcsdb_ora_13828174.trc -- scn: 0x0b78.2776dedc csc: 0xb78.2776ded7

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000a.000.00040f70  0x00c0060d.558b.1b  ----    1  fsc 0x0000.00000000

0x02   0x000b.019.00020f8d  0x00c00751.2ae7.1d  ----    1  fsc 0x0000.00000000

0x03   0x0008.01a.0002ab95  0x00c01593.3ea2.14  --U-    1  fsc 0x0000.2776dedc

 

 

阶段结论:在只有一个slot的情况下(当然前提是一个slot够用),每次dml后都会触发cleanout ,对于fast commit cleanout是只能通过dml语句触发才能看到cleanout现象,对于delayed block cleanout则可以通过select操作+flush buffer_cache观察到cleanout现象(当然也可以通过DML语句)。,fast commit cleanout场景下,block scn大于csc;在delayed block cleanout场景下,delayed block cleanout发生时block scncsc值相同,且这个值要大于itl scn值。


 


 [C1]---这个slot2天前update scott.emp set ename='WARDDD' where empno=7521语句使用的,还没有cleanout

 [C2]比上面的csc值推进了,可见发生了cleanout

 [C3]---update scott.emp set ename='ALLEN' where ename='ALLENNNN' 使用的slot

 [C4]---update scott.emp set ename='JONESSSS' where ename='JONES'使用的slot

 [C5]---update scott.emp set ename='MARTINNNNN' where ename='MARTIN'使用的slot

 [C6]后面的步骤可以证明select 后还需要flush buffer_cache才能更快的触发cleanout

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

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1617053