Oracle等待事件library cache pin异常处理

Oracle等待事件library cache pin异常处理

• 产品：Oracle Database
• 版本： 11g RAC
• 环境： CentOS Linux 7

异常信息

在今天的业务高峰期收到了2个数据库节的告警通知：

1节点：
报警类型：日志报警
策略名称：Oracle异常事件
告警设备：192.168.1.11
设备名称：xsjan1
业务系统：XS
异常事件类型：WAITEVENT_COUNT
异常内容：xsjan1 has waitevent -> library cache pin(42);
触发时间：2022-03-11T16:00:12.000+08:00
报警策略：oracle-monitor

2节点：
报警类型：日志报警
策略名称：Oracle异常事件
告警设备：192.168.1.12
设备名称：xsjan2
业务系统：XS
异常事件类型：WAITEVENT_COUNT
异常内容：xsjan2 has waitevent -> library cache pin(11);
触发时间：2022-03-11T16:01:00.000+08:00
报警策略：oracle-monitor

PS：Oracle数据库自动化性能监控脚本及使用方法，可到自动化运维模块下载。

告警显示当时数据库存在大量library cache pin等待事件。

library cache pin说明

当我们对业务对象，如：包体，存储过程，函数，视图等进行编译时，Oracle会在这些对象位于内存缓冲区的handle上面先获得一个library cache lock，然后再在这些对象的heap上获得pin，用于保证在编译的时候其它进程不会来更改这些对象的定义，或者将对象删除。

library cache pin等待事件常见于以下几种场景：
一、会话1正在运行业务对象A，此时会话2对A进行编译或权限变更，会被会话1阻塞，引起library cache pin；
若此时会话3运行、编译或变更A权限，会被会话2阻塞，引起library cache lock；

二、会话1正在运行业务对象A，此时会话2修改A的依赖对象，会导致A失效，此时会话3运行A前会自动编译A，会被会话1阻塞，引起library cache pin；

总之，在业务高峰期对业务对象进行变更操作，都存在引起业务阻塞的风险。

library cache pin等待事件排查方法

场景一:
1、找出申请对象独占锁的会话，确认由人为发起；
2、建议中断相关会话，待非业务高峰时段重新发起，减少对业务影响；
3、对在业务高峰做骚操作的人进行教(暴)育(打)。

查询脚本：

--查询ReqPin为3，即申请独占锁的会话信息，确认客户端主机名、访问工具、操作内容
SELECT distinct decode(kglpnreq,0,'holding_session:'||s.sid,'waiting_session: '||s.sid) sid,
s.SERIAL#,s.machine,s.program, kglpnmod "Pin Mode", kglpnreq "ReqPin",a.sql_text,kglnaown "Owner", kglnaobj "Object"
FROM x$kglpn p, v$session s,v$sqlarea a,v$session_wait sw,x$kglob x
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=sw.p1raw
and kglhdadr=sw.p1raw
and s.event like 'library cache%'
and (a.hash_value, a.address) IN (
select
 DECODE (sql_hash_value,0,prev_hash_value,sql_hash_value),
 DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
 from v$session s2
 where s2.sid=s.sid
 );
--PS：以上脚本已整合到数据库自动化性能监控中，当出现相关异常等待事件时，自动收集记录方便回溯分析，可到自动化运维模块下载使用。

场景二:
1、找出申请对象独占锁的会话，确认由应用而非人为发起；
2、通过v$session找出阻塞源及其正在运行的对象；
3、通过审计或DDL触发器记录找出最近执行DDL的操作信息；
4、确认最近被修改的对象属于阻塞源的依赖对象；
5、建议中断阻塞源的会话，待依赖对象自动编译完成后重新发起；
6、对在业务高峰做骚操作的人进行教(暴)育(打)。

查询脚本：

--查询ReqPin为3，即申请独占锁的会话信息，确认由应用而非人为发起，记录会话ID
SELECT distinct decode(kglpnreq,0,'holding_session:'||s.sid,'waiting_session: '||s.sid) sid,
s.SERIAL#,s.machine,s.program, kglpnmod "Pin Mode", kglpnreq "ReqPin",a.sql_text,kglnaown "Owner", kglnaobj "Object"
FROM x$kglpn p, v$session s,v$sqlarea a,v$session_wait sw,x$kglob x
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=sw.p1raw
and kglhdadr=sw.p1raw
and s.event like 'library cache%'
and (a.hash_value, a.address) IN (
select
 DECODE (sql_hash_value,0,prev_hash_value,sql_hash_value),
 DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
 from v$session s2
 where s2.sid=s.sid
 );
