2013-12-26一次library cache lock的诊断--OEM引发的

来源：互联网发布：送男朋友的礼物知乎编辑：程序博客网时间：2024/06/08 06:57

公司内有一个系统普遍慢，对于这种普遍慢的情况，就看AWR报告，晚上在用户不使用的情况下负载都很高(有4个逻辑CPU)，可以看到library cache lock的占比非常大。

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Snap IdSnap TimeSessionsCursors/SessionBegin Snap:1088225-12月-13 00:00:361887.5End Snap:1089025-12月-13 08:00:041877.2Elapsed: 479.46 (mins) DB Time: 999.69 (mins) EventWaitsTime(s)Avg wait (ms)% DB timeWait Classlibrary cache lock3,41923,204678738.69ConcurrencyDB CPU 9,997 16.67 log file sync84,34011210.19CommitSQL*Net message from dblink108,7285610.09NetworkSQL*Net message to client1,025,896700.01Network

对于这种问题，首先看到的是v$session_wait，我想通过v$session_wait，v$session，v$sql找到执行的sql，一直都没有成功，原因是library cache lock总是一闪而过，总是抓不到。还找到老盖提供的脚本，还是不行：

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%')));

上面的脚本可以找到那种长期锁住的持有者，但对于一闪而过的，基本没办法。我一直盯着v$session看，产生library cache lock事件的osuser,process,machine,terminal的信息都指向数据库服务器自身。此时我已经诊断问题2个小时了，突然灵机一动，我觉得是OEM的问题，然后把OEM停掉，重新监视v$session_wait，library cache lock居然没有了，此时天色已晚，明天在说。

上班来第一件事就是看AWR报告，惊喜的发现library cache lock没有了，系统整体快了，如：

调整前的AWR中的一条SQL，每次执行需要11.02s。

Elapsed Time (s)ExecutionsElapsed Time per Exec (s)%Total%CPU%IOSQL IdSQL ModuleSQL Text2,732.7424811.029.0798.960.00gqv8md6utz27xJDBC Thin ClientSELECT CQ.ID, CQ.CREATOR_ID, C...

调整后的同样的SQL，每次执行需要1.55s。

Elapsed Time (s)ExecutionsElapsed Time per Exec (s)%Total%CPU%IOSQL IdSQL ModuleSQL Text373.442411.5525.90100.630.00gqv8md6utz27xJDBC Thin ClientSELECT CQ.ID, CQ.CREATOR_ID, C...

0 0