模板
教程
環境
性能
功能
管理
library cache lock 的解決案例
發表于：2007-07-02來源：作者：點擊數：
標簽：
下午，業務人員報告，執行任何和zzss03201281cs_no表有關的操作都會hang住，包括desc zzss03201281cs_no，也會hang在那里 第一感覺是鎖了，于是，我看看鎖 SQL select * from v$lock where block=1; no rows selected SQL SQL select * from gv$lock where b
下午，業務人員報告，執行任何和zzss03201281cs_no表有關的操作都會hang住，包括desc zzss03201281cs_no，也會hang在那里
第一感覺是鎖了，于是，我看看鎖
SQL> select * from v$lock where block=1;
no rows selected
SQL>
SQL> select * from gv$lock where block=1;
no rows selected
SQL>
再看看等待事件：
SQL> col event for a30
SQL> l
1* select event,p1,p2,sid from v$session_wait where event=@#library cache lock@#
SQL> /
EVENT P1 P2 SID
------------------------------ ---------- ---------- ----------
library cache lock 1.3835E+19 1.3835E+19 32
SQL> /
EVENT P1 P2 SID
------------------------------ ---------- ---------- ----------
library cache lock 1.3835E+19 1.3835E+19 32
SQL> /
EVENT P1 P2 SID
------------------------------ ---------- ---------- ----------
library cache lock 1.3835E+19 1.3835E+19 32
。。。
奇怪，怎么這么多 library cache lock ？
SQL> show user
USER is "SYS"
SQL> exec dbms_system.set_ev(32,27506,10046,12,@#@#);
PL/SQL procedure suclearcase/" target="_blank" >ccessfully completed.
Elapsed: 00:00:00.10
SQL> l
1 SELECT d.VALUE
2 || @#/@#
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || @#_ora_@#
5 || p.spid
6 || @#.trc@# trace_file_name
7 FROM (SELECT p.spid
8 FROM v$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10 (SELECT t.INSTANCE
11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = @#thread@#
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECT VALUE
15 FROM v$parameter
16* WHERE NAME = @#user_dump_dest@#) d
SQL> /
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/ora9i/app/oracle/admin/csmisc/udump/csmisc2_ora_2708.trc
Elapsed: 00:00:00.10
SQL>
SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object;
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
14 35202 31 3
15 18 30 3
SQL> col object_name format a30
SQL> select owner,object_name,status from dba_objects where object_id=35202;
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
SYS
PLAN_TABLE
VALID
SQL>
這個對象顯然不是我們關注的。
SQL> l
/ 1* select owner,object_name,status from dba_objects where object_id=18
SQL>
OWNER OBJECT_NAME STATUS
------------------------------ ------------------------------ -------
SYS OBJ$ VALID
就是這個對象搞得，估計是開發人員異常退出一些進程
SQL> c/18/30
1* select serial#,username,command,lockwait,status,schemaname,osuser,machine,terminal,program,module from v$session where sid=30
SQL> /
SERIAL# USERNAME COMMAND LOCKWAIT STATUS
---------- ------------------------------ ---------- ---------------- --------
SCHEMANAME OSUSER
------------------------------ ------------------------------
MACHINE
----------------------------------------------------------------
TERMINAL PROGRAM
------------------------------ ------------------------------------------------
MODULE
------------------------------------------------
17921 PUBUSER 0 ACTIVE
PUBUSER report16
cs_dc02
SERIAL# USERNAME COMMAND LOCKWAIT STATUS
---------- ------------------------------ ---------- ---------------- --------
SCHEMANAME OSUSER
------------------------------ ------------------------------
MACHINE
----------------------------------------------------------------
TERMINAL PROGRAM
------------------------------ ------------------------------------------------
MODULE
------------------------------------------------
sqlplus@cs_dc02 (TNS V1-V3)
SQL*Plus
SQL> select b.username username, b.terminal terminal,b.program program,b.spid
2 from v$session a, v$process b
where a.PADDR=b.ADDR and a.sid =@#&sid@#;
3 Enter value for sid: 30
old 3: where a.PADDR=b.ADDR and a.sid =@#&sid@#
new 3: where a.PADDR=b.ADDR and a.sid =@#30@#
USERNAME TERMINAL
--------------- ------------------------------
PROGRAM SPID
------------------------------------------------ ------------
ora9i UNKNOWN
oracle@cs_dc02 (TNS V1-V3) 835
很顯然，是由于report16用戶執行了某些DDL操作，然后，異常退出，造成系統的鎖（估計和bug有關，有待考證）
SQL> host
ora9i@cs_dc02:/ora9i/app/oracle/product/920/rdbms/admin > ps -ef | grep 835
ora9i 4619 4617 1 14:48:18 pts/te 0:00 grep 835
ora9i 835 1 0 Jan 5 ? 0:01 oraclecsmisc2 (LOCAL=NO)
ora9i@cs_dc02:/ora9i/app/oracle/product/920/rdbms/admin > kill 835
ora9i@cs_dc02:/ora9i/app/oracle/product/920/rdbms/admin > exit
SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object;
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
14 35202 31 3
SQL>
kill掉這個進程后，問題解決了。（遺憾的是，忘了看看這個家伙執行的sql了，呵呵）
SQL> desc zzss03201281cs_no
ERROR:
ORA-04043: object zzss03201281cs_no does not exist
SQL> desc zzss03201281cs_no
ERROR:
ORA-04043: object zzss03201281cs_no does not exist
SQL>
SQL> exec dbms_system.set_ev(32,27506,0,0,@#@#);
PL/SQL procedure successfully completed.
SQL>
查看trace文件，：
果然大量的wait：
WAIT #1: nam=@#library cache lock@# ela= 316 p1=-4611686013647472824 p2=-4611686013691747544 p3=1301
WAIT #1: nam=@#library cache lock@# ela= 326 p1=-4611686013647472824 p2=-4611686013691747544 p3=1301
WAIT #1: nam=@#library cache lock@# ela= 398 p1=-4611686013647483736 p2=-4611686013691747816 p3=1301
WAIT #1: nam=@#library cache lock@# ela= 552 p1=-4611686013647483736 p2=-4611686013691747816 p3=1301
WAIT #1: nam=@#library cache lock@# ela= 330 p1=-4611686013649700264 p2=-4611686013691715248 p3=1301
WAIT #1: nam=@#library cache lock@# ela= 141 p1=-4611686013649700264 p2=-4611686013691715248 p3=1301
WAIT #1: nam=@#library cache lock@# ela= 223 p1=-4611686013647485472 p2=-4611686013691762016 p3=1301
WAIT #1: nam=@#library cache lock@# ela= 93 p1=-4611686013647485472 p2=-4611686013691762016 p3=1301
WAIT #1: nam=@#library cache lock@# ela= 223 p1=-4611686013595934816 p2=-4611686013642107320 p3=1301