--PS：以上脚本已整合到数据库自动化性能监控中，当出现相关异常等待事件时，自动收集记录方便回溯分析，可到自动化运维模块下载使用。

--查找阻塞源会话ID
select username,sql_id,event,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION,count(*) from v$session
where event like 'library cache%' 
and sid in (&sid)
group by username,sql_id,event,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION
order by 6,sql_id,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION;

--查询阻塞源正在运行的对象
select s.sid,s.serial#,s.program,s.machine,q.sql_text 
from v$session s,v$sql q
where s.sql_id=q.sql_id 
and s.sid=&sid;

--通过DDL触发器记录，找出最近执行DDL的操作信息
select * from sys.t_ddl_oper order by 1;
--PS：DDL触发器及使用方法，可到自动化运维模块下载。

--确认最近被修改的对象为阻塞源的依赖对象
select t.OWNER,NAME,TYPE,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE,REFERENCED_LINK_NAME 
from dba_dependencies t,dba_objects o
where t.REFERENCED_OWNER=o.owner
and t.REFERENCED_NAME=o.object_name
and NAME = &NAME
order by 1,2;

本次异常分析

本次library cache pin异常等待与场景二类似，分析的过程如下：

1、查看数据库性能监控记录，确认由应用发起独占锁申请：

LOCK_MODE_HELD LOCK_MODE_REQUESTED    INST_ID	     SID    SERIAL# 	    MODULE 			     SQL_ID
-------------- ------------------- ---------- ---------- ---------- ------------------  ---------------     
	     0	            		 3	           1	      1360      61795    JDBC Thin Client		fpj2z97rr2pqs
	     0	            		 3	           1	      5003      39893    JDBC Thin Client		3ysb59vyg2fwf

2、查询v$session找出阻塞源：

select sql_id,event,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION,count(*) from v$session
where event like 'library cache%' 
and sid in (1360，5003)
group by sql_id,event,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION
order by 6,sql_id,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION;

SQL_ID	          EVENT	     FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION	COUNT(1)
------------- ----------------- -----------------      ----------------  --------
fpj2z97rr2pqs	library cache pin	      1                	5629	      898
3ysb59vyg2fwf	library cache pin	      1	                5629	      891

3、通过ddl触发器记录找出最近执行ddl的操作信息(表名、主机名、IP已做处理，这位同事准备接受教(暴)育(打))：

select * from sys.t_ddl_oper order by 1;

OPER_TIME	     OPER_OBJ_OWNER	OPER_OBJ_NAME	OPER_OBJ_TYPE	     OPER_MODULE  	LOGIN_IP	LOGIN_HOST	      SQL_TEXT
------------------ -------------- ------------- ------------- ------------------- --------  ----------  --------------------------------
2022/3/11 15:51:34	   XS	        TABLE1	        TABLE    	PL/SQL Developer  192.168.1.145	HOST1	 alter table TABLE1 modify compid null
2022/3/11 15:52:26	   XS	        TABLE2	      	TABLE	     PL/SQL Developer	192.168.1.145	HOST1	 alter table TABLE2 modify compid null
2022/3/11 15:53:53	   XS	        TABLE3	      	TABLE	     PL/SQL Developer	192.168.1.145	HOST1	 alter table TABLE3 modify compid null
2022/3/11 15:54:03	   XS	        TABLE4	      	TABLE	     PL/SQL Developer	192.168.1.145	HOST1	 alter table TABLE3 modify compid null

4、确认最近被修改的对象属于阻塞源的依赖对象：

select t.OWNER,NAME,TYPE,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE,REFERENCED_LINK_NAME 
from dba_dependencies t,dba_objects o
where t.REFERENCED_OWNER=o.owner
and t.REFERENCED_NAME=o.object_name
and NAME = &NAME
order by 1,2;

5、中断阻塞源会话：

alter system disconnect session '5629,2345' immediate;

结论

两种引起library cache pin的场景说明，需要避免在业务高峰对业务对象进行ddl或dcl操作；尽管过程曲折，但这次也算顺利找到了“真凶”。

关注公众号，学习更多运维实战案例！