cursor: pin S wait on X in 11g

Question

This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution. Any advice will be greatly appreciated..

DB Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

AWR:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                            3,887          95.0
db file sequential read             207,505         125      1    3.1 User I/O
direct path read                     33,793          69      2    1.7 User I/O
cursor: pin S wait on X                   5          43   8650    1.1 Concurrenc
db file scattered read               34,229          39      1     .9 User I/O

Answer 1

Why does a wait event that occurred 5 times accounts for 1.1% of your database wait time concern you as a potential threat? Is there some additional information that leads you to believe that this is a threat? Obviously, something has to be in your top 5 wait events. Is this event consistently one of the top wait events? Or did it just appear on one AWR?

The particular wait generally occurs when one session is trying to pin a cursor while another session is parsing it. Potentially, some of the 95% of the CPU related wait events could be the result of parsing (though you'd want to look at the number of hard parses elsewhere in the AWR). If you have an OLTP system, you should really only be doing any quantity of hard parses immediately after a reboot-- everything else should be using prepared statements and, at most, doing a soft parse. If you've got dynamic SQL somewhere in the system, removing that may help though it may also be a rather drastic step to save a few seconds.

Answer 2

Check Oracle release notes for 11.2.0.3. I recall there were some issues solved - related to this wait event. There is also a note on Metalink describing this particular event. It can be something 11g related.

On the other hand the reason can be much simpler. You application does too many hard parses.

Answer 3

I had this problem after migrating to 10g R2 10.2.0.4 11g R2 11.2.0.2. A query written in the wrong way with several "AND" in the same column (INLIST).

The solution I found: modify the query ->

Change the parameter "optimizer_features_enable" of 11.2.0.2 to 10.2.0.4.

Hope this helps