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