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

現在，我們結束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: 28Unix 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

。。。 。。。

[@more@]