Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

cursor: pin S wait on X

Oracle Database Tips by Donald BurlesonMay 1, 2013

Question:  My AWR report shows "cursor: pin S wait on X" responsible for over 90% of my database processing time. How do I fix the "cursor: pin S wait on X" event?

Answer:  The "cursor: pin S wait on X" is related to the new "mutex" option.  Oracle 10g release 2 and beyond replaced some latch mechanisms with the mutex approach, claiming that they are faster and more efficient than traditional locking mechanisms.  To improve cursor execution speed and hard pare time within the library cache, mutexes replace library cache latches and library cache pins. Oracle claims that mutexes are faster and use less CPU, which is important for CPU-bound database where large data buffers remove I/O as a primary source of contention. 

Beware, Oracle bug 5184776 can affect 11g databases that deploy the mutex latching, causing excessive 'cursor pin s wait on x' wait events.

If you suspect that you have a bug, you can revert to the pre-mutex method with the consent of Oracle Support using these commands that bounce your database into the older latching mode, which may result in less deadlocks:

alter system set "_kks_use_mutex_pin"=false scope=spfile;
shutdown immediate
startup

NOTE:  This hidden parameter should only be used with the consent of Oracle support, and this parameter should NOT ever be set in Oracle 12c

The cursor pin S wait on X wait event is mostly related to mutex and hard parse and it happens when a session waits for this event when it is requesting a shared mutex pin and another session is holding an exclusive mutex pin on the same cursor object.  

These are the parameters associated with this mutex wait event:

  •  P1 Hash value of cursor
  •  P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
  •  P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps  

---------------------------------------
Rampant Author Brian Peasland has these observations on the cursor pin wait on x event:

"Any of the "cursor:" waits are bottlenecks in the Shared Pool in the SQL Area. A long time ago, this portion of the Shared Pool was protected by latches. But as is the case with many areas of the Shared Pool, Oracle is now using mutexes. With the change in the protection mechanism, we now have new wait events.

In the case of this particular wait event (cursor pin wait on x), we have a cursor that is wanting a Shared pin but must wait for another session to release its eXclusive mutex. A cursor is trying to be parsed. But it can't be parsed because another session is holding on to the same mutex.

There are three main causes to sessions waiting on this event.

  • High hard parses
  • A high number of versions of the SQL statement
  • Bugs

Unfortunately, there are a number of bugs related to this wait event. Most of the ones that I have seen are fixed in 11.2.0.4 or 12.1.0.1 so if you are lagging behind in versions, consider upgrading to one of the more recent Oracle versions."

 
 
 
Learn RAC Tuning Internals!

This is an excerpt from the landmark book Oracle RAC Performance tuning, a book that provides real world advice for resolving the most difficult RAC performance and tuning issues.

Buy it  for 30% off directly from the publisher.

So let's see if we can walk through an example to determine the cause of the problem. To do that, I used the following query:

select s.inst_id as inst,
       s.sid as blocked_sid, 
       s.username as blocked_user,
       sa.sql_id as blocked_sql_id,
       trunc(s.p2/4294967296) as blocking_sid,
       b.username as blocking_user,
       b.sql_id as blocking_sql_id
from gv$session s
join gv$sqlarea sa
  on sa.hash_value = s.p1
join gv$session b
  on trunc(s.p2/4294967296)=b.sid
 and s.inst_id=b.inst_id
join gv$sqlarea sa2
  on b.sql_id=sa2.sql_id
where s.event='cursor: pin S wait on X';

Running this in one of my production RAC databases, I get the following output:

INST BLOCKED_SID BLOCKED_USER BLOCKED_SQL_ID BLOCKING_SID BLOCKING_USER BLOCKING_SQL_ID
---- ----------- ------------ -------------- ------------ ------------- ---------------
   4         723 USER12345    cn7m7t6y5h77g          1226 USER12345     cn7m7t6y5h77g 
   4         723 USER12345    cn7m7t6y5h77g          1226 USER12345     cn7m7t6y5h77g 
   4         723 USER12345    cn7m7t6y5h77g          1226 USER12345     cn7m7t6y5h77g 
   4         723 USER12345    cn7m7t6y5h77g          1226 USER12345     cn7m7t6y5h77g 
   4        1226 USER12345    cn7m7t6y5h77g          1796 USER12345     cn7m7t6y5h77g 
   4        1226 USER12345    cn7m7t6y5h77g          1796 USER12345     cn7m7t6y5h77g 
   4        1226 USER12345    cn7m7t6y5h77g          1796 USER12345     cn7m7t6y5h77g 
   4        1226 USER12345    cn7m7t6y5h77g          1796 USER12345     cn7m7t6y5h77g

The first thing to note is that the mutex is only within that instance for Oracle RAC databases. For single-instance databases, the query above will still work. For Oracle RAC, the output from this query will show which instance is having the problem.

In the example above, we have session 723 blocked by session 1226. Session 1226 is further blocked by session 1796. Notice that all three sessions are issuing the same query with SQL ID cn7m7t6y5h77g.

Now that we know the SQL ID, we can easily query V$SQL to determine the SQL statement involved in the problem. I used this query to obtain more information.

select sql_id,loaded_versions,executions,loads,invalidations,parse_calls
from gv$sql 
where inst_id=4 and sql_id='cn7m7t6y5h77g';

The output from querying V$SQL is as follows:

SQL_ID        LOADED_VERSIONS EXECUTIONS LOADS      INVALIDATIONS PARSE_CALLS
------------- --------------- ---------- ---------- ------------- -----------
cn7m7t6y5h77g               1        105        546           308        3513

We can now see that this query has only 1 version in the SQL Area. So right away, we've eliminated one of the potential problem areas.

It should be obvious from the above that there is a very high number of parse calls. The query has only been executed 105 times but has been parsed 3513 times. Yikes!  The high number if invalidation's probably has something to do with this as well.

In this example, we now have a good idea of what the problem is. This is an application issue. The application is over-parsing the query. So we'll send this back to development and dig into the application code. The usual reasons for over-parsing need to be examined.

If the number of versions were low and excessive parsing/invalidations/loads was not an issue, then I would suspect a bug and file a SR with My Oracle Support Community (MOSC).




 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster