徹底搞清楚library cache lock的成因和解決方法(1)

問題描述：
接到應用人員的報告，說是在任何對錶CSNOZ629926699966的操作都會hang，包括desc CSNOZ629926699966,例如：

> sqlplus

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jan 10 10:11:06 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.4.0 - Production

SQL> conn pubuser/pubuser
Connected.
SQL> desc CSNOZ629926699966

。。。

這個程式 hang 了

。。。

詢問了一下之前有無特別的操作，業務人員說很久以前執行了指令碼，但是該教本執行很久都沒有結果，然後他就退出了會話，再之後，就出現了上面的情況。指令碼內容如下：$ cat CSNOZ629926699966.sh
#!/bin/sh
sqlplus << EOF #use your username/password

create table CSNOZ629926699966 as select * from CSNOZ62992266cs
where mid not in ( select mid from where servid='020999011964' and status in ('A','B','S'));

exit;
$
$
$
$

解決過程： > sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jan 10 10:19:13 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.4.0 - Production

SQL> select * from v$lock where block=1;

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL>
我們看到目前沒有鎖的資訊

SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object;

。。。

XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
14 18 37 3

。。。

SQL> /

。。。

XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
14 18 37 3

。。。

SQL> /

。。。

XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
14 18 37 3

。。。

SQL>
查詢 v$locked_object，我們發現了一個可疑的會話，SID 37：

SQL> select object_name,owner,object_type from dba_objects where object_id=18;

。。。 。。。

OBJECT_NAME OWNER OBJECT_TYPE
------------------------------ ------------------------------ ------------------
OBJ$ SYS TABLE

。。。 。。。

SQL>

奇怪怎麼一直有這個鎖？？
初步猜測是由於SID為37的會話執行了上面的DDL語句，並在語句未完成前異常退出，
造成了所有訪問那個（DDL語句中涉及到的）物件的程式都hang了。

接下來我們看看等待事件：
SQL> select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%';

EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
pmon timer 300 0 1
ges remote message 32 0 4
gcs remote message 64 0 5
gcs remote message 64 0 7
smon timer 300 0 19
library cache lock 1.3835E+19 1.3835E+19 30wakeup time manager 0 0 22

7 rows selected.

SQL> /

EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
pmon timer 300 0 1
ges remote message 32 0 4
gcs remote message 64 0 5
gcs remote message 64 0 7
smon timer 300 0 19
library cache lock 1.3835E+19 1.3835E+19 30wakeup time manager 0 0 22

7 rows selected.

SQL> /

EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
pmon timer 300 0 1
ges remote message 32 0 4
gcs remote message 64 0 5
gcs remote message 64 0 7
smon timer 300 0 19
library cache lock 1.3835E+19 1.3835E+19 30wakeup time manager 0 0 22

7 rows selected.

SQL> /

EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
pmon timer 300 0 1
ges remote message 32 0 4
gcs remote message 64 0 5
gcs remote message 64 0 7
smon timer 300 0 19
library cache lock 1.3835E+19 1.3835E+19 30wakeup time manager 0 0 22

7 rows selected.

SQL>

我們注意到下面的事件：
EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
。。。

library cache lock 1.3835E+19 1.3835E+19 30

。。。

P1 是控制程式碼地址(handle address),也就是'library cache lock'發生的地址。
P2 是一個狀態物件，在這裡，它表示在物件上載入的鎖的地址(lock address)。
P1 和 P2都是科學計數發表示的10進位制數。

這些資訊再次證實了上面的猜測，SID 37阻塞了SID 30。

找出這兩個可疑程式的sid和serial，然後對他們設定10046事件：SQL> select sid,serial# from v$session where sid in (30,37);

SID SERIAL#
---------- ----------
30 24167
37 2707

SQL> exec dbms_system.set_ev(30,24167,10046,12,'');

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_ev(37,2707,10046,12,'');

PL/SQL procedure successfully completed.

SQL>

[@more@]