mutex是11g中采用的轻量级锁,替代latch(更轻量,更细粒度)。其中mutex X表示eXclusive模式,如果是mutex s则是共享模式。引起library cache mutex冲突的原因很多。
[@more@]
一、 Library cache: mutex等待事件产生常见原因:
l os资源不足(cpu、内存)
l sga设置不合理,shared_pool不足,或动态调整导致hard parse
l hot object contention
l hard parse
l sql high version count 由于子游标太多,扫描时会形成锁
l library cache object失效导致重编译
l bug
二、 诊断方法
1. 首先检查os资源如cpu内存是否存在不足
2. 其次,检查是否存在Hot object contention
方法一:通过ash报告
Top sql with top events最活跃的sql及其top wait events
Top sql with top row sources 最活跃的sql及其详细运行计划信息,可以凭此定位sql中
哪一部分最消耗时间。
Top event p1/p2/p3 values显示wait event详细参数
方法二:通过sql
通过sql发现hot objects:
select KGLNAOBJ, KGLNAOWN, KGLHDNSP, KGLOBTYP, KGLOBT23, KGLOBT24
from x$kglob where KGLOBT23 > 1000000 or KGLOBT24 > 1000000
order by KGLOBT24;
也可以用以下sql找到相应sql,hot objects,以及每句sql等待事件占比
select sw.p1,s.sql_id, count(*),(ratio_to_report(count(*)) over ())*100 pct
from v$session s,v$session_wait sw
where s.event like '%mutex%'and s.sid=sw.sid
group by sw.p1,s.sql_id
order by count(*) desc;
select sql_id,sql_text from v$sql where sql_id=…;
如果要查看历史信息,可以从历史表查询:
select * from (
select p1, sql_id,
count(*),
(ratio_to_report(count(*)) over ())*100 pct
from dba_hist_active_sess_history
where event='library cache: mutex X'
and snap_id between and
and dbid =
group by p1, sql_id
order by count(*) desc)
where rownum <= 10;
然后根据p1查热点对象
select KGLNAOBJ, KGLNAOWN, KGLHDNSP, KGLOBTYP
from x$kglob where KGLNAHSH= ;
3. 如果不存在hot object contention,则检查AWR报告(和baseline做比较)
1) Hard parse
检查awr的load profile中的hard parse再检查sql orderd by pase call)
检查是否发生了shared_pool size的调整
2) high version count高
检查sql orderd by version count是否正常
cursor_sharing参数是否设成了SIMILAR(建议用FORCE取代)
3) Library cache object失效
检查 Library Cache Activity的invalidations. 如果失效很多,检查是否执行了ddl操作
check ddl's performed during the time such as truncate, drop, grants, dbms_stats
4. mutex sleeps
检查AWR中'Mutex Sleep Summary',主要关注location和times,
如果Sleeps次数多但time少,则影响不大。
也可通过sql查询(注意是instance启动以来的,而不是某个时段内)
select to_char(sysdate, 'HH:MI:SS') time, KGLNAHSH hash, sum(sleeps) sleeps,location,MUTEX_TYPE
, substr(KGLNAOBJ,1,40) object
from x$kglob , v$mutex_sleep --也可查历史表v$mutex_sleep_history
where kglnahsh=mutex_identifier
group by KGLNAOBJ,KGLNAHSH,location,MUTEX_TYPE
order by sleeps
/
三、 案例
在enterprise manager中发现大量library cache mutex X等待事件,由create table语句引起:
通过ASH报告,发现这些语句在取sequence时产生该等待事件:
我们发现,有十几个进程同时调用该sql,每个sql生成几千万数据,由于select中访问了某个sequence,导致竞争。于是我们将sequence的cache放大,以减少竞争。
参考metalink文档:
Troubleshooting 'library cache: mutex X' waits. [ID 1357946.1]
WAITEVENT: "library cache: mutex X" [ID 727400.1]
Troubleshooting: Waits for Mutex Type Events (Doc ID 1377998.1)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18474/viewspace-1060805/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18474/viewspace-1060805/