Tag Archives: cursor pin s

Simple explanation of “cursor: pin S wait on X”

Most Popular, Oracle May 21, 2011 Comments: 7

I think it is very  difficult if not impossible to  understand “cursor: pin S wait on X” without the knowledge of “cursor: pin S“.  So this blog will start with “cursor: pin S” along with basics of mutex.

Also see my blog about “cursor:pin s”  with more detailed info https://swervedba.wordpress.com/2011/03/30/cursor-pin-s/

Oracle introduced Mutexes in Oracle Version 10.2 and they stand for mutual exclusion (functionality is similar to mutexes in c).  Mutexes are used to protect data or other resources from concurrent access.

Working of Oracle Mutexes

Oracle uses counters  in implementing mutexes.  The counters are called as reference counters

1. Every time a particular mutex is obtained, Oracle will increment its value.

2. Every time a particular mutex is released, Oracle will decrement its value.

Cursor Pin S : A wait even that occurs when a session wants a specific mutex in shared mode on a specific cursor. As mentioned in the previous section,  Oracle has to update the ref counters  to get the mutex

However it is very important to understand that access to these counters are not concurrent. If there are concurrent sessions trying to obtain a mutex, then only one session can actually increment or decrement the reference counter at a time. Therefore concurrent sessions requesting the mutex must wait. This wait is called as “cursor: pin s” wait.

In short

“cursor: pin s” wait event occurs when Oracle wants a specific mutex in shared  mode on a specific cursor but could not obtain it as it was not able to update the reference counters (Not because another session was holding  the mutex in exclusive mode) .

“cursor: pin S wait on X” wait event occurs when Oracle is not able to get the mutex  in share(S)  mode  because some other session is  holding the  same mutex in  exclusive(X) mode.

Can “_first_spare_parameter fix the “cursor: pin s” issue?

Oracle May 21, 2011 1 Comment

The objective of this blog is to discuss the test results with _first_spare_parameter settings.  Please visit https://swervedba.wordpress.com/2011/03/ to understand  “cursor: pin s” issue.

So let’s get started with basics.

Oracle  development standards do not allow introducing new initialization parameters  with interim patches to fix bugs. So  “spare” parameters  are kind of reserve initialization parameters to fix bugs with interim patches. There are a lot of spare parameters like _first_spare_parameter , _second_spare_parameter and so  on

Using this parameter without Oracle support is s very dangerous as Patch 88888888  with spare setting of 1  may resolve bug ABC whereas Patch 99999999 with spare setting of 1 may resolve bug XYZ.

Please note that the value and meaning of the spare parameter changes with Oracle version.  As an example, _first_spare_parameter  setting of 50 was recommended for  streams in Oracle 9i.

Coming to  high CPU issue because of “cursor: pin  s” , the solution is to apply patch 6904068  and play with different values of _first_spare_parameter. The values for _first_spare_parameter is specified in centiseconds and the lowest value that can be set is 1 centisecond or 10ms .  When the patch and _first_spare_parameter is set ,  Oracle will sleep on CPU  for specified centiseconds when it cannot get mutexes

After applying patch 6904068 , we tested various configuration of  _first_spare_parameter to fix bug on 10.2.0.3 database.    Key points are

  1. Best results were with _first_spare_parameter setting of 1
  2.  Setting “_first_spare_parameter” to 0 is as good as not applying the patch as it reverts to default behavior without patch
  3. Querying x$ksppi for _first_spare_parameter before applying the patch returns null value but after applying the patch, _first_spare_parameter  defaults to 0. Off course this will depend on one-off patches applied in your environment
  4. Parameter “_first_spare_parameter” can be modified online
  5. All tested settings of this parameter other than 0 resulted in negligible performance degradation.
  6. The wait for the parameter ” _first_spare_parameter” did not work as intended even though the “cursor: pin s” issue was fixed.

See test results below

