library cache lock问题解决扩展

之前已有一篇处理library cache lock问题的blog文，本篇主要强化一下处理library cache lock/library cache pin问题的熟练度，总结一下有关SQL。

关键字：library cache lock 等待事件

[@more@]

__________________________________________________________________________________

周末在家，Jessie的同事打电话来，报告她公司的一个生产DB服务异常。

问题1

现象

重建一个MV的过程中，drop mlog提示资源忙。

原因

对该MV依赖的基表做DML操作会引起对mlog的INSERT操作，此时drop mlog会提示资源忙。

解决方法

停止所有对该MV依赖的基表所做的DML，drop mlog成功，重建MV，mlog完成。

问题2

现象

应用报超过连接数，无法连接数据库。

原因

造成连接数不足无非两个方面原因：

1.中间件配置问题

2.数据库问题。

中间件我们这边管不到，先检查DB的问题。最先想到的可能的两种情况：

1． sessions设置不足。

2． 大量session等待不能释放，最后达到阀值，新链接无法建立。

周末处于业务低谷，工作日是高峰。业务高峰期没有出现连接达到阀值，低谷期却出现了。由此判断1的可能性不大，直接针对2入手。

检查等待事件，非空闲等待中library cache lock高居榜首。原因找到。

解决方法

获得library cache lock等待的对象

SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj

FROM x$kglob

WHERE kglhdadr IN

(SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%')

居然是同一个对象！

告诉运维同事该对象造成了等待，询问是否在对该对象做DDL。得到肯定的反馈。运维同事终止DDL操作，系统恢复正常。

总结

本次问题都是常见问题，主要强化一下处理library cache lock/library cache pin问题的熟练度，总结一下有关SQL。再次感谢Jessie，感谢盖哥。

将会涉及到的两个oracle底层表X$KGLOB和X$KGLPN，含义如下：

X$KGLOB：[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject

X$KGLPN：[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s

获得library cache lock/library cache pin等待的对象

SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj

FROM x$kglob

WHERE kglhdadr IN

(SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%')

获得持有等待对象的session信息

SELECT a.SID,

a.SERIAL#,

a.username,

a.osuser,

a.program,

b.addr,

b.kglpnadr,

b.kglpnuse,

b.kglpnses,

b.kglpnhdl,

b.kglpnlck,

b.kglpnmod,

b.kglpnreq

FROM v$session a, x$kglpn b

WHERE a.saddr = b.kglpnuse

AND b.kglpnmod <> 0

AND b.kglpnhdl IN

(SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%');

获得持有对象用户执行的代码

SELECT sql_text

FROM v$sqlarea

WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN

(SELECT sql_address, sql_hash_value

FROM v$session

WHERE SID IN (SELECT SID

FROM v$session a, x$kglpn b

WHERE a.saddr = b.kglpnuse

AND b.kglpnmod <> 0

AND b.kglpnhdl IN

(SELECT p1raw

FROM v$session_wait

WHERE event LIKE 'library%')));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21129591/viewspace-1054561/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21129591/viewspace-1054561/