library_cache_pin等待处理方法.doc

library cache pin等待处理方法 数据库由于应用维护人员需要每天定期去disable与enable触发器GZDLGIS.TRIG_B$GG_PD_DYDX_N_TREE,而当应用程序提交工单时(做dml操作)会使用到这个触发器;当触发器正在被其他进程(session)调用的时候刚好有其他进程进行去disable或者enable或者重新编译维护工作,就会产生数据库共享池锁,出现library cache pin与cursor: pin S wait on X 等待事件, 导致触发器disable,eanble,重编译等操作都无法完成,而dml操作也无法完成,即应用程序的工单无法提交. 这两个等待事件经常出现在重新编译function,procedure,package,trigger的时候,刚好正在编译的对象被其他进程调用; 以下是遇到library cache pin等待事件的处理方法 --查询等待事件 在数据库主机1上面查询 export ORACLE_SID=gisdb1 sqlplus / as sysdba set line 200 pagesize 9999 Select inst_id,event,count(*) from gv$session where status=ACTIVE and wait_class# 6 group by inst_id,event order by 1,3; INST_ID EVENT COUNT(*) 1 gc cr request 1 1 enq: TX - row lock contention 1 1 library cache pin 1 1 cursor: pin S wait on X 2 2 PX Deq: reap credit 1 可以发现,在实例1(INST_ID为1,即实例gisdb1)上存在library cache pin与cursor: pin S wait on X等待事件,说明存在上文描述的情况 --查询堵塞进程(holding_session)信息 select Distinct /*+ ordered */ w1.sid waiting_session, h1.sid holding_session, w.kgllktype lock_or_pin, od.to_owner object_owner, od.to_name object_name, oc.Type, decode(h.kgllkmod, 0, None, 1, Null, 2, Share, 3, Exclusive, Unknown) mode_held, decode(w.kgllkreq, 0, None, 1, Null, 2, Share, 3, Exclusive, Unknown) mode_requested, xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and (((w.kgllkmod = 0) or (w.kgllkmod= 1)) and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) and w.kgllktype = h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse =