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

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

ora9i@cs_dc02:/ora9i > sqlplus pubuser/pubuser

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 pubuser/pubuser@csmisc << EOF #use your username/password

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

exit;
$
$
$
$

解決過程：ora9i@cs_dc02:/ora9i > 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>

跟蹤期間我們們再次測試一下，看看有沒有其他線索。

新開一個程式，找出其sid, serial和spid等資訊：ora9i@cs_dc01:/ora9i > sqlplus pubuser/pubuser

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jan 10 11:36:25 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 distinct sid from v$mystat;

SID
----------
33

SQL> select sid,serial# from v$session where sid=33;

SID SERIAL#
---------- ----------
33 6639

SQL> SELECT SPID,PID FROM V$PROCESS WHERE ADDR=(SELECT PADDR FROM V$SESSION WHERE SID=37);

SPID PID
------------ ----------
20552 26

SQL> SELECT SPID,PID FROM V$PROCESS WHERE ADDR=(SELECT PADDR FROM V$SESSION WHERE SID=30);

SPID PID
------------ ----------
22580 28

SQL> show parameter dump

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /ora9i/app/oracle/admin/csmisc
/bdump
core_dump_dest string /ora9i/app/oracle/admin/csmisc
/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /ora9i/app/oracle/admin/csmisc
/udump
SQL>

然後，再嘗試對 CSNOZ629926699966 表進行操作
SQL> desc CSNOZ629926699966

。。。

還是hang住了。

於是中斷這個操作（CTRL + C）：

SQL> desc CSNOZ629926699966
ERROR:
ORA-01013: user requested cancel of current operation

SQL> select tname from tab where tname='CSNOZ629926699966';

no rows selected

SQL> 檢視PUBUSER使用者下的這個表，居然不存在！！

進一步證實了前面的猜測，也就是說會話37阻塞了其他所有操作表CSNOZ629926699966的會話，造成了程式的hang，當然，包括上面的SID 30和SID 33的DDL語句

現在，我們結束10046的事件跟蹤：SQL> exec dbms_system.set_ev(30,24167,0,0,'');

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL>

根據上面記錄的資訊，我們知道這兩個會話產生的跟蹤資訊分別為：
SID為30的會話，產生的跟蹤檔案為：/ora9i/app/oracle/admin/csmisc/udump/csmisc2_ora_22580.trc
SID為37的會話，產生的跟蹤檔案為：/ora9i/app/oracle/admin/csmisc/udump/csmisc2_ora_20552.trc

看看trace檔案：
ora9i@cs_dc02:/ora9i > cd /ora9i/app/oracle/admin/csmisc/udump
ora9i@cs_dc02:/ora9i/app/oracle/admin/csmisc/udump > ll -tlc
total 4432
-rw-r----- 1 ora9i dba 332995 Jan 10 12:00 csmisc2_ora_22580.trc
-rw-r----- 1 ora9i dba 3168 Jan 10 11:59 csmisc2_ora_20552.trc-rw-r----- 1 ora9i dba 407133 Jan 7 15:10 csmisc2_ora_2708.trc
-rw-r----- 1 ora9i dba 640 Jan 7 14:48 csmisc2_ora_835.trc
-rw-r----- 1 ora9i dba 1590 Dec 30 22:50 csmisc2_ora_16244.trc
-rw-r----- 1 ora9i dba 1308403 Dec 30 22:44 csmisc2_ora_16033.trc
-rw-r----- 1 ora9i dba 616 Dec 28 14:16 csmisc2_ora_2176.trc
-rw-r----- 1 ora9i dba 644 Dec 8 18:22 csmisc2_ora_21083.trc
ora9i@cs_dc02:/ora9i/app/oracle/admin/csmisc/udump > mailx -s "csmisc2_ora_22580.trc" zhangdp@aspire-tech.com < csmisc2_ora_22580.trc
ora9i@cs_dc02:/ora9i/app/oracle/admin/csmisc/udump > mailx -s "csmisc2_ora_20552.trc" zhangdp@aspire-tech.com < csmisc2_ora_20552.trc
ora9i@cs_dc02:/ora9i/app/oracle/admin/csmisc/udump > exit

SQL>

我們看到SID為30的會話，產生的跟蹤檔案（csmisc2_ora_22580.trc）為的主要內容是：/ora9i/app/oracle/admin/csmisc/udump/csmisc2_ora_22580.trc
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
ORACLE_HOME = /ora9i/app/oracle/product/920
System name: HP-UX
Node name: cs_dc02
Release: B.11.11
Version: U
Machine: 9000/800
Instance name: csmisc2Redo thread mounted by this instance: 2
Oracle process number: 28Unix process pid: 22580, image: oracle@cs_dc02 (TNS V1-V3)

