摘选关于library cache lock事件的分析

诊断 library cache lock

--最近1小时内的事件对应 p3
set num 20
select p3,count(0) from v$active_session_history where sample_time>sysdate-1/24 and event='library cache lock' group by p3;

--转换p3
select to_char(&p3,'xxxxxxxxxxxxx') p from dual;

p3 解析 : '100*mode+namespace'=0x4f0003
1557f00010003 <--01 编译存储过程
0x4f0003 <--4f 密码错误或alter user

--再转换
select to_number('4f','xx') l_type from dual;

--查看对应类型
select distinct KGLHDNSP,KGLHDNSD from x$kglob order by 1;

摘选

一、

LIBRARY CACHE PIN通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时. 编译通常都是显性的, 如安装应用程序,升级,安装补丁程序等, 另外,"ALTER","GRANT","REVOKE"等操作也会使object变得无效, 当object变得无效时,Oracle 会在第一次访问此object时试图去重新编译它,如果此时其他session已经把此object pin到library cache中, 就会出现问题, 特别时当有大量的活动session并且存在较复杂的dependency时. 如果此时我们再发出一条grant或compile的命令, 那么library cache lock等待事件将会出现. 在生产数据库中权限的授予,对象的重新编译都可能会导致library cache pin等待的出现. 所以应该尽量避免在高峰期进行以上操作.  另外我们的案例本身就说明: 如果Package或过程中存在复杂的,交互的依赖关系极易导致library cache pin的出现. 所以在应用开发的过程中,我们也应该注意这方面的内容.

 

二、

RAC下 library cache pin与library cache lock的成因与single instance无本质区别，都是为了并发时保护shared pool的内存结构，本篇不再赘述其原理。
在日常排查方面，需要注意一些问题。
   在single instance下，我们找造成pin或者lock的根源，通常是使用v$sesion_wait.p1raw字段，得到handle地址，关联x$kglpn/v$session等基表或者视图来找到blocker的session信息。

  在rac下，如果blocker 和waiter session在不同的实例下，直接通过handle地址就无法获得blocker。因为rac系统每个实例有各自的sga结构，相同的结构对象在各自的library cache里面的handle/heap地址是不相同的。这种情况下，需要通过waiter上pin/lock住的对象名称，去另外的节点上查找该对象的lock/pin持有session信息。
  对library cache pin，全局的hanganalyze并不能trace出该等待的blocker和waiter( 对单节点也同样如此)，但对library cache lock来讲，hanganalyze 是适用的。如果数据库版本在10g以上，通过v$session BLOCKING_SESSION_STATUS/BLOCKING_INSTANCE/BLOCKING_SESSION 这几个字段，便可获取bloker信息。

查询等待：

SQL> select sid,event from v$session_wait where event like ‘library%’;

SID EVENT P1RAW
———- —————————————————————-
126 library cache pin 2B9E5B50

SQL> SELECT a.SID, a.username, a.program
 FROM v$session a, x$kglpn b
 WHERE a.saddr = b.kglpnuse
 AND b.kglpnmod <> 0
 AND b.kglpnhdl='2B9E5B50';

SID USERNAME PROGRAM
———- —————————— —————————-
122 TEST sqlplus@node1 (TNS V1-V3)

三、

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

select owner,object_name,status from dba_objects where object_id=18;

OWNER         OBJECT_NAME    STATUS
---------- ---------------- -------
SYS           OBJ$           VALID

就是这个对象搞得，估计是开发人员异常退出一些进程

select serial#,username,command,lockwait,status,schemaname,osuser,

machine,terminal,program,module from v$session where sid=30;

应该查询一下执行的语句:

select P.spid, s.SID,s.SERIAL#,s.USERNAME,s.PROGRAM,s.STATUS,sq.SQL_TEXT
  from v$process P, v$session S,v$sqlarea sq
 WHERE S.PADDR = P.ADDR and s.TYPE<>'BACKGROUND'  

AND S.SID = &sid
   AND s.SQL_HASH_VALUE=sq.HASH_VALUE

找出关联的进程，并kill掉即可
select b.username username, b.terminal terminal,b.program program,b.spid

from v$session a, v$process b
where a.PADDR=b.ADDR and a.sid ='&sid';