块清除(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
虽然这个事务已经提交了,不可以回滚了,但是在覆盖之前,这个前镜像信息仍然存在,通过某些手段,还是可以获得这个信息的.比如回闪查询