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

        公司内有一个系统普遍慢，对于这种普遍慢的情况，就看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...