常见的library cache lock产生的原因是什么

常见的library cache lock产生的原因是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

常见的library cache lock产生的原因

Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1)

一般可以理解的是alter table或者alter package/procedure会以X模式持有library cache lock，造成阻塞。

但是常见的问题还有以下几种原因：

1)错误的用户名密码：

一般需要通过ASH或者SSD/hang analyze去获取p3进行namespace分析。

             1.       event: library cache lock

time waited: 43 min 12 sec

                    wait id: 9               p1: handle address=0x7000003117dfca0

p2: lock address=0x700000310866c80

                                             p3: 100*mode+namespace=0x4f0003

             * time between wait #1 and #2: 0.000164 sec

<=================p3: 100*mode+namespace=0x4f0003

mode=3

namespace=4f

HEX: 4f =>DEC: 79

select * FROM V$DB_OBJECT_CACHE;

SQL> select distinct KGLHDNSP,KGLHDNSD from x$kglob;

  KGLHDNSP KGLHDNSD

---------- ----------------------------------------------------------------

         0 SQL AREA

         4 INDEX

         1 TABLE/PROCEDURE

         3 TRIGGER

52 SCHEDULER EARLIEST START TIME

        64 EDITION

        69 DBLINK

         2 BODY

        10 QUEUE

        79 ACCOUNT_STATUS

        23 RULESET

24 RESOURCE MANAGER

        73 SCHEMA

        74 DBINSTANCE

        51 SCHEDULER GLOBAL ATTRIBUTE

        38 RULE EVALUATION CONTEXT

        82 SQL AREA BUILD

75 SQL AREA STATS

         5 CLUSTER

        18 PUB SUB INTERNAL INFORMATION

<======79 ACCOUNT_STATUS

ACCOUNT_STATUS说明library cache lock是在account上，可能是用错误的用户名密码登录，或者是当时正有人alter user(这种几率极低）。

可以通过以下SQL去确认错误的用户名密码登录：

select username,

os_username,

userhost,

client_id,

trunc(timestamp),

count(*) failed_logins

from dba_audit_trail

where returncode=1017 and --1017 is invalid username/password

timestamp < sysdate -7

group by username,os_username,userhost, client_id,trunc(timestamp);

Or run following sql:

SELECT "USERNAME", "OS_USERNAME", "USERHOST", "EXTENDED_TIMESTAMP",returncode  FROM "SYS"."DBA_AUDIT_SESSION" WHERE returncode != 0;

当然必须确保audit 打开，并且有audit CREATE SESSION动作

To turn on audit:

Alter system set audit_trail=DB scope=spfile;

restart DB

audit CREATE SESSION;

audit ALTER USER;

检查：

show parameter audit_trail

select * from DBA_STMT_AUDIT_OPTS;

2)正在执行搜集统计信息，这是大家往往会忽略的，一般会看last_ddl_time，却忽略了last_analyzed，

检查脚本如下：

比如EMP是遇到library cache lock中的表名：

select owner,object_name,object_type,to_char(last_ddl_time,yyyy-mm-dd hh34:mi:ss) from dba_objects where object_name=EMP;

select table_name,to_char(last_analyzed,yyyy-mm-dd hh34:mi:ss) from dba_tables where table_name=EMP;

也需要检查所有dependency的对象，因为oracle对象是相互关联的，一个对象失效会导致一串失效。

select owner,object_name,object_type,to_char(last_ddl_time,yyyy-mm-dd hh34:mi:ss) ddl_time from dba_objects where object_name in

(

select p.name

from sys.obj$ d, sys.dependency$ dep, sys.obj$ p

where d.obj# = dep.d_obj# and p.obj# = dep.p_obj#

start with d.name=EMP

connect by prior dep.p_obj#=dep.d_obj#)

order by ddl_time desc;

select table_name,to_char(last_analyzed,yyyy-mm-dd hh34:mi:ss) from dba_tables where table_name in

(

select p.name

from sys.obj$ d, sys.dependency$ dep, sys.obj$ p

where d.obj# = dep.d_obj# and p.obj# = dep.p_obj#

start with d.name=EMP

connect by prior dep.p_obj#=dep.d_obj#)

order by last_analyzed desc;

比较典型的一个用户实例：

select to_char(last_analyzed,yyyy-mm-dd hh34:mi:ss) from dba_tables where table_name=XXXXX;

--2014-11-25 16:52:50

<=============gathering statistics in the issue time 

2014-11-25 16:52:52 16620 c34q5c8gf6kum library cache lock

2014-11-25 16:52:52 16643 c34q5c8gf6kum library cache lock

<======The issue starts from 16:52:52 while statistics was gathered at 16:52:50

3)错误的语句解析（failed parse）

这是通常很难注意到的一个问题，因为被解析的语句往往在AWR中找不到（因为没有通过parse），要注意查看AWR中的“failed parse elapsed time”

Event Waits Time(s) Avg wait (ms) % DB time Wait Class

library cache lock 6,714,208 363,093 54 67.14 Concurrency

library cache: mutex X 11,977,886 99,050 8 18.31 Concurrency

DB CPU   38,971   7.21  

db file sequential read 350,069 2,465 7 0.46 User I/O

log file sync 217,673 1,969 9 0.36 Commit

Statistic Name Time (s) % of DB Time

sql execute elapsed time 537,418.09 99.37

parse time elapsed 467,101.99 86.37

failed parse elapsed time 460,663.79 85.18 <===============failed parse elapsed time was high. That means the issue was caused by parse failed.

看完上述内容，你们掌握常见的library cache lock产生的原因是什么的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注行业资讯频道，感谢各位的阅读！