Wait event cursor: pin S wait on X
885729Aug 31 2011 — edited Sep 1 2011I'm tuning a data warehouse and my number 2 wait event yesterday was:
cursor: pin S wait on X
It is usually in the top 5.
I'm using Oracle 11.2.0.2.0
The library cache hit ratio is usually in the high 90s.
We are using automatic memory management. I've been monitoring the shared pool size and it is always 9 Gig.
We have over a thousand reports run from Business Objects every day.
Data loads are mainly from Informatica, which uses bind variables, but there are also some older scripts.
Cursor sharing is exact.
Other than this event, the top 5 tends to be dominated by physical reads, which is what you might expect for a data warehouse.
This event occurs throughout the day.
The average wait time is 2.6 seconds. This makes it difficult to analyze the wait when it occurs. Wait time peaks at about 3000 seconds per hour.
I don't want to disable mutex pinning because I would probably get a worse problem with the old library cache pins.
When I google this wait event, I get information like:
"Another process which also executes the same query needs to acquire the mutex but it's being blocked by preceding process."
This is all very well, but what I want to know is:
1)What is the root cause of the waits?
2)How do I fix it?
3)What long term strategy should I follow to reduce this wait event?