用三种方法证明DROP USER(有Table)会触发Object级Checkpoint

预备知识:

 

drop user 能引发checkpoint的前提是 USER下面有TABLE,因为DROP USER 等若隐含了不可flashback drop的DROP TABLE。
如果user下无任何表,则此讨论不成立

 

多种的checkpoint中, drop table 触发的是OBJECT CHECKPOINT

 

其行为为 写出所有节点上属于某个 对象 object 的所有脏dirty buffer到磁盘

可能由以下操作触发:
drop table xx;
drop table xx purge;
truncate table xx;

相关的统计信息有

 

DBWR object drop buffers written
DBWR checkpoints

1、我们通过 统计信息来验证 OBJECT CHECKPOINT

 
SQL> select * from v$VERSION;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bi
PL/SQL Release 10.2.0.5.0 – Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 – Production
NLSRTL Version 10.2.0.5.0 – Production

SQL> create user dropme identified by oracle;

User created.
SQL> grant dba to dropme;

Grant succeeded.

SQL> create table dropme.tab as select * from dba_objects;

Table created.

SQL> delete dropme.tab;

77262 rows deleted.

SQL> commit;

Commit complete.

SQL> select name,value from v$sysstat where name like ‘DBWR%’;

NAME VALUE
—————————————————————- ———-
DBWR checkpoint buffers written 54367
DBWR thread checkpoint buffers written 0
DBWR tablespace checkpoint buffers written 0
DBWR parallel query checkpoint buffers written 0
DBWR object drop buffers written 0
DBWR transaction table writes 2842
DBWR undo block writes 14263
DBWR revisited being-written buffer 0
DBWR make free requests 0
DBWR lru scans 0
DBWR checkpoints 7
DBWR fusion writes 0

12 rows selected.
SQL> drop user dropme cascade;

User dropped.

SQL> select name,value from v$sysstat where name like ‘DBWR%’;

NAME VALUE
—————————————————————- ———-
DBWR checkpoint buffers written 54367
DBWR thread checkpoint buffers written 0
DBWR tablespace checkpoint buffers written 0
DBWR parallel query checkpoint buffers written 0
DBWR object drop buffers written 1167
DBWR transaction table writes 2842
DBWR undo block writes 14263
DBWR revisited being-written buffer 0
DBWR make free requests 0
DBWR lru scans 0
DBWR checkpoints 8
DBWR fusion writes 0

12 rows selected.

 

可以看到 DBWR checkpoints 增长了1, DBWR object drop buffers written 增长了1167

从而证明确实发生了 OBJECT CHECKPOINT

 

2、通过dump buffer 来证明

 

session A:

SQL> create table dropme.tab as select * from dba_objects;
create table dropme.tab as select * from dba_objects
*
ERROR at line 1:
ORA-01918: user ‘DROPME’ does not exist
SQL> create user dropme identified by oracle;

User created.

SQL> grant dba to dropme;

Grant succeeded.

SQL> create table dropme.tab as select * from dba_objects;

Table created.

SQL> delete dropme.tab;

77262 rows deleted.

SQL> commit;

Commit complete.

 

此时SESSION B 上来suspend DBWR让DBWR HANG住:

