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 - 64bit Production


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

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.

  • Thanks Justin, AWR was actually during 1hr period so,we thought if this is one of the top5 events in 1hr period then it may occur many times during whole day.. we just got AWR for 5hr window and we dont see this as top5 wait event. – Sree Feb 14 '11 at 20:45

Check Oracle release notes for 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.


I had this problem after migrating to 10g R2 11g R2 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 to

Hope this helps

  • 1
    -1 Sorry, this does not sound like good general advice to me, based on the problem "evidence" posted. – Jack Douglas Sep 26 '11 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.