Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

3 Answers

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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