oracle cursor 泄露,学习笔记:Oracle等待事件 cursor: pin S事件详细解释与产生原因

天萃荷净

深入研究Oracle等待事件之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.

Oracle10g中引用的mutexes机制一定程度的替代了library cache pin，其结构更简单，get&set的原子操作更快捷。

它相当于，每个child cursor下面都有一个mutexes这样的简单内存结构，当有session要执行该SQL而需要pin cursor操作的时候，session只需要以shared模式set这个内存位+1，表示session获得该mutex的shared mode lock.可以有很多session同时具有这个mutex的shared mode lock；但在同一时间，只能有一个session在操作这个mutext +1或者-1。+1 -1的操作是排它性的原子操作。如果因为session并行太多，而导致某个session在等待其他session的mutext +1/-1操作,则该session要等待cursor: pin S等待事件。

当看到系统有很多session等待cursor: pin S事件的时候，要么是CPU不够快，要么是某个SQL的并行执行次数太多了而导致在child cursor上的mutex操作争用。

如果是Capacity的问题，则可以升级硬件。如果是因为SQL的并行太多，则要么想办法降低该SQL执行次数，要么将该SQL复制成N个其它的SQL。

select /*SQL 1*/object_name from t where object_id=?

select /*SQL 2*/object_name from t where object_id=?

select /*SQL …*/object_name from t where object_id=?

select /*SQL N*/object_name from t where object_id=?

这样就有了N个SQL Cursor,N个Mutex内存结构，就将争用分散开来，类似partition的作用了

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心

http://www.oracleplus.net

本文由大师惜分飞原创分享，转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle等待事件 cursor: pin S事件详细解释与产生原因