加载中…library cache: mutex X 等待事件
(2012-05-17 10:50:51)
标签：
原创 |
分类： oracle随笔 |
我们可以看到这里有大量的library cache: mutex X 。(在这里我们还可以看到一个PL/SQL LOCK
TIMER的等待事件，这是由于执行代码里调用了DBMS_LOCK.SLEEP，之所以要再代码里认为的中断，是由于在oracle11g1R里，如果频繁调用DBMS_SCHEDULER创建job会莫名奇妙的卡主,Developer不得以只能做判断然后人为中断job,进行等待。不等不说oracle11gR1的bug真的好多啊)
我们先来看看 library cache: mutex X 。 是个什么东西
The library cache mutex is
acquired for similar purposes that the library cache latches were
acquired in prior versions of Oracle. In 10g, mutexes were
introduced for certain operations in the library
cache. Starting with 11g, the library cache
latches were replaced by mutexes, hence this new wait
event. -----摘自metalink [ID
727400.1]
GV$MUTEX_SLEEP (or V$MUTEX_SLEEPS for non-RAC)
and GV$MUTEX_SLEEP_HISTORY (or V$MUTEX_SLEEP_HISTORY for non-RAC)
These views track the instance wide usage of mutexes since instance startup. Since these views show values that are
total values since startup, they are most meaningful when you
obtain the difference in values during a short time interval when
there was problem. The easiest way to see this
information is through an AWR or statspack report in the "Mutex
Sleep Summary" section.
Finding Blockers:
You can see the P2 value in
V$MUTEX_SLEEP_HISTORY along with the
"REQUESTING_SESSION". You may find a pattern
where one session commonly blocks others - this would lead you to
obtain a SQL trace of the blocking session to try and get more
information about what its doing.
按照oracle的说法我们只要取得P2的值和REQUESTING_SESSION关联就可以查到罪魁祸首。可是悲剧的是，我发现
P2值都是0。（顿时一阵郁闷）
正在郁闷时，突然Bug 10145558 - Selects on library cache V$/X$ views cause "library cache: mutex X" waits [ID 10145558.8] 吸引了我。难道是个Bug？
于是立马查看session执行的sql：
我们先来看看 library cache: mutex X 。
同时oracle也给出了追踪的办法。
Systemwide Waits:
At a systemwide level, there are two views which will help diagnose this wait:GV$MUTEX_SLEEP (or V$MUTEX_SLEEPS for non-RAC)
and GV$MUTEX_SLEEP_HISTORY (or V$MUTEX_SLEEP_HISTORY for non-RAC)
These views track the instance wide usage of mutexes since instance startup.
Finding Blockers:
按照oracle的说法我们只要取得P2的值和REQUESTING_SESSION关联就可以查到罪魁祸首。可是悲剧的是，我发现
P2值都是0。（顿时一阵郁闷）
正在郁闷时，突然Bug 10145558 - Selects on library cache V$/X$ views cause "library cache: mutex X" waits [ID 10145558.8] 吸引了我。难道是个Bug？
于是立马查看session执行的sql：
INSERT INTO
ADWMON_SESSIONS (INST_ID, SID, SERIAL#, USERNAME, LOGON_TIME,
STATUS, SQL_ID, PLAN_HASH_VALUE, MACHINE, LAST_CALL_ET, SQL_TEXT,
START_TIME, UNDO_SPACE, TEMP_SPACE, UNDO_TBS, TEMP_TBS, ERROR_MSG,
SUSPEND_TIME, TIMEOUT, REASON, OBJECT_TYPE, OBJECT_OWNER,
OBJECT_NAME, SUBOBJECT_NAME, TABLESPACE_NAME) SELECT S.INST_ID,
S.SID, S.SERIAL#, S.USERNAME, S.LOGON_TIME, R.STATUS, Q.SQL_ID,
Q.PLAN_HASH_VALUE, S.MACHINE, S.LAST_CALL_ET, Q.SQL_TEXT, SYSDATE,
T.USED_UBLK * :B8 UNDO_SPACE, SUM (U.BLOCKS) * :B12 TEMP_SPACE, :B7
, :B6 , SUBSTR (R.ERROR_MSG, 1, 2000) ERROR_MSG, R.SUSPEND_TIME,
R.TIMEOUT, 'SUSPENDED SESSION MONITORING', :B5 , :B4 , :B2 , :B1 ,
:B3
FROM GV$SESSION S, GV$SQL Q, GV$TRANSACTION T, GV$SORT_USAGE
U, DBA_RESUMABLE R WHERE S.INST_ID = Q.INST_ID(+) AND S.SQL_ID =
Q.SQL_ID(+) AND S.INST_ID = R.INSTANCE_ID AND S.INST_ID =
T.INST_ID(+) AND S.SADDR = T.SES_ADDR(+) AND S.INST_ID =
U.INST_ID(+) AND S.SADDR = U.SESSION_ADDR(+) AND S.SID =
R.SESSION_ID AND S.SID = :B11 AND S.SE…
我们可以发现这个sql要查询一系列的system table.
我们可以发现这个sql要查询一系列的system table.
我们也来访问下这些表
bu5705@ADW1U>select count(1)
from
----------
Elapsed:
00:00:33.28
发现区区86条数据竟然用了33秒。而且执行该语句的session也出现大量的 library cache: mutex
X
http://s7/middle/7c0ae04bgc00f53be6436&690cache:
P2的值依然是0.
到这里我们应该基本确定是个Bug了。
于是发ticket。要求检查数据库。
喜欢
0
赠金笔