几种常见的library cache lock产生的原因

转载

jsj_007 2021-06-24 08:02:10 博主文章分类：ORACLE

常见的library cache lock产生的原因在《高级OWI与Oracle性能调查》这本书和下面这个文档中有一般性的描述： 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 hh24:mi:ss') from dba_objects where object_name='EMP';

select table_name,to_char(last_analyzed,'yyyy-mm-dd hh24: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 hh24: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 hh24: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 hh24: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.

详细请参考： 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)

namespace：

select to_char(a.sp,'xxxxxxxxxxxx') subp3,a.sp ,a.sd from (select distinct KGLHDNSP sp,KGLHDNSD sd from x$kglob) a
ORDER BY 1 ASC NULLS LAST;

subp3	sp	sd
		SQL AREA
1	1	TABLE/PROCEDURE
2	2	BODY
3	3	TRIGGER
4	4	INDEX
5	5	CLUSTER
a	10	QUEUE
12	18	PUB SUB INTERNAL INFORMATION
15	21	APP CONTEXT
17	23	RULESET
18	24	RESOURCE MANAGER
19	25	XML SCHEMA
1c	28	SUBSCRIPTION
26	38	RULE EVALUATION CONTEXT
2d	45	MULTI-VERSION OBJECT FOR TABLE
30	48	MULTI-VERSION OBJECT FOR INDEX
33	51	SCHEDULER GLOBAL ATTRIBUTE
35	53	TEMPORARY TABLE
37	55	TEMPORARY INDEX
40	64	EDITION
45	69	DBLINK
49	73	SCHEMA
4b	75	SQL AREA STATS
4f	79	ACCOUNT_STATUS
52	82	SQL AREA BUILD
5d	93	AUDIT POLICY
68	104	OPTIMIZER DIRECTIVE OWNER
70	112	USER PRIVILEGE
71	113	GTT SESSION PRIVATE STATS
73	115	KZP Exempt Access Policy list
7f	127	Last_Successful_Logon_Time
82	130	APP STATE
88	136	CMP
89	137	OPTIMIZER EXPRESSION HEADER
8a	138	OPTIMIZER EXPRESSION OBJECT
8c	140	INMEMORY SERVICE METADATA