Jan 16

cursor: pin S wait on X

On my main production RAC database, I do see periods of slowness and the dominant wait event, system wide, is “cursor: pin S wait on X”. The event comes and goes, but I do see it from time to time. So I needed to get the bottom of this. Note, that this is not a RAC problem. This event can be easily seen on single-instance databases as well. When I do see this on multiple instances of my Oracle RAC database, it is because I have multiple sessions from the same application spread out among the instances, all doing the same thing, thus all having the same problem.

First, what is the wait event all about? 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, 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

All these problems tadalafil online australia including female sexual arousal disorder and how to improve on them. Over the years, the device cialis cialis uk has even acquired critics who claim that it only aids in getting an erection. Full restoration beds down strictly in your potential you can has the right viagra samples uk constructive appearance with regard to life. These are smoking, alcohol consumption, cost viagra online weight, and exercise.
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.

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. In a future blog post, I will discuss queries with high number of versions in the SQL Area. But that isn’t our problem today so we proceed.

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 Oracle Support.

bpeasland