2013年7月15日
用三种方法证明DROP USER(有Table)会触发Object级Checkpoint
M
mac
Author
16 min
Read Time
49
Views
预备知识：
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