[20150910]關於物件級檢查點.txt
[20150910]關於物件級檢查點.txt
--oracle從8i以後基本使用增量檢查點取代原來的檢查點模式,但是如果一個物件表被drop,truncate時也要將這些髒塊寫檔案.
--這個叫Object level Checkpoint,如何驗證這個過程,實際上很簡單僅僅需要檢查v$bh或者x$bh檢視,或者轉儲髒塊看看裡面
--的內容是否存在變化,來驗證這個過程,還是透過例子來說明:
--我個人喜歡使用bbed觀察，效果與塊轉儲一直。
1.建立測試環境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> show parameter alert
NAME TYPE VALUE
------------------------- -------- --------
log_checkpoints_to_alert boolean TRUE
--設定log_checkpoints_to_alert=true,主要是為了測試需要，如果存在full checkpoint以及增量檢查會寫alert日誌檔案。
create table t1 as select 1 id1 ,cast('aaaa' as varchar2(20)) name from dual ;
create table t2 as select 2 id1 ,cast('bbbb' as varchar2(20)) name from dual ;
SCOTT@test> select rowid,t1.* from t1;
ROWID ID1 NAME
------------------ ---------- --------------------
AABMuyAAEAAAACjAAA 1 aaaa
SCOTT@test> @rowid AABMuyAAEAAAACjAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
314290 4 163 0 4,163 alter system dump datafile 4 block 163 ;
SCOTT@test> select rowid,t2.* from t2;
ROWID ID1 NAME
------------------ ---------- --------------------
AABMuzAAEAAAAITAAA 2 bbbb
SCOTT@test> @rowid AABMuzAAEAAAAITAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
314291 4 531 0 4,531 alter system dump datafile 4 block 531 ;
alter system checkpoint;
--檢視alert日誌:
Fri Sep 11 09:26:47 2015
Beginning global checkpoint up to RBA [0xe09.dcac.10], SCN: 13200870007
Completed checkpoint up to RBA [0xe09.dcac.10], SCN: 13200870007
2.測試:
SCOTT@test> @bh 4 163
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC91E120 4 163 1 data block xcur 1 0 0 0 0 0 000000008FD54000 T1
00000000BC91E120 4 163 1 data block free 0 0 0 0 0 0 0000000093ED4000
SCOTT@test> @bh 4 531
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC78AD68 4 531 1 data block xcur 1 0 0 0 0 0 0000000090D20000 T2
update t1 set name=upper(name) where id1=1;
update t2 set name=upper(name) where id1=2;
SCOTT@test> @bh 4 163
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC91E120 4 163 1 data block xcur 1 0 0 0 0 0 0000000094B60000 T1
00000000BC91E120 4 163 1 data block cr 1 315968373 3 0 0 0 000000008FD54000 T1
00000000BC91E120 4 163 1 data block free 0 0 0 0 0 0 0000000093ED4000
SCOTT@test> @bh 4 531
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC78AD68 4 531 1 data block xcur 1 0 0 0 0 0 00000000925AA000 T2
00000000BC78AD68 4 531 1 data block cr 1 315968377 3 0 0 0 0000000090D20000 T2
--如果仔細看可以發現BA地址(0000000090D20000)的state,在修改後從XCUR變成了CR。
truncate table t2;
SCOTT@test> truncate table t2;
Table truncated.
SCOTT@test> @bh 4 163
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC91E120 4 163 1 data block xcur 1 0 0 0 0 0 0000000094B60000 T1
00000000BC91E120 4 163 1 data block cr 1 315968373 3 0 0 0 000000008FD54000 T1
00000000BC91E120 4 163 1 data block free 0 0 0 0 0 0 0000000093ED4000
SCOTT@test> @bh 4 531
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC78AD68 4 531 1 data block cr 1 315968377 3 0 0 0 0000000090D20000
--表T2的state僅僅存在CR。
--觀察alert日誌：
Fri Sep 11 09:26:47 2015
Beginning global checkpoint up to RBA [0xe09.dcac.10], SCN: 13200870007
Completed checkpoint up to RBA [0xe09.dcac.10], SCN: 13200870007
--並沒有寫增量檢查點。
3.透過bbed觀察:
BBED> set dba 4,163
DBA 0x010000a3 (16777379 4,163)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: aaaa
BBED> set dba 4,531
DBA 0x01000213 (16777747 4,531)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 2
col 1[4] @8183: BBBB
--注意看資料塊的內容可以發現dba=4,531的塊的資訊已經寫盤(name內容變成了大寫),而dba=4,163的塊資訊依舊name內容是小寫。
--說明T2相關的塊資訊已經寫入資料檔案。
SCOTT@test> @spid
SID SERIAL# SPID C50
---------- ---------- ------ --------------------------------------------------
399 1781 21284 alter system kill session '399,1781' immediate;
SCOTT@test> select * from V$SESSION_EVENT where sid=399;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
399 Disk file operations I/O 8 0 0 .03 0 2314 166678035 1740759767 8 User I/O
399 direct path sync 1 0 2 2.41 2 24055 2093619153 1740759767 8 User I/O
399 control file sequential read 14 0 0 0 0 163 3213517201 4108307767 9 System I/O
399 local write wait 3 0 4 1.38 2 41252 1570123276 1740759767 8 User I/O
399 enq: RO - fast object reuse 2 0 5 2.51 5 50110 143262751 4217450380 1 Application
399 log file sync 6 0 5 .89 2 53564 1328744198 3386400367 5 Commit
399 db file sequential read 30 0 15 .52 5 154818 2652584166 1740759767 8 User I/O
399 SQL*Net message to client 52 0 0 0 0 139 2067390145 2000153315 7 Network
399 SQL*Net message from client 51 0 276079 5413.3 185635 2760785228 1421975091 2723168908 6 Idle
399 SQL*Net break/reset to client 5 0 0 0 0 226 1963888671 4217450380 1 Application
399 events in waitclass Other 6 3 37 6.14 37 368495 1736664284 1893977003 0 Other
11 rows selected.
--你可以看到event='enq: RO - fast object reuse',這個就是truncate table t2的緣故。
4.繼續測試drop的情況:
--因為測試的時間問題，主要是避開中間執行增量檢查，先做一次full checkpoint。
alter system checkpoint;
--alert日誌，在輸出一個full checkpoint.
Fri Sep 11 09:26:47 2015
Beginning global checkpoint up to RBA [0xe09.dcac.10], SCN: 13200870007
Completed checkpoint up to RBA [0xe09.dcac.10], SCN: 13200870007
Fri Sep 11 09:46:27 2015
Beginning global checkpoint up to RBA [0xe09.e485.10], SCN: 13200871682
Completed checkpoint up to RBA [0xe09.e485.10], SCN: 13200871682
SCOTT@test> @bh 4 163
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC91E120 4 163 1 data block xcur 1 0 0 0 0 0 0000000094B60000 T1
00000000BC91E120 4 163 1 data block cr 1 315968373 3 0 0 0 000000008FD54000 T1
00000000BC91E120 4 163 1 data block free 0 0 0 0 0 0 0000000093ED4000
update t1 set name='BBBB' where id1=1;
drop table t1;
SCOTT@test> @bh 4 163
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BC91E120 4 163 1 data block free 0 0 0 0 0 0 0000000093F32000
00000000BC91E120 4 163 1 data block cr 1 315969875 3 0 0 0 0000000094B60000
00000000BC91E120 4 163 1 data block cr 1 315968373 3 0 0 0 000000008FD54000
00000000BC91E120 4 163 1 data block free 0 0 0 0 0 0 0000000093ED4000
5.透過bbed觀察:
BBED> set dba 4,163
DBA 0x010000a3 (16777379 4,163)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rncn
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x03
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: BBBB
--注意看資料塊的內容可以發現dba=4,163的塊的資訊已經寫盤(name內容變成了大寫'BBBB').
--說明T1相關的塊資訊已經寫入資料檔案。
SCOTT@test> select * from V$SESSION_EVENT where sid=399;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------------
399 Disk file operations I/O 8 0 0 .03 0 2314 166678035 1740759767 8 User I/O
399 direct path sync 1 0 2 2.41 2 24055 2093619153 1740759767 8 User I/O
399 control file sequential read 14 0 0 0 0 163 3213517201 4108307767 9 System I/O
399 local write wait 3 0 4 1.38 2 41252 1570123276 1740759767 8 User I/O
399 enq: RO - fast object reuse 3 0 6 2.01 5 60152 143262751 4217450380 1 Application
399 log file sync 8 0 7 .9 2 72297 1328744198 3386400367 5 Commit
399 db file sequential read 43 0 19 .45 5 194899 2652584166 1740759767 8 User I/O
399 SQL*Net message to client 63 0 0 0 0 169 2067390145 2000153315 7 Network
399 SQL*Net message from client 62 0 336818 5432.54 185635 3368175121 1421975091 2723168908 6 Idle
399 SQL*Net break/reset to client 5 0 0 0 0 226 1963888671 4217450380 1 Application
399 events in waitclass Other 8 3 75 9.36 38 749168 1736664284 1893977003 0 Other
11 rows selected.
--你可以看到event='enq: RO - fast object reuse',有增加1次。
--總結：
--從以上的測試說明：drop 以及 truncate時，涉及到的物件髒塊會寫盤。這個叫Object level Checkpoint。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1796999/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於oracle的ckpt(檢查點程式)Oracle
- object checkpoint物件檢查點小記Object物件
- 關於資料檔案頭的檢查點SCN
- js關於物件那點事JS物件
- 關於資料檔案頭的檢查點SCN知識
- 2 Day DBA-管理方案物件-關於方案物件管理許可權-管理檢視-關於檢視物件
- oracle檢查點的相關知識Oracle
- 全域性檢查點和增量檢查點
- Oracle 檢查點佇列與增量檢查點Oracle佇列
- 全域性檢查點和增量檢查點（zt）
- 【TUNE_ORACLE】Oracle檢查點（三）增量檢查點四個關鍵引數介紹Oracle
- 對於增量檢查點工作原理的理解
- Oracle完全檢查點和增量檢查點詳解Oracle
- 【TUNE_ORACLE】Oracle檢查點（二）檢查點效能Oracle
- [20150913]檔案檢查點_熱備份.txt
- [20181203]改變檔案大小與檢查點.txt
- oracle checkpoint檢查點Oracle
- OCP知識點講解 之 檢查點佇列與增量檢查點佇列
- 2 Day DBA-管理方案物件-關於方案物件管理許可權-管理檢視-練習：檢視檢視物件
- 【TUNE_ORACLE】Oracle檢查點（一）檢查點（Checkpoint）概念介紹Oracle
- 關於動態配置表檢查工具 (討論帖)
- [20140125]關於標量子查詢.txt
- 關於物件導向程式設計的一點思考物件程式設計
- 關於join查詢的那麼點糾結
- 關於程式碼審查的幾點建議
- 改變ogg抽取程式檢查點檔案中的檢查點
- oracle ckpt檢查點型別（增量及常規完全檢查點)checkpointOracle型別
- 檢查不可用的物件（轉）物件
- [20150308]熱備份和資料庫檢查點.txt資料庫
- [20141202]改變檔案大小與檢查點.txt
- 關於目錄物件物件
- 【TUNE_ORACLE】Oracle檢查點（五）建立並利用Statspack定位檢查點故障Oracle
- 何為程式碼檢查服務的門禁級檢查
- 關於資料庫查詢業務的幾點思考資料庫
- [20150913]檔案檢查點-表空間offline.txt
- 如何檢查一個物件是否為空物件
- 在 JavaScript 中如何檢查物件為空JavaScript物件
- Oracle 檢查點涉及的SCNOracle