最近安装了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/
转载于:https://blog.51cto.com/2874575/1054950