*** 2005-01-10 11:31:49.416
*** SESSION ID:(30.24167) 2005-01-10 11:31:49.354
WAIT #0: nam='library cache lock' ela= 507258 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 505686 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507678 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507595 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507880 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507317 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507703 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507683 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 508265 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507100 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507684 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 505889 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507731 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507650 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507604 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301
WAIT #0: nam='library cache lock' ela= 507698 p1=-4611686013547141416 p2=-4611686013691716064 p3=1301

。。。 。。。

我們看到SID 30的跟蹤檔案中的等待事件就是在V$SESSION_WAIT中看到的'library cache lock' .

再看看SID為37的會話，產生的跟蹤檔案（csmisc2_ora_20552.trc）為的主要內容是：
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
ORACLE_HOME = /ora9i/app/oracle/product/920
System name: HP-UX
Node name: cs_dc02Release: B.11.11
Version: U
Machine: 9000/800
Instance name: csmisc2Redo thread mounted by this instance: 2
Oracle process number: 26
Unix process pid: 20552, image: oracle@cs_dc02 (TNS V1-V3)

*** 2005-01-10 11:33:22.702
*** SESSION ID:(37.2707) 2005-01-10 11:33:22.690
WAIT #1: nam='SQL*Net message to dblink' ela= 4 p1=675562835 p2=1 p3=0
*** 2005-01-10 11:35:07.452
WAIT #1: nam='SQL*Net message from dblink' ela= 102293555 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message to dblink' ela= 3 p1=675562835 p2=1 p3=0*** 2005-01-10 11:36:55.980
WAIT #1: nam='SQL*Net message from dblink' ela= 105969709 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message to dblink' ela= 4 p1=675562835 p2=1 p3=0
*** 2005-01-10 11:39:05.416
WAIT #1: nam='SQL*Net message from dblink' ela= 126390826 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message to dblink' ela= 4 p1=675562835 p2=1 p3=0
*** 2005-01-10 11:41:12.878
WAIT #1: nam='SQL*Net message from dblink' ela= 124461520 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message to dblink' ela= 4 p1=675562835 p2=1 p3=0
*** 2005-01-10 11:43:01.285
WAIT #1: nam='SQL*Net message from dblink' ela= 105859385 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message to dblink' ela= 4 p1=675562835 p2=1 p3=0
*** 2005-01-10 11:44:48.200
WAIT #1: nam='SQL*Net message from dblink' ela= 104397696 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message to dblink' ela= 4 p1=675562835 p2=1 p3=0

。。。 。。。

現在我們來dump 系統狀態(systemstate)，看看更詳細的資訊。

首先簡單的介紹一下 event systemstate。很多人把 systemstate 事件理解為dump發生的那一刻的系統內所有程式的資訊，這是個錯誤的概念，事實上，
轉儲 system state 產生的跟蹤檔案是從dump那一刻開始到dump任務完成之間一段事件內的系統內所有程式的資訊。

dump systemstate產生的跟蹤檔案包含了系統中所有程式的程式狀態等資訊。每個程式對應跟蹤檔案中的一段內容，反映該程式的狀態資訊，包括程式資訊，會話資訊，enqueues資訊（主要是lock的資訊），緩衝區的資訊和該程式在SGA區中持有的（held）物件的狀態等資訊。

那麼通常在什麼情況下使用systemstate比較合適呢？ Oracle推薦的使用systemstate事件的幾種情況是：

• 資料庫 hang 住了
• 資料庫很慢
• 程式正在hang
• 資料庫出現某些錯誤
• 資源爭用

dump systemstate的語法為： ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10';

也可以使用ORADEBUG實現這個功能
ORADEBUG DUMP SYSTEMSTATE level 10

如果希望在資料庫發生某種錯誤時除非systemstate事件，可以在引數檔案（spfile或者pfile）中設定event引數，
例如，當系統發生死鎖（出現ORA-00060錯誤）時dump systemstate：
event = "60 trace name systemstate level 10"

言歸正傳，我們dump系統狀態：SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 8';

Session altered.

SQL> host
ora9i@cs_dc02:/ora9i >cd /ora9i/app/oracle/admin/csmisc/udump
ora9i@cs_dc02:/ora9i/app/oracle/admin/csmisc/udump > ll -ctl
-rw-r----- 1 ora9i dba 1070863 Jan 10 13:02 csmisc2_ora_22580.trc
-rw-r----- 1 ora9i dba 1345368 Jan 10 13:01 csmisc2_ora_22568.trc-rwxrwxrwx 1 ora9i dba 44114 Jan 10 12:52 ass1015.awk
-rw-r----- 1 ora9i dba 407133 Jan 7 15:10 csmisc2_ora_2708.trc
-rw-r----- 1 ora9i dba 640 Jan 7 14:48 csmisc2_ora_835.trc
-rw-r----- 1 ora9i dba 1590 Dec 30 22:50 csmisc2_ora_16244.trc
-rw-r----- 1 ora9i dba 1308403 Dec 30 22:44 csmisc2_ora_16033.trc
-rw-r----- 1 ora9i dba 616 Dec 28 14:16 csmisc2_ora_2176.trc
-rw-r----- 1 ora9i dba 644 Dec 8 18:22 csmisc2_ora_21083.trc
ora9i@cs_dc02:/ora9i/app/oracle/admin/csmisc/udump >
ora9i@cs_dc02:/ora9i/app/oracle/admin/csmisc/udump > mailx -s "22568" zhangdp@aspire-tech.com < csmisc2_ora_22568.trc

