select * from dual引发的性能问题

Oracle 10gR2后,Cursor Mutex代替了Library Cache Pin的功能,其好处在于资源持有更加“轻量级”,资源结构更小,获取一个Mutex的内部指令更少,并且更重要的是:原有的Latch以一种结构的方式存在,一个Latch可能需要控制管理多个对象的并发操作,对象并发的可能性大大增大;Mutex则不然,它对应对象,每个对象中有着独立的Mutex结构,减少了不同对象对应同一Latch管理下并发争用的可能性

官方解释如下:

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.

真实案例:

曾经公司一套核心数据库业务出现了SQL严重超时的现象,导致前台交易严重超时:

CUSS-1-1-1

生成该性能出现瓶颈严重时的AWR,发现SQL执行速度还算理想,理论上不存在要SQL优化的地步,也就是说本次后台SQL超时问题不是SQL本身问题导致。

查询该性能异常时间段等待时间,发现了一个可疑问题,数据库中出现了Cursor Pin:S等待事件,引发此问题的SQL为select * from dual语句

cuss-1-2

Grid Control信息如下,全天内Cusor Pin:S争用频率还是比较多的

Cuss-1-3

联系应用人员与中间件团队确认该SQL:SELECT * FROM DUAL为应用每次探测连接是否可用执行的SQL,先执行此SQL确认连接可用,再次执行相关具体应用业务SQL。正式因为频繁的执行了该Cursor,导致Cusor上Mutex产生争用,每个Cursor上存在reference count,软解析时是要持有S Mode的Mutex资源,持有资源也就意味着需要更新reference count,更新reference count操作为串行操作,并发高的情况下出现Cursor Pin :S等待。我提供了2种应对方案:

方案(1):将不同应用服务器SQL加HINT区别于不同的Cursor对象,分散Mutex争用

1-3台服务器 执行select /*+ A */ * from dual;

4-6台服务器执行select /*+ B */ * from dual;

7-10台服务器执行select /*+ C */ * from dual;
方案(2)取消或减少应用服务器中的探测机制,也就是select * from dual的频繁执行

应用开发负责人对此结论并不认可,认为SELECT * FROM DUAL一条简单的SQL语句不应该会引起数据这么大动静。虽然没有有力的证据反驳,但始终无法信服我的结论。。无奈之举,我们请来了一位业界专家,同样的观点,同样的结论,提供了同样的方案,但是运用了卓越的表达方式(没有优秀的表达,技术原理信息本身会被折扣后传递也无法说服充满自信对方,还需修炼),成功说服了应用人员,采用了方案2的处理方法。当晚更改中间件参数后,SQL超时现象马上消失了,一切回归于正常。