一、故障描述
l 2010年12月31日早晨上班后接到运维组同事反馈,EMC全部程序都被HANG住,没有继续执行,也没有报错。
l 紧接着,项目组尤欣星反应,无法DROP空表,王健反应无法查询表,没有任何明显报错,只是表象一直是执行,一个原本很简单的秒级查询已经运行半个小时。询问西南所有项目组同事,发现所有人都遇到了同样HANG死的问题,是一个大面积的事故。
二、故障分析
n 根据事件的表象,询问车廷曦近期数据库有何调整,据车廷曦反馈,无任何调整,查看数据库主机运行状况,除了正在做的RMAN,也没有其他异常占用很大资源的操作。
n 于是登录EDW进行测试,尝试DROP空表,当前的SID是739,等待一段时间后,查询v$session _wait视图,发现等待事件是library cache pin。
n 那么,什么是library cache pin呢?Oracle文档上这样介绍这个等待事件:library cache pin是用来管理library cache的并发访问的,pin一个Object会引起相应的heap被载入内存中(如果此前没有被加载),pins可以在Null、Share(2)、Exclusive(3)这3个模式下获得,可以认为pin是一种特定形式的锁。当library cache pin等待事件出现时,通常说明该pin被其他用户已非兼容模式持有。library cache pin的等待时间为3秒钟,其中有1秒钟用于PMON后台进程,即在取得pin之前最多等待3秒钟,否则就超时。library cache pin的参数有P1(KGL Handle Address)、P2(Pin Address)和P3(Encoded Mode & Namespace),常用的主要是P1和P2。library cache pin通常是发生在编译或重新编译PL/SQL、VIEW、TYPES等Object时。编译通常是显性的,如安装应用程序、升级、安装补丁程序等,另外ALTER、GRANT、REVOKE等操作也会使Object变得无效,可以通过Object的LAST_DDL_TIME观察这些变化。当Object变得无效时,Oracle会在第一次访问此Object时试图去重新编译它,如果此时其他session已经把此Object pin到library cache中,就会出现问题,特别是当大量的活动session并且存在较复杂的dependence时。在某种情况下,重新编译Object可能会花费几个小时,从而阻塞其他试图去访问此Object的过程。
三、故障解决
ü 既然查找到了等待事件,从而问题就变得明朗,由于是大面积的阻塞,所以不太可能是某一对象所造成的阻塞,而是某些个数据字典(内部对象)所造成的阻塞。
ü 于是让昌哥帮我运行如下语句:
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
FROM v$session_wait w, x$kglpn p, v$session s, v$process o
WHERE p.kglpnuse = s.saddr
AND kglpnhdl = w.p1raw
and w.event like '%library cache pin%'
and s.paddr = o.addr
得出结果:(部分省略)
SID Mode Req OS Process
---------- ---------- ---------- ------------
880 3 0 1004194
880 3 0 1004194
880 3 0 1004194
880 3 0 1004194
880 3 0 1004194
880 3 0 1004194
880 3 0 1004194
880 3 0 1004194
880 3 0 1004194
880 3 0 1004194
880 3 0 1004194
SID Mode Req OS Process
---------- ---------- ---------- ------------
739 0 2 1212618
739 0 2 1212618
739 0 2 1212618
739 0 2 1212618
739 0 2 1212618
739 0 2 1212618
739 0 2 1212618
739 0 2 1212618
739 0 2 1212618
739 0 2 1212618
739 0 2 1212618
SID Mode Req OS Process
---------- ---------- ---------- ------------
991 0 2 1872496
991 0 2 1872496
991 0 2 1872496
991 0 2 1872496
991 0 2 1872496
991 0 2 1872496
991 0 2 1872496
991 0 2 1872496
可以看出,880正在持有Exclusive(3)锁,而我的SESSION 739正在请求Share(2)锁,其他和我类似的还有很多很多很多的进程都在等待请求Share(2)锁,于是可以果断的判断,必定是此进程阻塞了服务器的整体运行。
a) 查询SID为880 的SESSION情况,当前执行的语句是:
/* Formatted on 2010/12/31 11:49:55 (QP5 v5.149.1003.31008) */
DELETE FROM cdef$
WHERE obj# = :1
可以看到,应该是在执行DROP操作,从字典表中删除某个对象信息。由于时间紧迫,直接选择杀掉进程:
SQL> alter system disconnect session '880,43305' immediate;
System altered
SQL>
此后,所有操作回复正常。
SQL> drop table LS_YXX_to_num_check;
Table dropped
SQL>
四、附录
n x$kglpn 描述
X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
它是与x$kgllk相对应的表﹐是关于pin的相关信息。它主要用于解决library cache pin
引用该表的视图有﹕
DBA_KGLLOCK
SQL> desc x$kglpn;
名称 类型
------------ ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW(4)
KGLPNUSE RAW(4) ---会话地址(对应v$session的saddr)
KGLPNSES RAW(4) ---owner地址
KGLPNHDL RAW(4) ---句柄
KGLPNLCK RAW(4)
KGLPNCNT NUMBER
KGLPNMOD NUMBER ---持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLPNREQ NUMBER ---请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLPNDMK NUMBER
KGLPNSPN NUMBER ---对应跟踪文件的savepoint的值
n 问题SESSION信息
附带一些诊断SQL:
1.通过查询v$session_wait找到正在等待“library cache pin”的session:
select sid waiter,
substr(rawtohex(p1), 1, 30) handle,
substr(rawtohex(p2), 1, 30) ping_addr
from v$session_wait
where wait_time = 0
and event like 'library cache pin%';
2.通过查询DBA_LOCK_INTERNAL和v$session_wait,可能得到与“library cache pin”等待相关的object的名字:
select to_char(SESSION_ID, '999') sid,
substr(LOCK_TYPE, 1, 30) type,
substr(lock_id1, 1, 23) object_name,
substr(mode_held, 1, 4) held,
substr(mode_requested, 1, 4) req,
lock_id2 lock_addr
from dba_lock_internal
where mode_requested <> 'None'
and mode_requested <> mode_held
and session_id = $sid
3.查出“library cache pin”占有者(即阻塞者)的session id:
select sid holder, KGLPNUSE session_id, KGLPNMOD held, KGLPNREQ req
from x$kglpn, v$session
where KGLPNHDL in (select p1raw
from v$session_wait
where wait_time = 0
and event like 'library cache pin%')
and KGLPNMOD <> 0
and v$session.saddr = x$kglpn.kglpnuse;
---第二版
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
FROM v$session_wait w, x$kglpn p, v$session s, v$process o
WHERE p.kglpnuse = s.saddr
AND kglpnhdl = w.p1raw
and w.event like '%library cache pin%'
and s.paddr = o.addr
4.查出“library cache pin”占有者(即阻塞者)正在等什么:
select sid,event,wait_time
from v$session_wait
where sid in (
select sid from x$kglpn, v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache pin%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse
)
5.查看阻塞者正在执行的SQL
select sid,sql_text
from v$session,v$sqlarea
where v$session.sql_address=v$sqlarea.address
and sid=&sid;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16628454/viewspace-1043944/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16628454/viewspace-1043944/