一 cursor: pin S
1.等待事件说明
1.1 官方文档解释
A session waits on this event when it wants to update a shared mutex pin and another session is currently in the process of updating a shared mutex pin for the same cursor object. This wait event should rarely be seen because a shared mutex pin update is very fast. 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
1.2 cursor pin S原因
等待事件cursor:pin * 用于针对游标的pin 操作。
Cursor Pin的mutex（不是cursor pin mutex）是在Library Cache Object 之内创建的动态对象。在竞争之下，通过x$mutex_sleep_history可以找到mutex的地址。
cursor: pin S 试图以S 模式Pin 某个Cursor，但是该Cursor正在被Pining ，也就是“in flux”，必须等待这个过程完成，才能被共享Pin。
Mutexes were introduced in Oracle 10.2. 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.
系统本身CPU不足，许多cursor pin S有许多不同的p1。
2.等待事件模拟以及排查
2.1 创建测试表
create table t (id number);
2.2 测试
session1:
select sid from v$mystat where rownum<2; declare a number; begin for i in 1..100000 loop execute immediate 'select count(*) from t where n=:v1' into a using i ; end loop; end; /
session2：
select sid from v$mystat where rownum<2; declare a number; begin for i in 1..100000 loop execute immediate 'select count(*) from t where n=:v1' into a using i ; end loop; end; /
session3查询：
col event for a30 col p1 for 999999999999999999999 col p2 for 999999999999999999999 col p3 for 999999999999999999999 col sid for 999 select event,p1,p1raw,p2,p2raw,p3,sid,blocking_session bs from v$session where sid in (45,40) EVENT P1 P1RAW P2 P2RAW P3 SID BS ------------------------------ ------------------ ---------------- --------------------- ---------------- ---------------------- ---- --- cursor: pin S 1664038472 00000000632F3648 193273528320 0000002D00000000 12884901888 40 cursor: pin S 1664038472 00000000632F3648 171798691841 0000002800000001 12884901888 45
可以看到两个会话都在竞争相同的SQL子游标，可以根据p1查询相关SQL，根据p2前2bytes找到并发竞争会话。关于p1 p2 p3更详细以及情况可以参考Mos文档： WAITEVENT: "cursor: pin S" Reference Note (Doc ID 1310764.1)
P1:
select sql_text,sql_id from v$sqlarea where hash_value=1664038472;
P2:
SELECT decode(trunc(&&P2/4294967296),
0,trunc(&&P2/65536),
trunc(&&P2/4294967296)) SID_HOLDING_MUTEX
FROM dual;
select to_number('2d','xx') from dual;
TO_NUMBER('2D','XX')
----------------------------------------
45
select to_number('28','xx') from dual;
TO_NUMBER('28','XX')
----------------------------------------
40
P3（32bit 10.2版本低位可能会溢出到高位，可能会计算出错误的location_id值）:
可以根据P3计算x$mutex_sleep中location_id：
SELECT decode(trunc(&P3/4294967296),
0,trunc(&P3/65536),
trunc(&P3/4294967296)) LOCATION_ID
FROM dual;
Use the LOCATION_ID returned above in this SQL:
SELECT MUTEX_TYPE, LOCATION
FROM x$mutex_sleep
WHERE mutex_type like 'Cursor Pin%'
and location_id=&LOCATION_ID;
MUTEX_TYPE LOCATION
-------------------------------- ----------------------------------------
Cursor Pin kksfbc [KKSCHLFSP2]
kkksfbc:kernel compile shared objects (cursor) find bound cursor
3.解决办法
3.1 针对hot SQL使用sql hint
由于SQL的hash value是根据文本转换为ASCII码计算hash值，可以使用如下办法生成多版本，降低单个mutex的高并发访问造成的cursor pin S For any identified "hot" SQLs one can reduce the concurrency on specific cursors by replacing the one SQL with some variants which are executed by different sessions.
eg: Consider "select <COLNAME1> from <TABLENAME> where <COLNAME2>=:1" is a very hot SQL statement then if clients can be put into groupings with: some using "select /*A*/ <COLNAME1> from <TABLENAME> where <COLNAME2>=:1", some using "select /*B*/ <COLNAME1> from <TABLENAME> where <COLNAME2>=:1", some using "select /*C*/ <COLNAME1> from <TABLENAME> where <COLNAME2>=:1", etc.. then the concurrency against any one of the SQLs can be reduced.
