如何证明 drop user 语句会触发checkpoint ?

iongxiao

如何证明 drop user 语句会触发checkpoint ?

Maclean Liu(刘相兵

预备知识：“

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

Maclean Liu(刘相兵

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

Maclean Liu(刘相兵

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上了

Maclean Liu(刘相兵

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

gdpr-dba

刘大威武，还是方法一比较实用，后面的方法有点复杂。

wendy

刘大真是给力！