EM12C监控遇到 ‘cursor: pin S wait on X’ waits.

     最近安装了EM12C，对上有ERP应用的所有库进行监控。EM12C相对之前的grid control还是改进比较大的。安装也蛮简单。

     今早一来发现一库有大量的并发，如下图，图1-1：

 

点上图，图1-1中深红色区域（并发区），进去，得到下图1-2（里面有引起并发的等待事件）：

 

由此很快能定位引起cursor:pin S wait on X等待事件的原因了，是某一个JOB调用引起的，接着查看alert日志，发现里面有很多报错，是因为前面JOB里面调用的procedure里面有用到dblink，而此dblink链接无效导致报错。

于是停用对应的JOB，情况立马好转。

 

下面简单介绍下cursor pin s wait on X 等待事件及排查方法：

doc描述：
cursor: pin S wait on X
A cursor wait is associated with parsing in some form. A session may wait for this event when it is trying to get a mutex pin in Share mode but another session is holding the mutex pin on the same cursor object in exclusive.  Frequently, waits for ‘Cursor: pin S wait on X’ is a symptom and not the cause.  There may be underlying tuning requirements or known issues.

Wait Time: Microseconds

Parameter Description 
P1 Hash value of cursor 
P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0) 
P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps

 

在出现cursor pin s wait on X 等待事件时，可用以下语句查找是执行哪个用户下哪个module导致的：

select a.*,s.sql_text,s.parsing_schema_name /*解析的SQL的用户*/,s.module,s.action 
     from v$sql s , 
       ( 
       select sid,event,wait_class,p1,p2raw,to_number(substr(p2raw,1,4),'xxxx') sid_hold_mutex_x  
       from v$session_wait where event like 'cursor%' 
       ) a 
     where s.HASH_VALUE=a.p1 

详细troubleshot cursor pin S wait on X wait events 可参考：

http://www.dbaspace.com/2012/05/31/troubleshooting-cursor-pin-s-wait-on-x-waits-id-1349387-1/