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

AABMuzAAEAAAAITAAA          2 bbbb

SCOTT@test> @rowid AABMuzAAEAAAAITAAA

    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

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;

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

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.

00000000BC78AD68       4         531     1 data block         cr                  1  315968377          3          0          0          0 0000000090D20000

--表T2的state僅僅存在CR。

--觀察alert日志：

--并沒有寫增量檢查點。

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)

lock@8178: 0x02

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。

--alert日志，在輸出一個full checkpoint.

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

update t1 set name='BBBB' where id1=1;

drop table t1;

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

5.通過bbed觀察:

lock@8178: 0x03

--注意看資料塊的内容可以發現dba=4,163的塊的資訊已經寫盤(name内容變成了大寫'BBBB').

--說明T1相關的塊資訊已經寫入資料檔案。

       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 events in waitclass Other                          8              3          75         9.36         38            749168 1736664284    1893977003           0 Other

--你可以看到event='enq: RO - fast object reuse',有增加1次。

--總結：

--從以上的測試說明：drop 以及 truncate時，涉及到的對象髒塊會寫盤。這個叫Object level Checkpoint。