This discussion is archived
6 Replies Latest reply: Nov 20, 2012 12:33 AM by Jonathan Lewis RSS

high waits on cursor: pin S wait on X

Moazzam Pro
Currently Being Moderated
We are using Oracle 10g R2 on Linux platform.

In AWR report, it is observed that waits for cursor: pin S wait on X are very high. Followings are the top 5 wait events:
    Event                                        Waits                 Time -outs           Total Wait Time (s)         Avg wait (ms)     Waits /txn
cursor: pin S wait on X                 51,998,882                     99.99                       563,103                  11            59.34 
library cache lock                         335,430                     71.92                       119,443                 356             0.38 
db file sequential read                  6,915,111                      0.00                        27,349                   4             7.89 
enq: TX - row lock contention               27,015                     99.20                        13,159                 487             0.03 
log file sync                              806,084                      0.03                        10,098                  13             0.92 
Followings are the OS statistics:

Operating System Statistics
BUSY_TIME  --   4,248,586 
IDLE_TIME   --   7,286,246 
IOWAIT_TIME  -- 4,552,364 
In Time Model statistics, followings are the top 5 rows:
Statistic Name                                  Time (s)      % of DB Time 
parse time elapsed                            504,697.67             66.18 
sql execute elapsed time                      323,904.89             42.48 
hard parse elapsed time                        79,544.87             10.43 
hard parse (sharing criteria) elapsed time     16,660.57              2.18 
hard parse (bind mismatch) elapsed time        16,529.40              2.17 
In Instance Efficiency Percentages (Target 100%), we see that
Execute to Parse %:  70.64
Parse CPU to Parse Elapsd %: 0.04 
I want to know if the above report suggest that server is performing lot of hard parses due to some probems in the SQL queries being sent to the server i.e.(missing bind variables in the query).

Further, why there are lots of waits on high waits on cursor event
  • 1. Re: high waits on cursor: pin S wait on X
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check MOS Notes :
    High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity [ID 742599.1]
    Instance hang with very high wait on 'cursor: mutex S' [ID 1274320.1]
    Bug 6904068 - High CPU usage when there are "cursor: pin S" waits [ID 6904068.8]
  • 2. Re: high waits on cursor: pin S wait on X
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,
    Moazzam wrote:
    We are using Oracle 10g R2 on Linux platform.

    In AWR report, it is observed that waits for cursor: pin S wait on X are very high. Followings are the top 5 wait events:
    ...
    In Time Model statistics, followings are the top 5 rows:
    Statistic Name                                  Time (s)      % of DB Time 
    parse time elapsed                            504,697.67             66.18 
    ...
    hard parse elapsed time 79,544.87 10.43
    ...
    I want to know if the above report suggest that server is performing lot of hard parses due to some probems in the SQL queries being sent to the server i.e.(missing bind variables in the query).

    Further, why there are lots of waits on high waits on cursor event
    Yes, your system is showing clear symptoms of parsing too much. The "cursor: pin S wait on X" wait event means that one session tries to pin a cursor in a shared mode when another one already has pinned it exclusively. It often happens when the database parses more than it should. I suggest that you look at:

    1) load profile section of the AWR report (to see how many hard parses and parses per second you have, and how this number compares to the number of user calls and executes)
    2) use force_matching_signature to identify to SQL queries to benefit from bind variables as described here: http://savvinov.com/2012/05/29/where-awr-cant-help-identifying-top-sql-in-absence-of-cursor-sharing/

    Best regards,
    Nikolay
  • 3. Re: high waits on cursor: pin S wait on X
    Moazzam Pro
    Currently Being Moderated
    Following is the load profile section:

    Load Profile
         --         Per Second  Per Transaction 
    User calls:    749.85            12.34 
    Parses:        311.13             5.12 
    Hard parses:   2.16               0.04 
    Executes:      1,059.70          17.44 
    Transactions:  60.76   
    From above we see that there are 2.16 hard parses for 311.13 parses per second. The hard parses compared to the total parses are not large enough to suggest us that hard parses is causing the high total waits for high waits on cursor event.
  • 4. Re: high waits on cursor: pin S wait on X
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,
    Moazzam wrote:
    Following is the load profile section:

    Load Profile
    --         Per Second  Per Transaction 
    User calls:    749.85            12.34 
    Parses:        311.13             5.12 
    Hard parses:   2.16               0.04 
    Executes:      1,059.70          17.44 
    Transactions:  60.76   
    From above we see that there are 2.16 hard parses for 311.13 parses per second. The hard parses compared to the total parses are not large enough to suggest us that hard parses is causing the high total waits for high waits on cursor event.
    1) hard parses are <1% of total parses. But they are responsible for 1/6 of time spent parsing
    2) soft parses are not as bad as hard parses, but nevertheless, they are quite bad
    3) look at SQL ordered by parse calls -- what are the top statements and how did they make it into that list? Is the application closing cursor that later need re-using?
    4) look in ASH to see if you can associate the wait event with specific SQL
    5) you still have to do something about hard parses -- use force_matching_signature to identify top offenders

    Best regards,
    Nikolay
  • 5. Re: high waits on cursor: pin S wait on X
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    I suggest read some related doc for your issue to get why you have this wait

    WAITEVENT: "cursor: pin S wait on X" Reference Note [ID 1298015.1]

    Than you can investigate your issue
         Troubleshooting 'cursor: pin S wait on X' waits. [ID 1349387.1]

    Regard
    Helios
  • 6. Re: high waits on cursor: pin S wait on X
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Moazzam wrote:
    We are using Oracle 10g R2 on Linux platform.

    In AWR report, it is observed that waits for cursor: pin S wait on X are very high. Followings are the top 5 wait events:
    There are several bugs relating to mutex handling in 10gR2 - you don't say which specific point release, but the earlier the release you have the more likely you are seeing some of the bugs. Osama Mustafa has already pointed you to one MOS document (High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity [ID 742599.1]) which may be relevant, there could be several others available. [update: and I see that Helios Gunos has cited a couple more while I was typing this response. Make sure you check relevance to your specific version, and also look at the bug patch documents for newer versions to see if there is a patch for your version.]

    A part of your problem (that appears to be relatively small according to the time model) relates to bind mismatches - Nikolay Savvinov suggested looking at SQL ordered by Parse Calls in the AWR, it would also be worth looking at the SQL ordered by Version Count. It is possible that you are generating large numbers of child cursors for some of your SQL - this could result in long hold times on the library cache lock. Unfortunately when you get into the condition you are in there is often a feedback effect where a small change in activity results in a catastrophic cycle of rapidly increasing contention, so it becomes hard to determine what is cause and what is effect.

    Regards
    Jonathan Lewis

    Edited by: Jonathan Lewis on Nov 20, 2012 8:32 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points