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

72次阅读
没有评论

共计 3631 个字符,预计需要花费 10 分钟才能阅读完成。

常见的 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 产生的原因是什么的方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注丸趣 TV 行业资讯频道,感谢各位的阅读!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-20发表,共计3631字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)