library cache lock 的解决案例
发布时间：2018-08-22 13:41:17 所属栏目：电子商务 来源：站长网
导读：下午，业务人员报告，执行任何和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
|
下午，业务人员报告，执行任何和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 successfully 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 （编辑：荆州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
