[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。
