問題描述：接到應用人員的報告，說是在任何對表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>
跟蹤期間咱們再次測試一下，看看有沒有其他線索。
新開一個程序，找出其sid, serial和spid等資訊：> 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檔案：> cd /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
> mailx -s "csmisc2_ora_22580.trc" < csmisc2_ora_22580.trc
> mailx -s "csmisc2_ora_20552.trc" < csmisc2_ora_20552.trc
> 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: 28
Unix process pid: 22580, image: (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: (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
>cd /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
>
> mailx -s "22568" < 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中的SPIDOSD pid info: Unix process pid: 22580, image: (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:(TNS V1-V3)
application name: SQL*Plus, hashvalue=3669949024waiting for 'library cache lock' blocking sess=0x0seq=18589 wait_time=0handle 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 C000000109F02C680 2 00 C000000122E2A6D8
2 PUBUSER CSNOZ629926699966 37 C000000108C99E28 C000000108C99E283 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 (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就可以了：> 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
> kill -9 20552
> ps -ef | grep 20552
ora9i 14966 14964 0 17:40:01 pts/ti 0:00 grep 20552
>
再來檢查一下SID 37的資訊，我們看到這個會話是真的被kill掉了，> 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>
在開一個會話，測試一把:
> 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
上一個例子中我們主要借助于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: 20552OSD pid info: Unix process pid: 20552, image:(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/-/-/0x00c000000108c99e28對應的就是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/PUBUSERO/S info: user: report16, term: , ospid: 20550, machine: cs_dc02program:(TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'SQL*Net message from dblink' blocking sess=0x0 seq=3319 wait_time=0driver 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=675562835p2=1 p3=0
WAIT #1: nam='SQL*Net message to dblink' ela= 4 p1=675562835p2=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=c00000011a4bb328user=c000000108c99e28 session=c000000108c99e28 count=1 flags=[00] savepoint=179user和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 1804782
C00000011676DAE0 C00000011676DB08 37 TM 18 0 3 0 1804782
C00000010B30C4E8 C00000010B30C508 37 XR 4 0 2 0 1803692
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>