這個跟蹤檔案很大（因為它包含了所有程式的資訊），那麼我們從哪裡開始看起呢？

首先，通過在跟蹤檔案中查詢字串"waiting for 'library cache lock'"，我們找到了被阻塞程式的資訊：

PROCESS 28: ----------------被阻塞的Oracle程式，這裡PROCESS 28對應了V$PROCESS中的PID的值，
也就是說我們可以根據這一資訊在V$PROCESS和V$SESSION找到被阻塞的會話的資訊 ----------------------------------------
SO: c000000109c83bf0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=28, calls cur/top: c00000010b277890/c00000010b277890, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 17 24 6
last post received-location: ksusig
last process to post me: c000000109c840f8 25 0
last post sent: 0 0 15
last post sent-location: ksasnd
last process posted by me: c000000109c7ff90 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: c000000109eefda0
O/S info: user: ora9i, term: pts/th, ospid: 22580 ----------------該程式的作業系統程式號，對應於V$PROCESS中的SPID
OSD pid info: Unix process pid: 22580, image: oracle@cs_dc02 (TNS V1-V3)
----------------------------------------
SO: c000000109f02c68, type: 4, owner: c000000109c83bf0, flag: INIT/-/-/0x00
(session) trans: 0000000000000000, creator: c000000109c83bf0, flag: (100041) USR/- BSY/-/-/-/-/-
DID: 0002-001C-00000192, short-term DID: 0000-0000-00000000
txn branch: 0000000000000000
oct: 0, prv: 0, sql: c00000011f8ea068, psql: c00000011f8ea068, user: 50/PUBUSER
O/S info: user: ora9i, term: , ospid: 22536, machine: cs_dc02
program: sqlplus@cs_dc02 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024 waiting for 'library cache lock' blocking sess=0x0 seq=18589 wait_time=0
handle address=c000000122e2a6d8, lock address=c00000011a449e20, 100*mode+namespace=515

。。。 。。。

SO: c00000010b277890, type: 3, owner: c000000109c83bf0, flag: INIT/-/-/0x00
(call) sess: cur c000000109f02c68, rec 0, usr c000000109f02c68; depth: 0
----------------------------------------
SO: c00000011a449e20, type: 51, owner: c00000010b277890, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c00000011a449e20 handle=c000000122e2a6d8 request=S
call pin=0000000000000000 session pin=0000000000000000
htl=c00000011a449e90[c00000011a4bc350,c00000011a4bc350] htb=c00000011a4bc350
user=c000000109f02c68 session=c000000109f02c68 count=0 flags=[00] savepoint=463
the rest of the object was already dumped

。。。 。。。

請注意下面的資訊： waiting for 'library cache lock' blocking sess=0x0 seq=18589 wait_time=0
handle address=c000000122e2a6d8, lock address=c00000011a449e20, 100*mode+namespace=515

這段資訊告訴我們ORACLE PID為 28的程式(PROCESS 28)，正在等待'library cache lock' ，通過‘handle address=c000000122e2a6d8’我們可以找到阻塞它的會話的ORACLE PID資訊。

還要注意這段資訊： LIBRARY OBJECT LOCK: lock=c00000011a449e20 handle=c000000122e2a6d8 request=S
call pin=0000000000000000 session pin=0000000000000000
htl=c00000011a449e90[c00000011a4bc350,c00000011a4bc350] htb=c00000011a4bc350
user=c000000109f02c68 session=c000000109f02c68 count=0 flags=[00] savepoint=463

這裡就是阻塞PROCESS 28程式的會話的資訊。

簡單的記住這個依據的要點是：

waiting session的'handle address'的值對應於blocking session的'handle'的值。

