分析Oracle Mutex等待事件
原创
©著作权归作者所有:来自51CTO博客作者maclean_007的原创作品,请联系作者获取转载授权,否则将追究法律责任
Troubleshooting or analyzing waits on mutex events, i.e. ones that start with "cursor:", are very similar to each other and below are some guidelines.
There are many bugs related to problems using mutexes in Oracle 10g, the first version where mutexes were introduced. If you are seeing excessive waits on mutexes, a quick fix may be to go back to the library cache latching mechanism. To do this, you can set the parameter _kks_use_mutex_pin=false.
Note: For mutex related waits, v$session.blocking_session is not populated in 10.2. However, v$session.blocking_session is populated in 11g R1.
To do further analysis, the P2RAW column in v$session gives the blocking session, i.e. the holder SID of the mutex in the upper 8 bytes. It is in hex so it needs to be converted in decimal. The following query provides the blockers with the most sessions blocked behind it as well as converts the P2RAW to an usable SID:
select p2raw, to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX') sid, count(1) sessions_waiting
from v$session
where event = 'cursor: pin S wait on X'
group by p2raw, to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX');
P2RAW
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
关于Oracle 数据库等待事件library cache: mutex X
关于Oracle 数据库等待事件library cache: mutex X
library cache mutex X oracle perl css