公司内有一个系统普遍慢,对于这种普遍慢的情况,就看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 Id | Snap Time | Sessions | Cursors/Session | |
---|---|---|---|---|
Begin Snap: | 10882 | 25-12月-13 00:00:36 | 188 | 7.5 |
End Snap: | 10890 | 25-12月-13 08:00:04 | 187 | 7.2 |
Elapsed: | 479.46 (mins) | |||
DB Time: | 999.69 (mins) |
Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
---|---|---|---|---|---|
library cache lock | 3,419 | 23,204 | 6787 | 38.69 | Concurrency |
DB CPU | 9,997 | 16.67 | |||
log file sync | 84,340 | 112 | 1 | 0.19 | Commit |
SQL*Net message from dblink | 108,728 | 56 | 1 | 0.09 | Network |
SQL*Net message to client | 1,025,896 | 7 | 0 | 0.01 | Network |
对于这种问题,首先看到的是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) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|---|---|---|
2,732.74 | 248 | 11.02 | 9.07 | 98.96 | 0.00 | gqv8md6utz27x | JDBC Thin Client | SELECT CQ.ID, CQ.CREATOR_ID, C... |
调整后的同样的SQL,每次执行需要1.55s。
Elapsed Time (s) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|---|---|---|
373.44 | 241 | 1.55 | 25.90 | 100.63 | 0.00 | gqv8md6utz27x | JDBC Thin Client | SELECT CQ.ID, CQ.CREATOR_ID, C... |