This content has been marked as final. Show 6 replies
Ogan, I have observed this in few 10g database instances. The reason for this wait could be excessive parsing in the database. Btw did you look at the blocking session and what blocking session was doing when another database session was waiting on this wait event.
Check if the SQL area is being reloaded frequently then you can think of setting session_cached_cursors parameter. The ultimate solution will be to reduce the overall parsing in the database. There are few bugs related to mutex pins that you may like to check with Oracle Support.
"cursor: Pin S wait on X" wait event, maybe mutex problem.
from 10.2.0.2, oracle use mutex instead of library cache latch, in case "_kks_use_mutex_pin"=true.
so, you change that parameter value from true to false.
SQL>alter system set "_kks_use_mutex_pin"=false scope=spfile; SQL>shutdown immediate SQL>startup
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.
Dion Cho - Oracle Performance Storyteller