Library Cache中Latch与Mutex Event(4)—Cursor Pin :S

在上一篇文章《select * from dual引发的性能问题》引发了严重SQL超时的Cursor Pin :S等待事件,通过模拟该故障现象,简单记录一下该问题的处理思路:

官方解释:
A session waits for “cursor: pin S” when it wants a specific mutex in S (share) mode on a specific cursor and there is no concurrent X holder but it could not acquire
that mutex immediately. This may seem a little strange as one might question why there should be any form of wait to get a mutex which has no session holding it in an
incompatible mode. The reason for the wait is that in order to acquire the mutex in S mode (or release it) the session has to increment (or decrement) the mutex reference
count and this requires an exclusive atomic update to the mutex structure itself. If there are concurrent sessions trying to make such an update to the mutex then only
one session can actually increment (or decrement) the reference count at a time. A wait on “cursor: pin S” thus occurs if a session cannot make that atomic change
immediately due to other concurrent requests. Mutexes are local to the current instance in RAC environments.

Parameter Description
P1                 idn — SQL HASH_VALUE
P2                value
P3                where (where|sleeps in 10)

模拟故障现象:

启动两个会话,进行大量SQL的执行(软解析、软软解析)

Cursor_pin_s-1

产生Cursor Pin:S等待事件

Cursor_pin_s-2

查看P1,P2,P3的值,通过P1定位到具体的执行SQL,执行多次,可以看到P1 Value不变,P1=SQL_HASH_VALUE。

Cursor_pin_s-3-3

P2=SESSION_SID,可以看到瞬时值,谁当时在持有Mutex

Cursor_pin_s-4

 

通过P3可以定位到具体的函数调用:

Cursor_pin_s-5

当生产系统出现大量此等待时:

1.建议首先怀疑有SQL执行过于频繁的问题,业务行为需求与数据库相同SQL执行次数是否一致

2.如遇到进程Hung死,未及时释放相关Mutex,需要执行systemstate dump后Kill Mutex 持有者,尽快恢复系统正常服务后,根据dump Trace文件进行根因分析。