oracle 块清除

块清除(Block Cleanouts)

当用户发出提交(commit)之后,oracle怎样来处理的.oracle是需要写出redo来保证故障时数据可以被
恢复,我们知道oracle并不需要在提交时就写出变更的数据块.那么在提交时,oracle会对数据块进行什么操作?

在事务需要修改数据时,必须分配ITL事务槽,必须锁定行,必须分配回滚段事务槽和回滚表空间来记录要修改
的数据的前镜像.当事务提交时,oracle需要将回滚段上的事务表信息标记为非活动,以便空间可以被重用
那么还有ITL事务信息和锁定信息需要清除,以记录提交.

由于oracle在数据块上存储了ITL和锁定等事务信息,所以oracle必须在事务提交之后清除这些事务数据,
这就是块清除.块清除主要清除的数据有行级锁,ITL信息(包括提交标志,scn等).

如果提交时修改过的数据块仍然在buffer cache中,那么oracle可以清除ITL信息,这种清除叫做快速块清除
(fast block cleanout),快速块清除还有一个限制,当修改的块数量超过buffer cache的10%,则对超出的部
分不再进行快速块清除.

如果提交事务时,修改过的数据块已经被写回到数据文件上(或大量修改超出10%的部分),再次读出该数据块
进行修改,显然成本过于高昂,对于这种情况,oracle选择延迟块清除(delayed block cleanout),等到下一次
访问该block时再来清除ITL锁定信息,这就是延迟块清除.oracle通过延迟块清除来提高数据库的性能,加快
提交操作.快速提交是最普遍的情况.来看一下延迟块清除的处理.
进行测试:

SQL> update emp set sal=4000 where empno=7788;
1 row updated.

SQL> update emp set sal=4000 where empno=7782;

1 row updated.

SQL> update emp set sal=4000 where empno=7698;

1 row updated.

更新完成之后,强制刷新buffer cache,将buffer cache中的数据都写出到数据文件:

SQL> alter session set events='immediate trace name flush_cache';

Session altered

此时再提交事务;

SQL> commit;

Commit complete.

由于此时更新过的数据已经写出到数据文件,oracle将执行延迟块清除,将此时的数据块和回滚段转储出来:

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 10:18:56 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5023.trc

查看跟踪文件信息,看数据块上的信息,ITL事务信息仍然存在:
其中scn表示提交commit scn,fsc表示快速提交scn

*** 2013-01-07 10:19:33.032
*** SERVICE NAME:(SYS$USERS) 2013-01-07 10:19:33.031
*** SESSION ID:(140.421) 2013-01-07 10:19:33.031
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601
frmt: 0x02 chkval: 0xf364 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e6ebb  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  ----    3  fsc 0x0002.00000000
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495
xid=0x0009.01d.00000181的事务lck=3

数据块的锁定信息仍然存在:

tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

再来看回滚段的信息:

*** 2013-01-07 10:20:01.417

********************************************************************************
Undo Segment:  _SYSSMU9$ (9)
********************************************************************************

Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00800546.0129.18 ext: 0xa  spc: 0x12ea
    uba: 0x00000000.0129.05 ext: 0xa  spc: 0x1e08
    uba: 0x00000000.0129.42 ext: 0xa  spc: 0x73e
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
  TRN TBL::

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x1d    9    0x00  0x0181  0xffff  0x0000.000e743c  0x00800546  0x0000.000.00000000  0x00000001   0x00000000  1357525082

事务提交,事务表已经释放。如果此时查询scott.emp表,数据库将产生延迟块清除:

SQL> set autotrace on
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 WARD       SALESMAN        7698 22-FEB-81          1250        500        30
      
      7566 JONES      MANAGER         7839 02-APR-81          2975         20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400        30

      7698 BLAKE      MANAGER         7839 01-MAY-81          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         20

      7839 KING       PRESIDENT            17-NOV-81          5000         10
      
      7844 TURNER     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
      
      7902 FORD       ANALYST         7566 03-DEC-81          3000         20

      7934 MILLER     CLERK           7782 23-JAN-82          1300         10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
      345 redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

注意,在此查询是产生了物理读取和redo,这个redo就是因为延迟块清除导致的,再次查询则不会
产生redo了:

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 WARD       SALESMAN        7698 22-FEB-81          1250        500        30
      
      7566 JONES      MANAGER         7839 02-APR-81          2975         20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400        30

      7698 BLAKE      MANAGER         7839 01-MAY-81          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         20

      7839 KING       PRESIDENT            17-NOV-81          5000         10


      7844 TURNER     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

      7902 FORD       ANALYST         7566 03-DEC-81          3000         20

      7934 MILLER     CLERK           7782 23-JAN-82          1300         10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

再次转储一下该数据块和回滚段:

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5043.trc

查看跟踪文件,看到此时ITL事务信息已经清除,但是注意,这里的xid和uba信息仍然存在:

Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e7560 seq: 0x01 flg: 0x00 tail: 0x75600601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e7560  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  C---    0  scn 0x0000.000e743c
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495

数据块块的锁定位也已经清除了:
tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

提交之后的undo信息
当提交事务之后,回滚段事务表标记该事务为非活动,继续再来看一下回滚段数据块的信息,
看到这里的irb指向了0x28,此前的事务已经不可回滚

********************************************************************************
UNDO BLK:
xid: 0x0009.02f.00000181  seq: 0x129 cnt: 0x28  irb: 0x28  icl: 0x0   flg: 0x0000

看一下偏移量列表也已经新增了一条信息0x28 0x0a4c

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c     0x02 0x1ed0     0x03 0x1e54     0x04 0x1de8     0x05 0x1d3c
0x06 0x1c90     0x07 0x1c24     0x08 0x1b78     0x09 0x1acc     0x0a 0x1a20
0x0b 0x1974     0x0c 0x1908     0x0d 0x189c     0x0e 0x17f0     0x0f 0x1784
0x10 0x1718     0x11 0x166c     0x12 0x1600     0x13 0x1594     0x14 0x14e8
0x15 0x147c     0x16 0x13e4     0x17 0x1388     0x18 0x132c     0x19 0x1280
0x1a 0x11d4     0x1b 0x1128     0x1c 0x10ac     0x1d 0x1000     0x1e 0x0f54
0x1f 0x0ee8     0x20 0x0e7c     0x21 0x0e10     0x22 0x0da4     0x23 0x0cf8
0x24 0x0c4c     0x25 0x0ba0     0x26 0x0b24     0x27 0x0ab8     0x28 0x0a4c

再看前镜像0x18 0x132c的信息,仍然存在:

*-----------------------------
* Rec #0x18  slt: 0x1d  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x17
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800546.0129.17
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 1d 33

虽然这个事务已经提交了,不可以回滚了,但是在覆盖之前,这个前镜像信息仍然存在,通过某些手段,还是可以获得这个信息的.比如回闪查询

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.