| 无法查询v$session_wait | DBA之路：OoNiceDream |

无法查询v$session_wait

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/04/lock_when_query_vsession_wait.html

RAC环境，一个实例无法查询v$session_wait视图。这次的故障，跟上一篇文章：实例2上语句长时间等待某个块，都是差不多时间点的事情。不同的现象，没写在一起，隔得有点久了，不过当时还是记录了一些信息：

会话一：

SYS@ZJDB2> @login_ZJDB2
 
INSTANCE_NUMBER INSTANCE_NAME   NAME       OPEN_MODE  LOG_MODE        CHECKPOINT_CHANGE#
--------------- --------------- ---------- ---------- --------------- ------------------
              2 ZJDB2           ZJPG       READ WRITE ARCHIVELOG          10739857759850
 
SPID                SID    SERIAL# OSUSER          PROCESS      MACHINE
------------ ---------- ---------- --------------- ------------ --------------------
116596              313       8870 NiceDream       2220:2224    WORKGROUP\OONICEDREA
                                                                M
 
SYS@ZJDB2> select count(*) from v$session_wait;

会话二：
发现直接查询v$session_wait的内部视图倒是可以的，会话的等待事件为：library cache lock，可惜没找到锁住的会话。当时我把所有访问v$session_wait的会话都杀了，再一查，发现还是死在那。有一次查dba_ddl_locks，发现是自己持有锁，使别人在等待，可惜那个现象没记录下来了。

SQL> r
  1  select s.inst_id,
  2         s.indx,
  3         s.ksussseq,
  4         e.kslednam,
  5         s.ksussp1r,
  6         s.ksussp2r,
  7         s.ksussp3r
  8    from x$ksusecst s, x$ksled e
  9   where bitand(s.ksspaflg, 1) != 0
 10     and bitand(s.ksuseflg, 1) != 0
 11     and s.ksussseq != 0
 12     and s.ksussopc = e.indx
 13*    and s.indx=313
 
INST_ID       INDX   KSUSSSEQ KSLEDNAM              KSUSSP1R         KSUSSP2R         KSUSSP3R
------- ---------- ---------- --------------------- ---------------- ---------------- ----------------
      2        313       1313 library cache lock    07000002CB67F2A8 07000002C46A2920 0000000000000515
 
SQL> SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
  2                  WHERE LOCK_B.KGLLKHDL = '07000002CB67F2A8'
  3                  AND KGLLKREQ > 0;
 
KGLLKHDL
----------------
07000002CB67F2A8
07000002CB67F2A8
 
SQL> select inst_id,kglnaown,kglnaobj from x$kglob where KGLHDADR ='07000002CB67F2A8';
 
   INST_ID KGLNAOWN   KGLNAOBJ
---------- ---------- --------------------
         2 SYS        GV$SESSION_WAIT
 
SQL> 
SQL>  SELECT * FROM V$SESSION 
  2   WHERE SADDR in  
  3    (SELECT KGLLKSES FROM X$KGLLK LOCK_A  
  4     WHERE KGLLKREQ = 0
  5     and LOCK_A.KGLLKHDL in
  6              ( SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
  7                  WHERE LOCK_B.KGLLKHDL = '07000002CB67F2A8'
  8                  AND KGLLKREQ > 0 ) 
  9    );
 
no rows selected
 
SQL> 
SQL> oradebug setospid 116596
Oracle pid: 49, Unix process pid: 116596, image: oracle@pg_db_2 (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> oradebug tracefile_name
/app/oracle/admin/ZJPG/udump/zjpg2_ora_116596.trc
SQL> 
SQL> !
[/home/oracle]$ tail -f /app/oracle/admin/ZJPG/udump/zjpg2_ora_116596.trc
WAIT #1: nam='library cache lock' ela= 488298 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488297 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488296 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488300 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488297 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488298 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488299 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488296 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488332 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488295 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488297 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488295 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488546 p1=504403170268017320 p2=504403170150721824 p3=1301
WAIT #1: nam='library cache lock' ela= 488294 p1=504403170268017320 p2=504403170150721824 p3=1301
 
SQL> select to_char('504403170268017320','XXXXXXXXXXXXXXXX') from dual;
 
TO_CHAR('50440317
-----------------
  7000002CB67F2A8