v$dead_cleanup on 12.2.0.1

Oracle在11.2.0.4和后续的版本分别引入了V$DEAD_CLEANUP监控dead processes、killed sessions。按照官方文档对V$DEAD_CLEANUP的描述
“V$DEAD_CLEANUP shows the dead processes and killed sessions present in the instance and their cleanup status.”。
本文在Oracle 12.2.0.1环境下进行测试了2次,一次视图的状态信息为RESOURCES FREED,一次为CLEANUP PENDING。
使用json用户登录数据库,获取到的session信息如下

SQL> col username for a10
SQL> select sid,serial#,paddr,saddr,username,status from v$session where username = 'JSON';

SID SERIAL# PADDR SADDR USERNAME STATUS
---------- ---------- ---------------- ---------------- ---------- ----------------
138 63930 000000007DCC8E48 000000007C147988 JSON INACTIVE

SQL> set linesize 200
SQL> col tracefile for a70
SQL> select addr,pid,spid,stid,execution_type,tracefile from v$process where addr = '000000007DCC8E48';

ADDR PID SPID STID EXECUTION_ TRACEFILE
---------------- ---------- ---------- -------- ---------- ----------------------------------------------------------------------
000000007DCC8E48 65 19675 19675 PROCESS /orasoft/oracle/diag/rdbms/ywdb/ywdb/trace/ywdb_ora_19675.trc

Kill杀掉会话

SQL> alter system kill session '138,63930';

系统已更改。

SQL> select sid,serial#,paddr,saddr,username,status from v$session where username = 'JSON';

SID SERIAL# PADDR SADDR USERNAME STATUS
---------- ---------- ---------------- ---------------- ---------- ----------------
138 63930 000000007DCC8E48 000000007C147988 JSON KILLED

SQL> col type for a20
SQL> col cleanup_owner for a30
SQL> col state for a30

SQL> select type,paddr,saddr,root_addr,cleanup_owner,state,dead_time,con_id from V$DEAD_CLEANUP t;

TYPE PADDR SADDR ROOT_ADDR CLEANUP_OWNER STATE DEAD_TIME CON_ID
-------------------- ---------------- ---------------- ---------------- ------------------------------ 
KILLED SESSION 000000007DCC8E48 000000007C147988 000000007C147988 OWNER PROCESS RESOURCES FREED 102 3

SQL> select addr,pid,spid,stid,execution_type,tracefile from v$process where addr = '000000007DCC8E48';

未选定行

进程信息已经从v$process中删除

再次进行测试

SQL> alter system kill session '134,10324';

系统已更改。

SQL> col name for a5
SQL> col name for a8
SQL> select sid,serial#,username,status,saddr,paddr from v$session where username = 'JSON';

       SID    SERIAL# USERNAME STATUS           SADDR            PADDR
---------- ---------- -------- ---------------- ---------------- ----------------
       134      10324 JSON     KILLED           000000007C151068 000000007DCA3A08
SQL> col type for a25
SQL> col CLEANUP_OWNER for a25
SQL> col state for a25
SQL> select type,paddr,saddr,root_addr,cleanup_owner,state,dead_time,con_id from V$DEAD_CLEANUP t;

TYPE                      PADDR            SADDR            ROOT_ADDR        CLEANUP_OWNER             STATE                      DEAD_TIME     CON_ID
------------------------- ---------------- ---------------- ---------------- ------------------------- ------------------------- ---------- ----------
KILLED SESSION            000000007DCA3A08 000000007C151068 000000007C151068 OWNER PROCESS             CLEANUP PENDING                  274          3

SQL> select * from V$CLEANUP_PROCESS;

NAME     PADDR            SADDR            STATE                     DEAD_IN_CLEANUP  CLEANUP_TIME TIME_SINCE_LAST_CLEANUP NUM_CLEANED     CON_ID
-------- ---------------- ---------------- ------------------------- ---------------- ------------ ----------------------- ----------- ----------
PMON     000000007DC87AD8 000000007C28A900 IDLE                      00                          0                 1889383           0          0
CLMN     000000007DC88B68 000000007C3B13D8 IDLE                      00                          0                 1889383           0          0

SQL> select type,paddr,saddr,root_addr,cleanup_owner,state,dead_time,con_id from V$DEAD_CLEANUP t;

TYPE                      PADDR            SADDR            ROOT_ADDR        CLEANUP_OWNER             STATE                      DEAD_TIME     CON_ID
------------------------- ---------------- ---------------- ---------------- ------------------------- ------------------------- ---------- ----------
KILLED SESSION            000000007DCA3A08 000000007C151068 000000007C151068 OWNER PROCESS             CLEANUP PENDING                  587          3

采用event=’10246 trace name context forever, level 10′ 跟踪pmon和clmn进程并没有得到有趣的信息。

Footnote:
对session进行kill,在alert日志文件中会进行记录信息

BLUE(3):Opening pdb with no Resource Manager plan active
Pluggable database BLUE opened read write
Completed: alter pluggable database blue open
2017-03-14T13:00:22.029236+08:00
BLUE(3):KILL SESSION for sid=(266, 1517):
BLUE(3):  Reason = alter system kill session
BLUE(3):  Mode = KILL SOFT -/-/-
BLUE(3):  Requestor = USER (orapid = 63, ospid = 14620, inst = 1)
BLUE(3):  Owner = Process: USER (orapid = 62, ospid = 14611)
BLUE(3):  Result = ORA-0
2017-03-14T13:12:41.833469+08:00
此条目发表在12c分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s