彻底搞清楚library cache lock的成因和解决方法(7)

根据上述两个ORACLE进程号(ORACLE PID)，我们可以找到他们的会话信息和操作系统进程信息
SQL> select spid,pid,addr from v$process where pid in (26,28);

SPID PID ADDR
------------ ---------- ----------------
20552 26 C000000109C831E0 ----------- 阻塞其他会话的Oracle进程
22580 28 C000000109C83BF0 ----------- 被阻塞的Oracle进程

SQL>

我们来进一步证实一下上述信息：

SQL>col username for a20
SQL> col osuser for a20
SQL> col machine for a20
SQL> l
1 select sid,serial#,username,osuser,machine,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') LogonTime
2* from v$session where paddr in ( select addr from v$process where spid ='&spid')
SQL> /
Enter value for spid: 20552 ----------- 阻塞其他会话的Oracle进程

old 2: from v$session where paddr in ( select addr from v$process where spid ='&spid')
new 2: from v$session where paddr in ( select addr from v$process where spid ='20552')

SID SERIAL# USERNAME OSUSER MACHINE LOGONTIME
---------- ---------- -------------------- -------------------- -------------------- -------------------
37 2707 PUBUSER report16 cs_dc02 2005/01/08 13:00:17

SQL> /
Enter value for spid: 22580 ----------- 被阻塞的Oracle进程

old 2: from v$session where paddr in ( select addr from v$process where spid ='&spid')
new 2: from v$session where paddr in ( select addr from v$process where spid ='22580')

SID SERIAL# USERNAME OSUSER MACHINE LOGONTIME
---------- ---------- -------------------- -------------------- -------------------- -------------------
30 24167 PUBUSER ora9i cs_dc02 2005/01/10 10:20:31

SQL> select sid,saddr,paddr,username,status,OSUSER from v$session where sid in (37,30);

SID SADDR PADDR USERNAME STATUS OSUSER
---------- ---------------- ---------------- -------------------- -------- --------------------
30 C000000109F02C68 C000000109C83BF0 PUBUSER ACTIVE ora9i
37 C000000108C99E28 C000000109C831E0 PUBUSER ACTIVE report16

SQL>
现在，问题已经水落石出了，解决方法和方法1中的一样（在操作系统中直接kill掉相应的操作系统进程）。

当然，处于研究的目的，我们可以进一步了解一下上述两个会话（SID 30 和 SID 37）所有已经持有锁的相关信息：
SQL> set linesize 150
SQL> set pages 10000
SQL> select * from v$lock where sid in (37,30);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
C0000001169403C0 C000000116940538 37 TX 917507 26579 6 0 180478 2
C00000011676DAE0 C00000011676DB08 37 TM 18 0 3 0 180478 2
C00000010B30C4E8 C00000010B30C508 37 XR 4 0 2 0 180369 2
C00000010B30C460 C00000010B30C480 37 DX 21 0 1 0 68 0

SQL>
不难看出，会话37阻塞了其他会话

现在，我们再进一步看看会话37当前在哪些对象上加了锁：
SQL> select object_name,object_id from dba_objects where object_id in ('917507','18','4','21') order by object_id;

OBJECT_NAME OBJECT_ID
------------------------------ ----------
TAB$ 4
OBJ$ 18
COL$ 21

SQL> /

OBJECT_NAME OBJECT_ID
------------------------------ ----------
TAB$ 4
OBJ$ 18
COL$ 21

SQL>

接下来，再着重看看SID 为37的会话在library cache中请求和持有对象锁的详细信息：
SQL> col KGLNAOBJ for a30
SQL> col USER_NAME for a10
SQL> l
1 select INST_ID,USER_NAME,KGLNAOBJ,KGLLKSNM,KGLLKUSE,KGLLKSES,KGLLKMOD,KGLLKREQ
2* from x$kgllk where KGLLKSNM = 37
SQL> /

INST_ID USER_NAME KGLNAOBJ KGLLKSNM KGLLKUSE KGLLKSES KGLLKMOD KGLLKREQ
---------- ---------- ------------------------------ ---------- ---------------- ---------------- ---------- ----------
2 PUBUSER DBMS_OUTPUT 37 C000000108C99E28 C000000108C99E28 1 0
2 PUBUSER DBMS_OUTPUT 37 C000000108C99E28 C000000108C99E28 1 0
2 PUBUSER DBMS_STANDARD 37 C000000108C99E28 C000000108C99E28 1 0
2 PUBUSER PUBUSER 37 C000000108C99E28 C000000108C99E28 1 0
2 PUBUSER SELECT MINOR_VERSION FROM SY 37 C000000108C99E28 C000000108C99E28 1 0
S.CDC_SYSTEM$

2 PUBUSER SELECT MINOR_VERSION FROM SY 37 C000000108C99E28 C000000108C99E28 1 0
S.CDC_SYSTEM$

