常見(jiàn)的library cache lock產(chǎn)生的原因是什么，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

創(chuàng)新互聯(lián)公司是一家專注網(wǎng)站建設(shè)、網(wǎng)絡(luò)營(yíng)銷策劃、重慶小程序開(kāi)發(fā)、電子商務(wù)建設(shè)、網(wǎng)絡(luò)推廣、移動(dòng)互聯(lián)開(kāi)發(fā)、研究、服務(wù)為一體的技術(shù)型公司。公司成立十年以來(lái)，已經(jīng)為上1000家辦公空間設(shè)計(jì)各業(yè)的企業(yè)公司提供互聯(lián)網(wǎng)服務(wù)。現(xiàn)在，服務(wù)的上1000家客戶與我們一路同行，見(jiàn)證我們的成長(zhǎng)；未來(lái)，我們一起分享成功的喜悅。

常見(jiàn)的library cache lock產(chǎn)生的原因

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

一般可以理解的是alter table或者alter package/procedure會(huì)以X模式持有l(wèi)ibrary cache lock，造成阻塞。
但是常見(jiàn)的問(wèn)題還有以下幾種原因：

1)錯(cuò)誤的用戶名密碼：

一般需要通過(guò)ASH或者SSD/hang analyze去獲取p3進(jìn)行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說(shuō)明library cache lock是在account上，可能是用錯(cuò)誤的用戶名密碼登錄，或者是當(dāng)時(shí)正有人alter user(這種幾率極低）。

可以通過(guò)以下SQL去確認(rèn)錯(cuò)誤的用戶名密碼登錄：
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;

當(dāng)然必須確保audit 打開(kāi)，并且有audit CREATE SESSION動(dòng)作

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)正在執(zhí)行搜集統(tǒng)計(jì)信息，這是大家往往會(huì)忽略的，一般會(huì)看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的對(duì)象，因?yàn)閛racle對(duì)象是相互關(guān)聯(lián)的，一個(gè)對(duì)象失效會(huì)導(dǎo)致一串失效。
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;

比較典型的一個(gè)用戶實(shí)例：
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)錯(cuò)誤的語(yǔ)句解析（failed parse）
這是通常很難注意到的一個(gè)問(wèn)題，因?yàn)楸唤馕龅恼Z(yǔ)句往往在AWR中找不到（因?yàn)闆](méi)有通過(guò)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. 

看完上述內(nèi)容，你們掌握常見(jiàn)的library cache lock產(chǎn)生的原因是什么的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！