回過頭來，看看這個值，它應於上面我們在V$SESSION_WAIT中看到的P1和P2的值：
SQL> select to_number('C000000122E2A6D8','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;

TO_NUMBER('C000000122E2A6D8','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
----------------------------------------------------------------
1.3835E+19

SQL>

問題的成因已經基本上明確了，這裡推薦兩種解決問題的方法：
方法1，根據 c000000122e2a6d8 地址，我們可以得到當前在library cache中相應的鎖資訊： SQL> l
1 select INST_ID,USER_NAME,KGLNAOBJ,KGLLKSNM,KGLLKUSE,KGLLKSES,KGLLKMOD,KGLLKREQ,KGLLKPNS,KGLLKHDL
2* from X$KGLLK where KGLLKHDL = 'C000000122E2A6D8' order by KGLLKSNM,KGLNAOBJ
SQL> /

INST_ID USER_NAME KGLNAOBJ KGLLKSNM KGLLKUSE KGLLKSES KGLLKMOD KGLLKREQ KGLLKPNS KGLLKHDL
---------- ------------- ---------------------- ---------- ---------------- ---------------- ---------- ---------- ---------------- ----------------
2 PUBUSER CSNOZ629926699966 30 C000000109F02C68 C000000109F02C68 0 2 00 C000000122E2A6D8
2 PUBUSER CSNOZ629926699966 37 C000000108C99E28 C000000108C99E28 3 0 00 C000000122E2A6D8

SQL>

按照Oracle推薦的做法，我們現在應該使用'alter system kill session'命令kill掉SID 37，結果得到了ORA-00031錯誤：
SQL> alter system kill session '37,2707';

alter system kill session '37,2707'
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL>

檢查SID 37的狀態：
SQL> set linesize 150
SQL> col program for a50
SQL> select sid,serial#,status,username,program from v$session where sid=37;

SID SERIAL# STATUS USERNAME PROGRAM
---------- ---------- -------- ------------------------------ --------------------------------------------------
37 2707 KILLED PUBUSER sqlplus@cs_dc02 (TNS V1-V3)

SQL>
再次證實了我們最初的想法—— 有人在執行了某個需要執行很久的DDL（多數是語句效率低，當然不排除遭遇bug的可能），
然後沒等語句結束就異常退出了會話。

這個例子中我們在上面的跟蹤檔案已經找到了該會話對應的作業系統程式(SPID)，如果在其他情況下，我們如何找到這種狀態為'KILLED'
的作業系統程式號（SPID）呢？
下面給出了一個方法，可以借鑑：
SQL> l
1 SELECT s.username,s.status,
2 x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
3 decode(bitand (x.ksuprflg,2),0,null,1)
4 FROM x$ksupr x,v$session s
5 WHERE s.paddr(+)=x.addr
6 and bitand(ksspaflg,1)!=0
7* and s.sid=37
SQL> /

USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D
------------------------------ -------- ---------------- ---------- ---------- ------------ ---------- -- -
PUBUSER KILLED C000000109C831E0 41 15 16243 17

SQL>

x$ksupr.ADDR列的值對應了V$PROCESS 中的ADDR的值，知道了這個SPID的地址，找到這個作業系統程式(SPID)就簡單了，例如：
SQL> select spid,pid from v$process where addr='C000000109C831E0';

SPID PID
------------ ----------
20552 26

SQL>

現在，我們只需要在作業系統上 kill 作業系統程式20552就可以了：
ora9i@cs_dc02:/ora9i > ps -ef | grep 20552
ora9i 20552 1 0 Jan 8 ? 0:01 oraclecsmisc2 (LOCAL=NO)
ora9i 14742 14740 0 17:19:02 pts/ti 0:00 grep 20552
ora9i@cs_dc02:/ora9i > kill -9 20552
ora9i@cs_dc02:/ora9i > ps -ef | grep 20552
ora9i 14966 14964 0 17:40:01 pts/ti 0:00 grep 20552
ora9i@cs_dc02:/ora9i >

再來檢查一下SID 37的資訊，我們看到這個會話是真的被kill掉了，
ora9i@cs_dc02:/ora9i > exit

SQL> select sid,serial#,status,username,program from v$session where sid=37;

no rows selected

SQL> l
1 SELECT s.username,s.status,
2 x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
3 decode(bitand (x.ksuprflg,2),0,null,1)
4 FROM x$ksupr x,v$session s
5 WHERE s.paddr(+)=x.addr
6 and bitand(ksspaflg,1)!=0
7* and s.sid=37
SQL> /

no rows selected

SQL>

回到剛才hang住的會話，它已經恢復了正常操作，
並且我們已經得到了'ORA-04043: object CSNOZ629926699966 does not exist'這個正常的資訊：
SQL> desc CSNOZ629926699966

ERROR:
ORA-04043: object CSNOZ629926699966 does not exist

SQL>

在開一個會話，測試一把:
ora9i@cs_dc02:/ora9i > sqlplus pubuser/pubuser

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jan 10 17:42:16 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> set timing on
SQL> desc CSNOZ629926699966
ERROR:
ORA-04043: object CSNOZ629926699966 does not exist

SQL>
當發出命令'desc CSNOZ629926699966'的時候，我們看到系統立刻返回了ORA-04043: object CSNOZ629926699966 does not exist'資訊，問題就此解決了。

這裡，簡單的介紹一下X$KGLLK，這個基表儲存了庫快取中物件的鎖的資訊，它對於解決這類問題特別有用，其名稱的含義如下：
[K]ernel Layer
[G]eneric Layer
[L]ibrary Cache Manager ( defined and mapped from kqlf )
Object Locks
X$KGLLK - Object [L]oc[K]s

KGLNAOBJ 列包含了在librarky cache中的物件上執行命令的語句的前80個字元（其實從這裡我們也可以大大縮小範圍了）
X$KGLLK.KGLLKUSE 和 x$kgllk.KGLLKSES 對應於跟蹤檔案中的owner的值
X$KGLLK.KGLLKADR
X$KGLLK.KGLLKHDL 對應於跟蹤檔案中的handle的值(handle=C000000122E2A6D8)，也就是'library cache lock'的地址
X$KGLLK.KGLLKPNS 對應於跟蹤檔案中的session pin的值
X$KGLLK.KGLLKSPN對應於跟蹤檔案中的savepoint的值

我們再來看一下更全面的資訊：
SQL> set linesize 2000
SQL> select * from X$KGLLK where KGLLKHDL = 'C000000122E2A6D8' order by KGLLKSNM,KGLNAOBJ
2 /

ADDR INDX INST_ID KGLLKADR KGLLKUSE KGLLKSES KGLLKSNM KGLLKHDL KGLLKPNC KGLLKPNS KGLLKCNT KGLLKMOD KGLLKREQ KGLLKFLG KGLLKSPN KGLLKHTB KGLNAHSH KGLHDPAR KGLHDNSP USER_NAME KGLNAOBJ
---------------- ---------- ---------- ---------------- ---------------- ---------------- ---------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------------- ---------- ------------------------------ ------------------------------------------------------------
800003FB0007E4D0 33 2 C00000011A449E20 C000000109F02C68 C000000109F02C68 30 C000000122E2A6D8 00 00 0 0 2 0 463 C00000011A4BC350 3990848181 C000000122E2A6D8 1 PUBUSER CSNOZ629926699966
800003FB0007E5B0 34 2 C00000011A44A150 C000000108C99E28 C000000108C99E28 37 C000000122E2A6D8 00 00 1 3 0 0 179 C00000011A4BB328 3990848181 C000000122E2A6D8 1 PUBUSER CSNOZ629926699966

SQL> set linesize 100
SQL> l
1* select * from X$KGLLK where KGLLKHDL = 'C000000122E2A6D8' order by KGLLKSNM,KGLNAOBJ
SQL> /

ADDR INDX INST_ID KGLLKADR KGLLKUSE KGLLKSES KGLLKSNM
---------------- ---------- ---------- ---------------- ---------------- ---------------- ----------
KGLLKHDL KGLLKPNC KGLLKPNS KGLLKCNT KGLLKMOD KGLLKREQ KGLLKFLG
---------------- ---------------- ---------------- ---------- ---------- ---------- ----------
KGLLKSPN KGLLKHTB KGLNAHSH KGLHDPAR KGLHDNSP USER_NAME
---------- ---------------- ---------- ---------------- ---------- ------------------------------
KGLNAOBJ
------------------------------------------------------------
800003FB0007E4D0 33 2 C00000011A449E20 C000000109F02C68 C000000109F02C68 30
C000000122E2A6D8 00 00 0 0 2 0
463 C00000011A4BC350 3990848181 C000000122E2A6D8 1 PUBUSER
CSNOZ629926699966

800003FB0007E5B0 34 2 C00000011A44A150 C000000108C99E28 C000000108C99E28 37
C000000122E2A6D8 00 00 1 3 0 0
179 C00000011A4BB328 3990848181 C000000122E2A6D8 1 PUBUSER
CSNOZ629926699966

SQL>

上一個例子中我們主要藉助於X$KGLLK基表和event systemstate解決問題，那麼如果你不瞭解X$KGLLK基表，或者忘記了如何使用它，那也不要緊張，這裡再介紹一種常規的方法。

從system state 的轉儲資訊中，我們已經注意到PROCESS 28當前正在等待'library cache lock'。
'handle address'表示的就是正持有 PROCESS 28 程式所等待的library cache中的地址。

現在我們繼續在跟蹤檔案中查詢包含 'handle=c000000122e2a6d8' 字串的ORACLE PROCESS，也就是查詢blocking session的資訊，發現資訊如下：

PROCESS 26: ----------------阻塞其他會話的Oracle程式，這裡PROCESS 26對應了V$PROCESS中的PID的值 ----------------------------------------
SO: c000000109c831e0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=26, calls cur/top: c00000010b2774d0/c00000010b2774d0, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 17 24 6
last post received-location: ksusig
last process to post me: c000000109c840f8 25 0
last post sent: 751404 0 15
last post sent-location: ksasnd
last process posted by me: c000000109c836e8 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: c000000109eefda0
O/S info: user: ora9i, term: UNKNOWN, ospid: 20552 OSD pid info: Unix process pid: 20552, image: oracle@cs_dc02 (TNS V1-V3)
----------------------------------------
SO: c0000001180b9510, type: 8, owner: c000000109c831e0, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=162e1b48 incno=0 pending i/o cnt=0
----------------------------------------
SO: c0000001180b9458, type: 8, owner: c000000109c831e0, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=162deb18 incno=0 pending i/o cnt=0
----------------------------------------
SO: c0000001180b8230, type: 8, owner: c000000109c831e0, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=162de848 incno=0 pending i/o cnt=0
----------------------------------------
SO: c0000001180b7b00, type: 8, owner: c000000109c831e0, flag: INIT/-/-/0x00
(FOB) flags=2 fib ptr=162de578 incno=0 pending i/o cnt=0
----------------------------------------
SO: c000000108c99e28, type: 4, owner: c000000109c831e0, flag: INIT/-/-/0x00
c000000108c99e28 對應的就是V$SESSION 中的SADDR的值，通過這個資訊就可以找到blocking session的SID等資訊
(session) trans: c0000001169403c0, creator: c000000109c831e0, flag: (100041) USR/- BSY/-/-/-/-/-
DID: 0002-001A-0000007D, short-term DID: 0000-0000-00000000
txn branch: c00000011b825e18
oct: 0, prv: 0, sql: 800003fb0005f7b0, psql: c00000011fbe3f98, user: 50/PUBUSER
O/S info: user: report16, term: , ospid: 20550, machine: cs_dc02 program: sqlplus@cs_dc02 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'SQL*Net message from dblink' blocking sess=0x0 seq=3319 wait_time=0
driver id=28444553, #bytes=1, =0 -------------------
這裡，

#bytes 表示個server process通過database link傳送給另一個server process的位元組數（bytes）driver id 是一個10進位制數，我們需要把它轉化為16進位制數，然後就會發現它對應於我們通過event 10046中的相應的資訊：*** 2005-01-10 11:44:48.200
WAIT #1: nam='SQL*Net message from dblink' ela= 104397696 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message to dblink' ela= 4 p1=675562835 p2=1 p3=0

SQL> select to_char(675562835,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;

TO_CHAR(675562835,'XXXXXXXXXXXXXX
---------------------------------
28444553

SQL>

temporary object counter: 0
----------------------------------------
SO: c00000011a4496b0, type: 51, owner: c000000108c99e28, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c00000011a4496b0 handle=c00000012029f968 mode=N
call pin=0000000000000000 session pin=c00000011a44ad70
htl=c00000011a449720[c00000011a4baa78,c00000011a4baa78] htb=c00000011a4baa78
user=c000000108c99e28 session=c000000108c99e28 count=1 flags=[00] savepoint=173
LIBRARY OBJECT HANDLE: handle=c00000012029f968
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=3
lwt=c00000012029f998[c00000012029f998,c00000012029f998] ltm=c00000012029f9a8[c00000012029f9a8,c00000012029f9a8]
pwt=c00000012029f9c8[c00000012029f9c8,c00000012029f9c8] ptm=c00000012029fa58[c00000012029fa58,c00000012029fa58]
ref=c00000012029f978[c0000001202a0068, c0000001202a0068] lnd=c00000012029fa70[c00000012029fa70,c00000012029fa70]
LIBRARY OBJECT: object=c00000012029f5c8
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=1 size=16
AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
ACCESSES: count=1 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 c00000012029f8a8 c00000012029f288 I/P/A 0 NONE
6 c00000012029f6e8 c00000012029e7c8 I/-/A 0 NONE
----------------------------------------

。。。 。。。

----------------------------------------
SO: c00000011a44a150, type: 51, owner: c0000001169403c0, flag: INIT/-/-/0x00
////////////// X$KGLLK.KGLLKADR 對應於SO(SO: c00000011a44a150 //////////////
////////////// X$KGLLK.KGLLKUSE 和 x$kgllk.KGLLKSES 對應於owner的值(owner: c0000001169403c0)
LIBRARY OBJECT LOCK: lock=c00000011a44a150 handle=c000000122e2a6d8 mode=X
////////////// X$KGLLK.KGLLKADR 對應於SO 和 lock的值(SO: c00000011a44a150,lock=c00000011a44a150) //////////////
////////////// X$KGLLK.KGLLKHDL 對應於handle的值(handle=c000000122e2a6d8) ////////////////
call pin=0000000000000000 session pin=0000000000000000
////////////// X$KGLLK.KGLLKPNS 對應於session pin的值(session pin=0000000000000000) //////////////
htl=c00000011a44a1c0[c00000011a4bb328,c00000011a4bb328] htb=c00000011a4bb328
user=c000000108c99e28 session=c000000108c99e28 count=1 flags=[00] savepoint=179
user和session的值分別對應著x$kgllk.KGLLKUSE 和 x$kgllk.KGLLKSES，也對應於V$SESSION中阻塞其他會話的SADDR ////////////// X$KGLLK.KGLLKSPN對應於savepoint的值(savepoint=179) //////////////
LIBRARY OBJECT HANDLE: handle=c000000122e2a6d8
name=PUBUSER.CSNOZ629926699966
hash=eddf82b5 timestamp=01-08-2005 13:00:18 previous=NULL
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/PTM/SML/[02000000]
kkkk-dddd-llll=0000-0709-0001 lock=X pin=X latch#=3
lwt=c000000122e2a708[c00000011a449e40,c00000011a449e40] ltm=c000000122e2a718[c000000122e2a718,c000000122e2a718]
pwt=c000000122e2a738[c000000122e2a738,c000000122e2a738] ptm=c000000122e2a7c8[c000000122e2a7c8,c000000122e2a7c8]
ref=c000000122e2a6e8[c000000122e2a6e8, c000000122e2a6e8] lnd=c000000122e2a7e0[c000000122e2a7e0,c000000122e2a7e0]
LOCK INSTANCE LOCK: id=LBcafc8485d0949f81
PIN INSTANCE LOCK: id=NBcafc8485d0949f81 mode=X release=F flags=[00]
LIBRARY OBJECT: object=c000000122e12f70
type=TABL flags=EXS/LOC/CRT[0015] pflags= [00] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 c000000122e2a618 c000000122e13118 I/P/A 0 INSERT
3 c000000122e13178 0 -/P/- 1 NONE
8 c000000122e12c30 c000000122febdb8 I/P/A 1 UPDATE
9 c000000122e13090 0 -/P/- 1 NONE
10 c000000122e12ce0 c000000122acbc70 I/P/A 1 UPDATE
----------------------------------------

。。。 。。。

根據上述兩個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>

前面我們已經知道碰到'library cache lock'導致程式hang時，如何解決。

那麼如何避免這類問題的發生呢？這篇文章主要介紹了library cache lock的成因以及如何避免。

當我們在執行一些DML，DDL，甚至desc tablename等等操作的時候，會話就hang住了，
還有一種情況，當我們使用create or replace procedure/function等語句修改Procedure和Function的時候，會話也會hang住，這是為什麼呢？

當出現上述情況的時候，我們可以通過v$session_wait查詢等待事件，當然，這種情況下，該session的等待事件一定是'Library cache lock'。

讓我們來解釋一下，

第一種情況，當會話1（session 1）在對一個表執行DML 或者 DDL，與此同時還有另一個會話，我們姑且稱之為會話2（session 2），這個會話2也在對這個表執行DDL（如ALTER TABLE），當會話2的完成需要很長時間時（依操作的具體的資料量而定），會話1就會hang住，這時，你查詢會話1的等待事件就是'Library cache lock'。

第二種情況，當會話1（session 1）在修改一個package，與此同時還有另一個會話，我們姑且稱之為會話2（session 2），這個會話2正在執行會話1所修改的package中的Procedure或者Function，會話1就會hang住，這時，你查詢會話1的等待事件就是'Library cache lock'。

因此，在對Package/Procedure/Function/View進行編譯和分析的時候，我們必須確定此時沒有人正在編譯和分析相同的物件，即確保沒有人也在此時改變這些需要重定義（drop和recreate）的物件的定義。

SQL> create or replace procedure who_is_using wrapped
2 0
3 abcd
4 abcd
5 abcd
6 abcd
7 abcd
8 abcd
9 abcd
10 abcd
11 abcd
12 abcd
13 abcd
14 abcd
15 abcd
16 abcd
17 abcd
18 abcd
19 7
20 200f000
21 1
22 4
23 0
24 1e
25 c WHO_IS_USING:
26 8 OBJ_NAME:
27 8 VARCHAR2:
28 b DBMS_OUTPUT:
29 6 ENABLE:
30 7 1000000:
31 1 I:
32 1 B:
33 8 USERNAME:
34 3 SID:
35 3 SYS:
36 7 X$KGLPN:
37 1 A:
38 9 V$SESSION:
39 7 X$KGLOB:
40 1 C:
41 8 KGLPNUSE:
42 1 =:
43 5 SADDR:
44 5 UPPER:
45 8 KGLNAOBJ:
46 4 LIKE:
47 8 KGLPNHDL:
48 8 KGLHDADR:
49 4 LOOP:
50 8 PUT_LINE:
51 1 (:
52 2 ||:
53 7 TO_CHAR:
54 4 ) - :
55 0
56
57 0
58 0
59 74
60 2
61 0 1d 9a 8f a0 b0 3d b4
62 55 6a :2 a0 6b 51 a5 57 91
63 :2 a0 6b :2 a0 6b ac :2 a0 6b a0
64 b9 :2 a0 b9 :2 a0 6b a0 b9 b2
65 ee :2 a0 6b a0 7e a0 6b b4
66 2e :3 a0 6b a5 b 7e :2 a0 a5
67 b b4 2e a 10 :2 a0 6b a0
68 7e a0 6b b4 2e a 10 ac
69 d0 e5 e9 37 :3 a0 6b 6e 7e
70 :3 a0 6b a5 b b4 2e 7e 6e
71 b4 2e 7e :2 a0 6b b4 2e a5
72 57 b7 a0 47 b7 a4 b1 11
73 68 4f 17 b5
74 74
75 2
76 0 3 4 19 15 14 20 13
77 25 29 2d 31 35 38 3b 3c
78 41 45 49 4d 50 54 58 5b
79 5c 60 64 67 11 6b 6f 73
80 75 79 7d 80 84 86 87 8e
81 92 96 99 9d a0 a4 a7 a8
82 ad b1 b5 b9 bc bd bf c2
83 c6 ca cb cd ce 1 d3 d8
84 dc e0 e3 e7 ea ee f1 f2
85 1 f7 fc fd 101 106 10b 10d
86 111 115 119 11c 120 123 127 12b
87 12f 132 133 135 136 13b 13e 142
88 143 148 14b 14f 153 156 157 15c
89 15d 162 164 168 16f 171 175 177
90 182 186 188 18f
91 74
92 2
93 0 1 b 18 21 :2 18 17 :2 1
94 2 :2 e 15 :2 2 6 1c :2 1e 27
95 :2 29 1c 11 15 11 1d 11 1f
96 29 1f 2b 2f 2b 37 2b c
97 11 12 :2 14 1f 1d :2 21 :2 1d 12
98 18 :2 1a :3 12 2a 30 :2 2a :5 12 :2 14
99 1f 1d :2 21 :2 1d :2 12 :4 c 2 2b
100 3 :2 f 18 1b 1d 25 :2 27 :2 1d
101 :2 18 2b 2d :2 18 33 35 :2 37 :2 18
102 :2 3 2b 6 2 :8 1
103 74
104 2
105 0 :9 1 :6 3 :8 4 :f 5 :9 6 :d 7 :2 6
106 :9 8 :2 6 5 :4 4 8 :19 9 8 a
107 4 :2 2 :6 1
108 191
109 2
110 :4 0 1 :a 0 70 1 1a 1b 5
111 :2 3 :3 0 2 :6 0 5 4 :3 0 7
112 :2 0 70 2 8 :2 0 4 :3 0 5
113 :3 0 a b 0 6 :2 0 7 c
114 e :2 0 6c 7 :3 0 8 :3 0 9
115 :3 0 11 12 0 8 :3 0 a :3 0
116 14 15 0 9 b :3 0 c :3 0
117 18 19 0 d :3 0 e :3 0 8
118 :3 0 1d 1e b :3 0 f :3 0 20
119 21 0 10 :3 0 22 23 c 25
120 49 0 4a :3 0 d :3 0 11 :3 0
121 27 28 0 8 :3 0 12 :2 0 13
122 :3 0 2a 2c 0 12 2b 2e :3 0
123 14 :3 0 10 :3 0 15 :3 0 31 32
124 0 10 30 34 16 :2 0 14 :3 0
125 2 :3 0 15 37 39 17 36 3b
126 :3 0 2f 3d 3c :2 0 d :3 0 17
127 :3 0 3f 40 0 10 :3 0 12 :2 0
128 18 :3 0 42 44 0 1c 43 46
129 :3 0 3e 48 47 :3 0 2 17 26
130 0 4b :5 0 4c :2 0 4e 10 4d
131 19 :3 0 4 :3 0 1a :3 0 50 51
132 0 1b :3 0 1c :2 0 1d :3 0 7
133 :3 0 a :3 0 56 57 0 1a 55
134 59 1f 54 5b :3 0 1c :2 0 1e
135 :3 0 22 5d 5f :3 0 1c :2 0 7
136 :3 0 9 :3 0 62 63 0 25 61
137 65 :3 0 28 52 67 :2 0 69 2a
138 6b 19 :3 0 4e 69 :4 0 6c 2c
139 6f :3 0 6f 0 6f 6e 6c 6d
140 :6 0 70 0 2 8 6f 72 :2 0
141 1 70 73 :6 0
142 2f
143 2
144 :3 0 1 3 1 6 1 d 2
145 13 16 3 1c 1f 24 1 33
146 2 29 2d 1 38 2 35 3a
147 1 58 2 41 45 2 53 5a
148 2 5c 5e 2 60 64 1 66
149 1 68 2 f 6b
150 1
151 4
152 0
153 72
154 0
155 1
156 14
157 2
158 3
159 0 1 0 0 0 0 0 0
160 0 0 0 0 0 0 0 0
161 0 0 0 0
162 2 0 1
163 3 1 0
164 10 2 0
165 0
166 /

Procedure created.

Elapsed: 00:00:00.07
SQL>