Library Cache中Latch与Mutex Event(3)—Library Cache Mutex: x

一. 什么是Library cache:Mutex X

官方解释:
Library cache mutexes are similar to library cache operations in earlier versions except they are now implemented using mutexes. In all these cases, waits for these resources occurs when 2 (or more) sessions are working with
the same cursors simultaneously. When one session takes and holds a resource required by another, the second session will wait and will wait on one of these events.library cache: mutex X which means that sessions are waiting
to get a library cache mutex in eXclusive mode for one or more cursors.

Parameter Description
P1 “idn” = Unique Mutex Identifier
P2 Mutex “value” = in order to find the mutex value, read note 1298015.1
upper 8-bits which is the session ID (SID) of the Mutex owner
lower 8-bits is the Mutex reference count which is used for concurrency.
P3 “where” = location in code (internal identifier) where mutex is being waited for

个人理解:Library cache:Mutex X保护Library Cache Object Hanles Object(10.2.0.5后Handle上的并发控制由Mutex所体态),当SQL解析过程中首先要持有Library Cache Mutex扫描HASH_BUCKET与HASH_TABLE中Libray Cache Objects Handle首先已S模式进行扫描Parent Curosr Handle,当无法匹配任何游标句柄则需要硬解析一对父子游标,那么此时会持有Library cache:Mutex X,硬解析扫描HASH_BUCKET与HASH_TABLE均以Library cache:Mutex X持有.保护与控制HASH_BUCKET与HASH_TABLE链表扫描时的内存结构一致性,SCAN动作均持有Library Cache Mutex :S/X,软解析先持有S,硬解析持有X。

二.实验模拟:

会话1:构造并执行大量硬解析:

librart cache -3-1 会话2:

利用ORADEBUG,将硬解析进程SUSPEND,构造出该游标持有Library cache:Mutex X不释放的现象

library cache 3-2

会话3:再次执行相同硬解析游标,会话4查询当前数据库EVENT,出现Library cache:Mutex X等待:

library cache 3-3

ORACLE 官方提供一个脚本来定位目前数据库中的等待阻塞关系:

select s.inst_id as inst,s.sid as blocked_sid, s.username as blocked_user,sa.sql_id as blocked_sql_id,trunc(s.p2/4294967296) as blocking_sid, b.username as blocking_user,
cb.sql_id as blocking_sql_id from gv$session s join gv$sqlarea sa on sa.hash_value = s.p1
join gv$session b on trunc(s.p2/4294967296)=b.sid and s.inst_id=b.inst_id join gv$sqlarea sa2 on b.sql_id=sa2.sql_id  where s.event=’library cache: mutex X’;

library cache -3-4

当生产系统中出现大量该等待事件,首要怀疑该数据库中是否存在硬解析过多原因,该等待多出现于数据库解析异常时,例如解析游标进行HUNG死,长时间持有该资源不释放,其次需要关注Shared pool中是否存在空间不足,操作系统SWAP PAGE,SGA动态调整等因素,建议在出现此性能问题时执行oradebug (RAC: -g all:所有节点 ) dump systemstate 266操作,可完整呈现该数据库阻塞关系与资源持有异常情况。