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 COUNT(*)
EVENT
---------- ---------------------------------------------------------------- ----------
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 xw,x$kgllk xh
h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk
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 = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name=oc.Name
And od.to_owner=oc.owner
And w1.sid=xw.KGLLKSNM
And h1.sid=xh.KGLLKSNM
And (w1.SQL_ADDRESS=xw.KGLHDPAR And
w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h1.SQL_ADDRESS=xh.KGLHDPAR And
h1.SQL_HASH_VALUE=xh.KGLNAHSH) order by h1.sid;
以下是查询结果
WAITING_SESSION HOLDING_SESSION LOCK OBJECT_OWNER
--------------- --------------- ---- -------------------------------------------
---------------------
OBJECT_NAME
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
TYPE MODE_HELD MODE_REQU WAIT_SQL
HOLD_SQL
---------------------------- --------- --------- -------------------------------
----------------------------- --------------------------------------------------
----------
445 373 Pin GZDLGIS
TRIG_B$GG_PD_DYDX_N_TREE
TRIGGER Share GG_PD_DYDX_N( G3E_F
NO,G3E_FID,G3E_CNO,G3E_CID,G3 insert VALUES(GNWZ_TREE_LIST_s
eq.nextval
1009 373 Pin GZDLGIS
TRIG_B$GG_PD_DYDX_N_TREE
TRIGGER Share INDEX(B$GG_PD_DYDX_N
IDX_B$GG_PD_DYDX_N_G3EID) */ insert Share Share into INSERT INTO GNWZ_TREE_LIST
UPDATE /*+ GNWZ_TREE_LIST
into
VALUES(GNWZ_TREE_LIST_s
eq.nextval
1033 373 Pin GZDLGIS
TRIG_B$GG_PD_DYDX_N_TREE
TRIGGER Share Share update B$GG_PD_DYDX_N set LTT_S
TATUS = NULL where G3E_ID in insert into GNWZ_TREE_LIST VALUES(GNWZ_TREE_LIST_s
eq.nextval
445 487 Pin GZDLGIS
TRIG_B$GG_PD_DYDX_N_TREE
TRIGGER Share Share INSERT INTO GG_PD_DYDX_N( G3E_F
NO,G3E_FID,G3E_CNO,G3E_CID,G3 VALUES(GNWZ_TREE_LIST_s
insert into GNWZ_TREE_LIST
eq.nextval
1009 487 Pin GZDLGIS
TRIG_B$GG_PD_DYDX_N_TREE
TRIGGER Share Share UPDATE /*+ INDEX(B$GG_PD_DYDX_N
IDX_B$GG_PD_DYDX_N_G3EID) VALUES(GNWZ_TREE_LIST_s
*/ insert into GNWZ_TREE_LIST
eq.nextval
1033 487 Pin GZDLGIS
TRIG_B$GG_PD_DYDX_N_TREE
TRIGGER Share Share update B$GG_PD_DYDX_N set LTT_S
TATUS = NULL where G3E_ID in insert into GNWZ_TREE_LIST VALUES(GNWZ_TREE_LIST_s
eq.nextval
从查询结果可以看到HOLDING_SESSION是sid为373与487的这两个进程;
--查询堵塞进程信息
select
sid,serial#,status,username,program,sql_id,machine,event,blocking_session,PREV_SQL_ID from v$session where sid in (373,487);
SID SERIAL# STATUS USERNAME PROGRAM
SQL_ID MACHINE
---------- ---------- -------- ------------------------------ ------------------
------------------------------ ------------- -----------------------------------
-----------------------------
EVENT BLOCKING_SESSIO
N PREV_SQL_ID
---------------------------------------------------------------- ---------------
- -------------
373 33772 ACTIVE DY43 G3E.exe
g8dnqy6bnt4z6 HY\\RSPX023
cursor: pin S wait on X
d0sj7zxgrmkcv
487 34027 ACTIVE ND16 G3E.exe
g8dnqy6bnt4z6 HY\\RSPX039
cursor: pin S wait on X
d0sj7zxgrmkcv
这两个进程分别是数据库用户DY43从计算机名为HY\\RSPX023,ND16从计算机HY\\RSPX039连接数据库的
从查询结果发现PREV_SQL_ID列的值为空,说明这两个进程没有在等待其他进程,即这两个进程都是堵塞源头进程,需要终止掉才能释放共享内存锁
--通过sql_id查询这两个进程正在执行与上一次执行的sql语句
select * from table(dbms_xplan.DISPLAY_CURSOR('g8dnqy6bnt4z6',null));
select * from table(dbms_xplan.DISPLAY_CURSOR('d0sj7zxgrmkcv',null));
--终止堵塞进程
--查询进程的sid,serial#,spid
select s.sid,s.serial#,s.username,s.status,p.spid from v$process p,v$session s where p.addr=s.paddr and s.sid in (373,487);
SID SERIAL# USERNAME STATUS SPID
---------- ---------- ------------------------------ -------- ------------
373 33772 DY43 ACTIVE 3482
487 34027 ND16 ACTIVE 24301
--终止进程语句
alter system kill session '373,33772';
alter system kill session '487,34027';
SQL> alter system kill session '373,33772';
alter system kill session '373,33772'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> alter system kill session '487,34027';
alter system kill session '487,34027'
*
ERROR at line 1:
ORA-00031: session marked for kill
终止sesion过程报\"ORA-00031: session marked for kill\需要退出sqlplus,在操作系统级别手动清除操作系统进程spid
exit
ps -ef|grep 3482
ps -ef|grep 24301
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options
-bash-3.00$ ps -ef|grep 3482
oracle 13482 1 0 14:28:05 ? 0:00 oraclegisdb1 (LOCAL=NO)
oracle 3482 1 0 Nov 15 ? 9:03 oraclegisdb1 (LOCAL=NO)
oracle 11637 11733 0 16:13:11 pts/10 0:00 grep 3482
-bash-3.00$ ps -ef|grep 24301
oracle 24301 1 0 Nov 15 ? 3:15 oraclegisdb1 (LOCAL=NO)
oracle 11710 11733 0 16:13:19 pts/10 0:00 grep 24301
--确认spid都是LOCAL=NO的进程后,用kill -9命令清除操作系统进程,如果spid不是LOCAL=NO的,千万不要终止
--清除操作系统进程命令
kill -9 3482
kill -9 24301
--再次连接数据库,查询等待事件,确认library cache pin与cursor: pin S wait on X 等待事件已消失
export ORACLE_SID=gisdb2
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 CO
EVENT
UNT(*)
---------- ---------------------------------------------------------------- ----
------
1 control file sequential read
1
2 SQL*Net message to client
1
2 PX Deq: reap credit
1
建议:建议应用开发人员调整应用程序,,不要经常对触发器进行disable与enable, 特别是在业务高峰期不要尝试去对trigger,function,procedure,package等对象进行重编译或者disable与enable.
select object_name ,machine ,s.program ,
s.sid||','||s.serial# as killid,
p.spid as os_pid ,
s.sql_id,
l.locked_mode,
s.username,
s.process
from v$locked_object l,
dba_objects o ,v$session s ,v$process p
where l.object_id=o.object_id and l.session_id=s.sid
and s.paddr=p.addr
and object_name='B$GG_PD_DYDX_N'
order by 1;
查询B$GG_PD_DYDX_N被哪些工单卡死
select sid,serial#,username,program,status,sql_id,
blocking_instance,blocking_session from v$session where event='enq: TX - row lock contention' order by blocking_session;
select * from gv$lock where block=1;