[oracle@vrh8 ~]$ ps -ef|grep dbw
oracle 6739 1 0 Jul11 ? 00:00:29 ora_dbw0_G10R25
oracle 29514 29491 0 08:16 pts/1 00:00:00 grep dbw
[oracle@vrh8 ~]$
[oracle@vrh8 ~]$
[oracle@vrh8 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Mon Jul 15 08:16:36 2013

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setospid 6739;
Oracle pid: 5, Unix process pid: 6739, image: oracle@vrh8.oracle.com (DBW0)
SQL> oradebug suspend;
Statement processed.
然后SESSION A执行drop user :

SQL> drop user dropme cascade;
由于DBWR无法工作所以 object checkpoint 无法完成所以drop user HANG
此时session C 上来dump buffers level 1;

SQL> oradebug setmypid
Statement processed.

SQL> oradebug dump buffers 1;
Statement processed.
SQL>

SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_29541.trc

 

分析trace , 可以看到大量object checkpoint list上的dirty buffer;

 

[oracle@vrh8 ~]$ grep -B4 -A3 object_ckpt_list /s01/admin/G10R25/udump/g10r25_ora_29541.trc|less
BH (0x71fce868) file#: 4 rdba: 0x010bacbf (4/765119) class: 1 ba: 0x71a6a000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 91
dbwrid: 0 obj: 94007 objn: 94007 tsn: 4 afn: 4
hash: [70fc5298,a5d26830] lru: [71fd0bf8,71fce7d8]
obj-flags: object_ckpt_list
ckptq: [71fce678,71fd0ba8] fileq: [71fce138,71fd4958] objq: [71fd0e88,71fce628]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty gotten_in_current_mode redo_since_read

BH (0x6ffd97b8) file#: 4 rdba: 0x010bada8 (4/765352) class: 1 ba: 0x6fbb4000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 91
dbwrid: 0 obj: 94007 objn: 94007 tsn: 4 afn: 4
hash: [6ffdc018,a5d26d80] lru: [6ffd9948,6ffd9728]
obj-flags: object_ckpt_list
ckptq: [6ffd96d8,6ffd9a08] fileq: [6ffd9088,6ffd9e58] objq: [6ffd9bd8,6ffd9578]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty gotten_in_current_mode redo_since_read

BH (0x6ff9c528) file#: 4 rdba: 0x010bae91 (4/765585) class: 1 ba: 0x6f482000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 91
dbwrid: 0 obj: 94007 objn: 94007 tsn: 4 afn: 4
hash: [6ff9e618,a5d272d0] lru: [6ff9c6b8,6ff9c498]
obj-flags: object_ckpt_list
ckptq: [6ff9c448,6ff9c778] fileq: [6ff9bf08,6ff9ccd8] objq: [6ff9ca58,6ff9c2e8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty gotten_in_current_mode redo_since_read

BH (0x6efe62a8) file#: 4 rdba: 0x010baf7a (4/765818) class: 1 ba: 0x6ed32000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 91
dbwrid: 0 obj: 94007 objn: 94007 tsn: 4 afn: 4
hash: [70f97bd8,a5d27820] lru: [6efe6438,6efe6218]
obj-flags: object_ckpt_list
ckptq: [6efe61c8,6efe64f8] fileq: [6efe4eb8,6efe6948] objq: [6efe66c8,6efe6068]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty gotten_in_current_mode redo_since_read

[oracle@vrh8 ~]$ grep object_ckpt_list /s01/admin/G10R25/udump/g10r25_ora_29541.trc|wc -l
3700
有3700个dirty buffer在object checkpoint list上
之后释放DBWR

SQL> oradebug resume;
Statement processed.
则drop user 顺利完成, 之后再dump buffer:

 

 

SQL> oradebug setmypid
Statement processed.

 

SQL> oradebug dump buffers 1;
Statement processed.

 

SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_29636.trc

[oracle@vrh8 ~]$ grep object_ckpt_list /s01/admin/G10R25/udump/g10r25_ora_29636.trc|wc -l
14

仅有14个dirty buffer在object checkpoint list上了

 
3、通过内部视图来判断

X$ACTIVECKPT代表 活跃的 检查点队列, X$CKTPBUF代表buffer checkpoint queue

X$ACTIVECKPT的 ckpt_type 0代表PQ induced Tablespace/Object checkpoint

7代表Incremental ckpt
10代表object reuse/truncate checkpoint
11 代表object checkpoint;

使用方法2中的步骤:

 

 

SQL> oradebug setospid 6739;
Oracle pid: 5, Unix process pid: 6739, image: oracle@vrh8.oracle.com (DBW0)
SQL> oradebug suspend;
Statement processed.

HANG DBWR

检查 x$activeckpt;

 

SQL> select ckpt_type from x$activeckpt;

CKPT_TYPE
———-
7
0
0
0
0
0
0
0
0

 

之后呢

 

SQL> drop user dropme cascade;

 

 

并查看

 

 

SQL> select ckpt_type from x$activeckpt;

CKPT_TYPE
———-
10 ==》10代表object reuse/truncate checkpoint
10
3
7
0
0
0
0
0
0
0
0

12 rows selected.

 

 
SQL> select count(*),BUF_RBA_SEQ from X$CKPTBUF
2 where BUF_RBA_SEQ!=0
3 group by BUF_RBA_SEQ;

COUNT(*) BUF_RBA_SEQ
———- ———–
3 1356
5 1355
释放dbwr

 

 

SQL> oradebug resume;
Statement processed.

 

 

SQL> select ckpt_type from x$activeckpt;

CKPT_TYPE
———-
7
0
0
0
0
0
0
0
0

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号