Simple explanation of Wait Event “cursor: pin s”

Oracle March 30, 2011 Comments: 5

Let’s start with basics to understand the root cause of this problem.

What are Mutexes?

Oracle introduced Mutexes in Oracle Version 10.2 replacing library cache pin and they stand for mutual exclusion (functionality is similar to mutexes in c).  Mutexes are used to protect data or other resources from concurrent access. Don’t come to any conclusion based on the previous statement.  Please read ahead.

How does Oracle Mutexes work?

Oracle uses counters to implement mutexes.  The counters are called as reference or ref counters

1. Every time a particular mutex is obtained, Oracle will increment its value.

2. Every time a particular mutex is released, Oracle will decrement its value.

Finally what is “cursor pin s”?

A wait even that occurs when a session wants a mutex in shared mode on a cursor. As mentioned in the previous section, Oracle has to update the ref counters to get the mutex

However it is very important to understand that access to these counters are not concurrent. If there are concurrent sessions trying to obtain the mutex, then only one session can actually increment or decrement the reference counter at a time. Therefore these concurrent sessions must wait.

Four key points to avoid confusion are

  1. Oracle is requesting the mutex in shared mode
  2. No session is holding  the mutex in exclusive mode
  3.  Wait event occurs as Oracle was not able to update the reference counters.
  4. Sessions will wait over and over until the mutex is obtained. This may cause CPU spike.

The simplest analogy to understand “cursor pin s” is traffic bottlenecks caused by Tolls. There may not be a real traffic problem because of road capacity or lane merger. Instead the traffic problem is caused by toll booth.

The easiest way to detect “cursor: pin s” is running AWR report and the queries causing the “cursor: pin s” wait event can be identified with ADDM report.  You can also query

  1. V$ACTIVE_SESSION_HISTORY:  Column P1 (idn or sql hash_value)
  2.  V$MUTEX_SLEEP_HISTORY:  Column MUTEX_IDENTIFIER (idn or sql hash_value)

Bugs

One of the notorious bugs associated with mutexes  is Bug 6904068. This affects almost all versions of Oracle  from 10.2.0.x to 11.2.0.x .  We hit this bug with 10.2.0.3 database because one of our SQL statement was executed with very high concurrency.

Database Solutions (As usual please contact Oracle support when playing around with underscore parameter)

1. Apply patch for the bug 6904068 and then adjust _FIRST_SPARE_PARAMETER.  After applying the patch, setting _FIRST_SPARE_PARAMETER to ZERO will retain current behavior.

2. Set underscore  parameter “ _CURSOR_FEATURES_ENABLED”  to  10

3. Disable mutexes and embrace library cache pins. Mutexes can be disabled by setting underscore parameter “_KKS_USE_MUTEX_PIN” to false

Application solution

1. Synonym Approach: Create multiple synonyms for table and use them in SQL statements. This will force Oracle  to use different SQL area for the SQL statement.  This is good if high concurreny SQL is used for multiple packages.

  • Select  ename f rom synonym1 where job=:b1; (In Package emppkg1)
  • Select  ename f rom synonym2 where job=:b1; (In Package emppkg2)

Where synonym1  and synonym2 are synonyms for  table EMP

2. Table Alias Approach: Create table aliases for the same SQL if they are used in different packages

  • Select  ename f rom EMP A  where job=:b1; (In Package emppkg1)
  • Select  ename f rom EMP B where job=:b1; (In Package emppkg2)

3.JAVA prepare statement Approach:  This approach is good when SQL statement is prepared using JAVA where in we modify the SQL statement in a way to have multiple SQL areas for same SQL statement by adding table alias for the SQL statement.  The simplest way to generate  table alias is using hostname.

“select ename from emp myapp1  where myapp1.ename = :1″  INSTEAD OF    “select ename from emp where ename =:1”


Moral of Story 

Everything in excess is opposed to nature –Hippocrates  .