Thinking outside of the patch: Resolving “cursor: pin s”

Oracle November 12, 2011

Simple Solutions to “cursor: pin s” wait events
..

There are 100’s of articles regarding “cursor: pin s” wait event but very few discussing real, simple and implementable solutions to “cursor: pin s” wait event and associated CPU spike (Bug 6904068). The objective of this article is to discuss the solution and as such not much emphasis is given to detect “cursor: pin s” wait events. It does not matter if you are a PL/SQL shop or Java shop, the options discussed in this article to reduce “cursor: pin s” focuses on implementing solutions that are simple, manageable and will not require extensive QA testing.

..
Now the million dollar question “Why would someone not apply the patch and resolve this issue?”.  There may be many reasons and I will list just couple of them

..
1.    Millisecond  SLA’s
2.    Use of underscore  parameter specially spare parameters
3.    Patch conflicts
4.    Extensive QA testing

..
Before we discuss some easy ways to resolve “cursor: pin s” issue, let us recap some of the existing solutions.  Please contact  Oracle Support if you are planning to use any of the following.

..
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.    The 2nd solution is   to disable mutexes and embrace library cache pins. Mutexes can be disabled by setting underscore parameter “_KKS_USE_MUTEX_PIN” to false.

..

From the above documented undocumented solutions, the 1st solution is widely used. This is a good if you don’t care about  your SLA’s, just apply the patch and take few milliseconds hit on SLA’s but what happens when your SLA’s are in microseconds or milliseconds, then applying this  patch  is not feasible because the lowest value that can be specified for ”_first_spare_parameter” is 1 centisecond or 10 microseconds.  Even though the lowest value for ”_first_spare_parameter” is 1 centisecond or 10 microseconds, the impact to response time is much lesser.

Now 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 way to detect “cursor: pin s” is running AWR report and the queries causing the “cursor: pin s” wait event can be obtained with ADDM report.
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.

..

Now that we have understood the problem, lets us looks at couple of solutions without use of underscore parameter.
Assumptions

..
Connections are made to database via  application servers

Outline Approach: Outlines are used to trick Oracle to create multiple child cursors for  high concurrency SQL statement.

..

Step 1.    Create  4 categories of outlines for the same high concurrency SQL

..

CREATE OUTLINE APP1_SQL1 FOR CATEGORY CAT_APP1
ON select ename from emp where ename =’SCOTT’ ;

CREATE OUTLINE APP2_SQL1 FOR CATEGORY CAT_APP2
ON select ename from emp where ename =’SCOTT’ ;

CREATE OUTLINE APP1_SQL1 FOR CATEGORY CAT_APP3
ON select ename from emp where ename =’SCOTT’ ;

CREATE OUTLINE APP2_SQL1 FOR CATEGORY CAT_APP4
ON select ename from emp where ename =’SCOTT’ ;

..

Step 2.    Issue ALTER SESSION to enable outlines from each application server before executing the high concurrency SQL. Logon triggers or any other approach can be used.
..

ALTER SESSION SET USE_STORED_OUTLINES= CAT_APP1 ;
ALTER SESSION SET USE_STORED_OUTLINES= CAT_APP2 ;
ALTER SESSION SET USE_STORED_OUTLINES= CAT_APP3 ;
ALTER SESSION SET USE_STORED_OUTLINES= CAT_APP3 ;

..

In the example below , I executed the same SQL statement with different outline category. In the 1st session , I execute the query once where as in the 2nd session , I executed the query 7 times. You can see the output  by querying v$sql. Please not that the child_address(child cursor) is different for both the SQL.

..

SQL>SELECT ADDRESS, CHILD_ADDRESS, EXECUTIONS, OUTLINE_CATEGORY, SQL_TEXT
2  FROM V$SQL WHERE SQL_ID=’7xhzp0kt9yt6j’;

ADDRESS          CHILD_ADDRESS    EXECUTIONS OUTLINE_CATEGOR SQL_TEXT
—————- —————- ———- ————— ————————————————–
070000013B771C88 070000013B770D58          1 CAT_APP1        select ename from emp where ename =’SCOTT’
070000013B771C88 070000013B75C4A0          7 CAT_APP2        select ename from emp where ename =’SCOTT’

JAVA prepare statement Approach:  This approach is good when SQL statement is prepare 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”

“select ename from emp myapp2  where myapp2.ename = :1”
Instead of
“select ename from emp where ename =:1”

Tagged: , , , , , , , , , , ,

%d bloggers like this: