Home -> Mailing Lists -> Oracle-L -> Re: Identify blocking session for cursor: pin S wait on X when database is hung

Re: Identify blocking session for cursor: pin S wait on X when database is hung

From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 28 Feb 2011 12:28:58 -0700
Message-ID: <4D6BF77A.1000206_at_evdbt.com> 



  
    
  
  
    Sanjeev,

    

    Query DBA_HIST_ACTIVE_SESS_HISTORY where EVENT = 'cursor: pin S wait
    on X' and use the information provided there.  What might be most
    useful will be the relationship between SESSION_ID/SESSION_SERIAL#
    and BLOCK_SESSION_ID/BLOCK_SESSION_SERIAL#, as well as the P1, P2,
    and P3 value associated with the wait event, as well as the SQL_ID.

    

    Have fun!

    
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => 13087 West Cedar Drive #225, Lakewood CO 80228
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...


    

    On 2/28/2011 11:34 AM, Sanjeev M wrote:
    

      
All,

      
 

      

        
Platform            : Solaris(64-bit)

        
RDBMS Version: 11.1.0.7.2

      

      
 

      
Our production database experienced hang and we could not
        connect to sqlplus and hang cleared on its own after few
        minutes. From AWR report for duration of the hang Top 5 Timed
        Foreground Events were:

      
 

      

        

          
            

              Event
              Waits
              Time(s)
              Avg wait (ms)
              % DB time
              Wait Class
            

            

              
                
cursor: pin S wait on X 

              		
              34,422,745
              688,152
              20
              49.78
              Concurrency
            

            

              row cache lock
              205,545
              518,821
              2524
              37.53
              Concurrency
            

            

              library cache load lock
              778
              110,728
              142324
              8.01
              Concurrency
            

            

              DB CPU
               
              27,809
               
              2.01
               
            

            

              rdbms ipc reply
              16,003
              22,098
              1381
              1.60
              Other
            

          
        

      

      
 

      
Found this MOS document : HOW
            TO FIND BLOCKING SESSION FOR MUTEX WAIT EVENT cursor: pin S
            wait on X [ID 786507.1] however when the issue
          occured could not get connection to sqlplus 

      
 

      
QUESTION: Is there a
          way to find out blocker session for this wait event after the
          fact the issue got cleared and the sessions got completed?

      
 

      
Other research done and metalink notes found were:

      
 

      
Found this note: "Cursor: Pin
            S Wait On X" Contention Mutex Sleep Reason Primarily '
            kkslce [KKSCHLPIN2]' [ID 1268724.1]  

      
 

      

        

          
            

              Mutex Type
              Location
              Sleeps
              Wait Time (ms)
            

            

              Cursor Pin
              kkslce [KKSCHLPIN2]
              31,465,924
              -3,361,856
            

            

              Cursor Pin
              kksfbc [KKSCHLFSP2]
              3,607,296
              2,750,834
            

            

              Cursor Parent
              kksfbc [KKSPRTLOC1]
              406,049
              1,120
            

          
        

      

      
 

      
Although we have above event the solution mentioned in this
        note is not relevant in our environment.

      
  

      
I have found this bug: Bug 7234778 
          Unnecessary "cursor: pin S wait on X" wait and I am going to
          follow up with oracle support on this with service request.

      
 

      
Regards,

      
Sanjeev.

    

  

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 28 2011 - 13:28:58 CST












Original text of this message