cursor: pin S wait on X 分析

从oracle10g(10.2.0.2)开始 ，开始启用mutex来替换以前的library cache bin latch，这是一种os级更低层的轻量级锁，但是也不是那么完美，有时会出现events  cursor: pin S wait on X
通过隐藏参数来禁用mutex
alter system set _kks_use_mutex_pin=false scope=spfile;
shutdown immediate ;
startup;
Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
cursor: pin S wait on X	1,771,367	19,072	11	55.1	Concurrency
CPU time		15,750		45.5
log file parallel write	2,240	7	3	.0	System I/O
control file parallel write	2,078	6	3	.0	System I/O
log file sync	1,508	4	3	.0	Commit

cursor: pin SA 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
-------extracts
每个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的并行太多,减少软解析
Some fix-ups
1. Some backgrounds
- In previous versions of Oracle, library cache pin is protected by "library cache pin latch".
- But in recent versions of Oracle(I believe it's 10.2.0.2), library cache pin for the cursor LCO is protected by mutext.
- Mutex is allocated per LCO, so it enables fine-grained access control.
2. "cursor: pin S wait on X" wait event is mostly related to mutex and hard parse.
- When a process hard parses the SQL statement, it should acquire exclusive library cache pin for the corresponding LCO.
- This means that the process acquires the mutex in exclusive mode.
- Another process which also executes the same query needs to acquire the mutex but it's being blocked by preceding process. The wait event is "cursor: pin S wait on X".
Some bugs would make the contention worse as many metalink notes describe.
3. Cursor mutex is a replacement of library cache pin latch for cursor, not library cache latch.
4. As of 11g, library cache latch is also replaced with mutex. Now, each library cache bucket is protected by independent mutexes, which enables fine-grained access control.