[color=#555555][font=Arial, Verdana, sans-serif]常见的library cache lock产生的原因在《高级OWI与Oracle性能调查》这本书和下面这个文档中有一般性的描述：
[color=#555555][font=Arial, Verdana, sans-serif]Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1)

[color=#555555][font=Arial, Verdana, sans-serif]一般可以理解的是alter table或者alter package/procedure会以X模式持有library cache lock，造成阻塞。
[color=#555555][font=Arial, Verdana, sans-serif]但是常见的问题还有以下几种原因：

[color=#555555][font=Arial, Verdana, sans-serif]1)错误的用户名密码：

[color=#555555][font=Arial, Verdana, sans-serif]一般需要通过ASH或者SSD/hang analyze去获取p3进行namespace分析。

[color=#555555][font=Arial, Verdana, sans-serif] 1. event: 'library cache lock'
[color=#555555][font=Arial, Verdana, sans-serif] time waited: 43 min 12 sec
[color=#555555][font=Arial, Verdana, sans-serif] wait id: 9 p1: 'handle address'=0x7000003117dfca0
[color=#555555][font=Arial, Verdana, sans-serif] p2: 'lock address'=0x700000310866c80
[color=#555555][font=Arial, Verdana, sans-serif] p3: '100*mode+namespace'=0x4f0003
[color=#555555][font=Arial, Verdana, sans-serif] * time between wait #1 and #2: 0.000164 sec

[color=#555555][font=Arial, Verdana, sans-serif]<=================p3: '100*mode+namespace'=0x4f0003 [color=#555555][font=Arial, Verdana, sans-serif]mode=3 [color=#555555][font=Arial, Verdana, sans-serif]namespace=4f [color=#555555][font=Arial, Verdana, sans-serif]HEX: 4f =>DEC: 79

[color=#555555][font=Arial, Verdana, sans-serif]select * FROM V$DB_OBJECT_CACHE;

[color=#555555][font=Arial, Verdana, sans-serif]SQL> select distinct KGLHDNSP,KGLHDNSD from x$kglob;

[color=#555555][font=Arial, Verdana, sans-serif] KGLHDNSP KGLHDNSD
[color=#555555][font=Arial, Verdana, sans-serif]---------- ----------------------------------------------------------------
[color=#555555][font=Arial, Verdana, sans-serif] 0 SQL AREA
[color=#555555][font=Arial, Verdana, sans-serif] 4 INDEX
[color=#555555][font=Arial, Verdana, sans-serif] 1 TABLE/PROCEDURE
[color=#555555][font=Arial, Verdana, sans-serif] 3 TRIGGER
[color=#555555][font=Arial, Verdana, sans-serif] 52 SCHEDULER EARLIEST START TIME
[color=#555555][font=Arial, Verdana, sans-serif] 64 EDITION
[color=#555555][font=Arial, Verdana, sans-serif] 69 DBLINK
[color=#555555][font=Arial, Verdana, sans-serif] 2 BODY
[color=#555555][font=Arial, Verdana, sans-serif] 10 QUEUE
[color=#555555][font=Arial, Verdana, sans-serif] 79 ACCOUNT_STATUS
[color=#555555][font=Arial, Verdana, sans-serif] 23 RULESET
[color=#555555][font=Arial, Verdana, sans-serif] 24 RESOURCE MANAGER
[color=#555555][font=Arial, Verdana, sans-serif] 73 SCHEMA
[color=#555555][font=Arial, Verdana, sans-serif] 74 DBINSTANCE
[color=#555555][font=Arial, Verdana, sans-serif] 51 SCHEDULER GLOBAL ATTRIBUTE
[color=#555555][font=Arial, Verdana, sans-serif] 38 RULE EVALUATION CONTEXT
[color=#555555][font=Arial, Verdana, sans-serif] 82 SQL AREA BUILD
[color=#555555][font=Arial, Verdana, sans-serif] 75 SQL AREA STATS
[color=#555555][font=Arial, Verdana, sans-serif] 5 CLUSTER
[color=#555555][font=Arial, Verdana, sans-serif] 18 PUB SUB INTERNAL INFORMATION

[color=#555555][font=Arial, Verdana, sans-serif]<======79 ACCOUNT_STATUS [color=#555555][font=Arial, Verdana, sans-serif]ACCOUNT_STATUS说明library cache lock是在account上，可能是用错误的用户名密码登录，或者是当时正有人alter user(这种几率极低）。 [color=#555555][font=Arial, Verdana, sans-serif]可以通过以下SQL去确认错误的用户名密码登录： [color=#555555][font=Arial, Verdana, sans-serif]select username, [color=#555555][font=Arial, Verdana, sans-serif]os_username, [color=#555555][font=Arial, Verdana, sans-serif]userhost, [color=#555555][font=Arial, Verdana, sans-serif]client_id, [color=#555555][font=Arial, Verdana, sans-serif]trunc(timestamp), [color=#555555][font=Arial, Verdana, sans-serif]count(*) failed_logins [color=#555555][font=Arial, Verdana, sans-serif]from dba_audit_trail [color=#555555][font=Arial, Verdana, sans-serif]where returncode=1017 and --1017 is invalid username/password [color=#555555][font=Arial, Verdana, sans-serif]timestamp < sysdate -7 [color=#555555][font=Arial, Verdana, sans-serif]group by username,os_username,userhost, client_id,trunc(timestamp); [color=#555555][font=Arial, Verdana, sans-serif]Or run following sql: [color=#555555][font=Arial, Verdana, sans-serif]SELECT "USERNAME", "OS_USERNAME", "USERHOST", "EXTENDED_TIMESTAMP",returncode FROM "SYS"."DBA_AUDIT_SESSION" WHERE returncode != 0; [color=#555555][font=Arial, Verdana, sans-serif]当然必须确保audit 打开，并且有audit CREATE SESSION动作 [color=#555555][font=Arial, Verdana, sans-serif]To turn on audit: [color=#555555][font=Arial, Verdana, sans-serif]Alter system set audit_trail=DB scope=spfile; [color=#555555][font=Arial, Verdana, sans-serif]restart DB [color=#555555][font=Arial, Verdana, sans-serif]audit CREATE SESSION; [color=#555555][font=Arial, Verdana, sans-serif]audit ALTER USER; [color=#555555][font=Arial, Verdana, sans-serif]检查： [color=#555555][font=Arial, Verdana, sans-serif]show parameter audit_trail [color=#555555][font=Arial, Verdana, sans-serif]select * from DBA_STMT_AUDIT_OPTS; [color=#555555][font=Arial, Verdana, sans-serif]2)正在执行搜集统计信息，这是大家往往会忽略的，一般会看last_ddl_time，却忽略了last_analyzed， [color=#555555][font=Arial, Verdana, sans-serif]检查脚本如下： [color=#555555][font=Arial, Verdana, sans-serif]比如EMP是遇到library cache lock中的表名： [color=#555555][font=Arial, Verdana, sans-serif]select owner,object_name,object_type,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') from dba_objects where object_name='EMP'; [color=#555555][font=Arial, Verdana, sans-serif]select table_name,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='EMP'; [color=#555555][font=Arial, Verdana, sans-serif]也需要检查所有dependency的对象，因为oracle对象是相互关联的，一个对象失效会导致一串失效。 [color=#555555][font=Arial, Verdana, sans-serif]select owner,object_name,object_type,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') ddl_time from dba_objects where object_name in [color=#555555][font=Arial, Verdana, sans-serif]( [color=#555555][font=Arial, Verdana, sans-serif]select p.name [color=#555555][font=Arial, Verdana, sans-serif]from sys.obj$ d, sys.dependency$ dep, sys.obj$ p [color=#555555][font=Arial, Verdana, sans-serif]where d.obj# = dep.d_obj# and p.obj# = dep.p_obj# [color=#555555][font=Arial, Verdana, sans-serif]start with d.name='EMP' [color=#555555][font=Arial, Verdana, sans-serif]connect by prior dep.p_obj#=dep.d_obj#) [color=#555555][font=Arial, Verdana, sans-serif]order by ddl_time desc; [color=#555555][font=Arial, Verdana, sans-serif]select table_name,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name in [color=#555555][font=Arial, Verdana, sans-serif]( [color=#555555][font=Arial, Verdana, sans-serif]select p.name [color=#555555][font=Arial, Verdana, sans-serif]from sys.obj$ d, sys.dependency$ dep, sys.obj$ p [color=#555555][font=Arial, Verdana, sans-serif]where d.obj# = dep.d_obj# and p.obj# = dep.p_obj# [color=#555555][font=Arial, Verdana, sans-serif]start with d.name='EMP' [color=#555555][font=Arial, Verdana, sans-serif]connect by prior dep.p_obj#=dep.d_obj#) [color=#555555][font=Arial, Verdana, sans-serif]order by last_analyzed desc; [color=#555555][font=Arial, Verdana, sans-serif]比较典型的一个用户实例： [color=#555555][font=Arial, Verdana, sans-serif]select to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='XXXXX'; [color=#555555][font=Arial, Verdana, sans-serif]--2014-11-25 16:52:50 [color=#555555][font=Arial, Verdana, sans-serif]<=============gathering statistics in the issue time [color=#555555][font=Arial, Verdana, sans-serif]2014-11-25 16:52:52 16620 c34q5c8gf6kum library cache lock [color=#555555][font=Arial, Verdana, sans-serif]2014-11-25 16:52:52 16643 c34q5c8gf6kum library cache lock [color=#555555][font=Arial, Verdana, sans-serif]<======The issue starts from 16:52:52 while statistics was gathered at 16:52:50 [color=#555555][font=Arial, Verdana, sans-serif]3)错误的语句解析（failed parse） [color=#555555][font=Arial, Verdana, sans-serif]这是通常很难注意到的一个问题，因为被解析的语句往往在AWR中找不到（因为没有通过parse），要注意查看AWR中的“failed parse elapsed time” [color=#555555][font=Arial, Verdana, sans-serif]Event Waits Time(s) Avg wait (ms) % DB time Wait Class [color=#555555][font=Arial, Verdana, sans-serif]library cache lock 6,714,208 363,093 54 67.14 Concurrency [color=#555555][font=Arial, Verdana, sans-serif]library cache: mutex X 11,977,886 99,050 8 18.31 Concurrency [color=#555555][font=Arial, Verdana, sans-serif]DB CPU 38,971 7.21 [color=#555555][font=Arial, Verdana, sans-serif]db file sequential read 350,069 2,465 7 0.46 User I/O [color=#555555][font=Arial, Verdana, sans-serif]log file sync 217,673 1,969 9 0.36 Commit [color=#555555][font=Arial, Verdana, sans-serif]Statistic Name Time (s) % of DB Time [color=#555555][font=Arial, Verdana, sans-serif]sql execute elapsed time 537,418.09 99.37 [color=#555555][font=Arial, Verdana, sans-serif]parse time elapsed 467,101.99 86.37 [color=#555555][font=Arial, Verdana, sans-serif]failed parse elapsed time 460,663.79 85.18 <===============failed parse elapsed time was high. That means the issue was caused by parse failed. [color=#555555][font=Arial, Verdana, sans-serif]详细请参考： [color=#555555][font=Arial, Verdana, sans-serif]High Waits for 'library cache lock' and 'library cache: mutex X' Due to Parse Failures When Using JDBC ResultSet.TYPE_SCROLL_SENSITIVE (Doc ID 1566018.1)