2 PUBUSER DBMS_CDC_PUBLISH 37 C000000108C99E28 C000000108C99E28 1 0
2 PUBUSER DBMS_CDC_PUBLISH 37 C000000108C99E28 C000000108C99E28 1 0
2 PUBUSER CSNOZ629926699966 37 C000000108C99E28 C000000108C99E28 3 0
2 PUBUSER DBMS_APPLICATION_INFO 37 C000000108C99E28 C000000108C99E28 1 0
2 PUBUSER DBMS_APPLICATION_INFO 37 C000000108C99E28 C000000108C99E28 1 0
2 PUBUSER DATABASE 37 C000000108C99E28 C000000108C99E28 1 0

12 rows selected.

SQL>

再看看SID为30的会话在library cache中请求和持有对象锁的详细信息：
SQL> select INST_ID,USER_NAME,KGLNAOBJ,KGLLKSNM,KGLLKUSE,KGLLKSES,KGLLKMOD,KGLLKREQ
2 from x$kgllk where KGLLKSNM = 30
3 /

INST_ID USER_NAME KGLNAOBJ KGLLKSNM KGLLKUSE KGLLKSES KGLLKMOD KGLLKREQ
---------- ---------- ------------------------------ ---------- ---------------- ---------------- ---------- ----------
2 PUBUSER PUBUSER 30 C000000109F02C68 C000000109F02C68 1 0
2 PUBUSER CSNOZ629926699966 30 C000000109F02C68 C000000109F02C68 0 2
2 PUBUSER DBMS_APPLICATION_INFO 30 C000000109F02C68 C000000109F02C68 1 0
2 PUBUSER DBMS_APPLICATION_INFO 30 C000000109F02C68 C000000109F02C68 1 0
2 PUBUSER DATABASE 30 C000000109F02C68 C000000109F02C68 1 0

SQL>
KGLNAOBJ 列包含了在librarky cache中的对象上执行命令的语句的前80个字符，其实从这里我们也可以大大缩小范围了
KGLLKSES 对应于V$SESSION 中的 SADDR列的值
KGLLKSNM 对应于V$SESSION 中的SID(Session ID)
KGLLKHDL 的值与方法1中跟踪文件中的'handle address'的值对应
KGLLKPNS 的值对应于方法1中跟踪文件中的'Ssession pin'的值

SQL> set linesize 2000
SQL> l
1 select INST_ID,USER_NAME,KGLNAOBJ,KGLLKSNM,KGLLKUSE,KGLLKSES,KGLLKMOD,KGLLKREQ,KGLLKPNS,KGLLKHDL
2* from x$kgllk where KGLLKSNM in (30,37) order by KGLLKSNM,KGLNAOBJ
SQL> /

INST_ID USER_NAME KGLNAOBJ KGLLKSNM KGLLKUSE KGLLKSES KGLLKMOD KGLLKREQ KGLLKPNS KGLLKHDL
---------- ------------------------------ ------------------------------------------------------------ ---------- ---------------- ---------------- ---------- ---------- ---------------- ----------------
2 PUBUSER DATABASE 30 C000000109F02C68 C000000109F02C68 1 0 00 C000000119F8EC58
2 PUBUSER DBMS_APPLICATION_INFO 30 C000000109F02C68 C000000109F02C68 1 0 00 C00000011CCDDA48
2 PUBUSER DBMS_APPLICATION_INFO 30 C000000109F02C68 C000000109F02C68 1 0 00 C00000011CCD81B8
2 PUBUSER PUBUSER 30 C000000109F02C68 C000000109F02C68 1 0 00 C00000011CBFDAA8
2 PUBUSER CSNOZ629926699966 30 C000000109F02C68 C000000109F02C68 0 2 00 C000000122E2A6D8
2 PUBUSER DATABASE 37 C000000108C99E28 C000000108C99E28 1 0 00 C000000119F8EC58
2 PUBUSER DBMS_APPLICATION_INFO 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011CCDDA48
2 PUBUSER DBMS_APPLICATION_INFO 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011CCD81B8
2 PUBUSER DBMS_CDC_PUBLISH 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011FEA4918
2 PUBUSER DBMS_CDC_PUBLISH 37 C000000108C99E28 C000000108C99E28 1 0 00 C0000001202A4988
2 PUBUSER DBMS_OUTPUT 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011CCB48B0
2 PUBUSER DBMS_OUTPUT 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011FFF5098
2 PUBUSER DBMS_STANDARD 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011CCF0ED8
2 PUBUSER PUBUSER 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011CBFDAA8
2 PUBUSER SELECT MINOR_VERSION FROM SYS.CDC_SYSTEM$ 37 C000000108C99E28 C000000108C99E28 1 0 C00000011A44AD70 C00000012029F968
2 PUBUSER SELECT MINOR_VERSION FROM SYS.CDC_SYSTEM$ 37 C000000108C99E28 C000000108C99E28 1 0 00 C0000001202A0228
2 PUBUSER CSNOZ629926699966 37 C000000108C99E28 C000000108C99E28 3 0 00 C000000122E2A6D8

17 rows selected.

SQL>

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18921899/viewspace-1017543/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18921899/viewspace-1017543/