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
SQL>
檢視跟蹤檔案資訊,看資料塊上的資訊,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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
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
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.
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
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
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.
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
SQL>
檢視跟蹤檔案,看到此時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
雖然這個事務已經提交了,不可以回滾了,但是在覆蓋之前,這個前映象資訊仍然存在,透過某些手段,還是可以獲得這個資訊的.比如回閃查詢
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-752458/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle block cleanout塊清除_延遲塊清除OracleBloC
- Oracle Block Cleanouts 塊清除OracleBloC
- Oracle （block clean out） oracle的塊清除OracleBloC
- oracle 塊延遲清除(delayed block cleanout) 理解OracleBloC
- 塊清除的理解
- 使用10203事件來跟蹤oracle塊清除事件Oracle
- zt_使用10203事件event跟蹤Oracle塊清除事件Oracle
- 關於延遲塊清除的原理是什麼?為什麼要進行塊清除
- 清除oracle回收站Oracle
- Oracle RMAN 清除歸檔日誌Oracle
- Oracle RMAN清除歸檔日誌Oracle
- 清除oracle的recyclebin回收站Oracle
- linux清除oracle所有的sessionLinuxOracleSession
- oracle清除資料庫表空間Oracle資料庫
- [Oracle] Oracle11g listener.log清除步驟Oracle
- 清除Oracle中無用索引 改善DML效能Oracle索引
- Oracle9i流環境清除（三）Oracle
- Oracle9i流環境清除（二）Oracle
- Oracle9i流環境清除（一）Oracle
- 清除Oracle中無用索引,改善DML效能Oracle索引
- oracle 10g 歸檔日誌清除Oracle 10g
- 在windows下使用orakill清除oracle執行緒WindowsOracle執行緒
- (轉)Oracle rac環境下清除asm例項OracleASM
- oracle壞塊(二)Oracle
- [20211105]索引分裂 塊清除 日誌增加.txt索引
- oracle 資料庫徹底清除目錄指令碼Oracle資料庫指令碼
- Oracle11gr2 AUDIT清除功能增強（三）Oracle
- 手動清除Oracle 10g RAC CRS的方法Oracle 10g
- Oracle11gr2 AUDIT清除功能增強（四）Oracle
- Oracle11gr2 AUDIT清除功能增強（二）Oracle
- Oracle11gr2 AUDIT清除功能增強（一）Oracle
- oracle10g 清除回收站中垃圾表Oracle
- Oracle資料塊格式Oracle
- Oracle壞塊處理Oracle
- oracle資料塊概述Oracle
- oracle壞塊Block CorruptionsOracleBloC
- oracle資料塊理解Oracle
- Oracle資料塊blockOracleBloC