Monthly Archives: November 2011

Find if SPMs are being used

Oracle provides multiple  ways  to implement plan stability  starting with Outlines(8i)  followed by SQL profiles (10g).  As of 11g,  plan stability is implemented with SPM’s or SQL Plan Management. Off course you can use  hints to implement plan stability.  Some DBA’s swear by hints where as some of them are opposed to hints as they do not let the optimizer to choose  most effective execution plan.  Without going into the merits of one feature over the other, let me discuss about the main purpose of this blog.

If you implement outlines or SQL Profiles or SPM’s, you  really want a way to ensure that  optimizer is behaving as it is supposed to.  Unfortunately with 11.2.0.2, there is no  easy way to find if SPMs are being used or not. The  LAST_EXECUTE column of  DBA_SQL_PLAN_BASELINES  view does not get updated always as documented.  As per documentation LAST_EXECUTED column provides the timestamp when the plan baseline was last executed.

Here is a specific case scenario where  time stamp of LAST_ACTIVE_TIME column from V$SQL  is 11-AUG-2011 14:36:09 where as LAST_EXECUTE column of  DBA_SQL_PLAN_BASELINES  is 08-AUG-11 02.39.58.000000

SQL> select sql_id , SQL_PLAN_BASELINE,LAST_LOAD_TIME,LAST_ACTIVE_TIME from v$sql where sql_id =’fmg67gm9j2skh’;

SQL_ID SQL_PLAN_BASELINE LAST_LOAD_TIME LAST_ACTIVE_TIME
————- —————————— ——————- ——————–
fmg67gm9j2skh SQL_PLAN_7xaxz3w5hd4fbd6e420b9 2011-08-11/14:32:18 11-AUG-2011 14:36:09
fmg67gm9j2skh SQL_PLAN_7xaxz3w5hd4fbd6e420b9 2011-08-11/14:36:09 11-AUG-2011 14:36:09
fmg67gm9j2skh SQL_PLAN_7xaxz3w5hd4fbd6e420b9 2011-08-11/14:36:09 11-AUG-2011 15:17:40

SQL> select LAST_EXECUTED , plan_name from dba_sql_plan_baselines where plan_name=’SQL_PLAN_7xaxz3w5hd4fbd6e420b9′;

LAST_EXECUTED PLAN_NAME
————————————————————————— ——————————
08-AUG-11 02.39.58.000000 PM SQL_PLAN_7xaxz3w5hd4fbd6e420b9

We  opened an SR with Oracle. Oracle got back to us  stating that we are hitting Bug 8864741 and  provided some workaround to query SQLOBJ$. However even this table was NOT getting  updated.  So until this bug is fixed, the only fool proof way is tracing.

Advertisements

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

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”

Disable Optimizer Features

Every version of Oracle optimizer  has new features and bugs. As requirements of  every Oracle environment varies, there may be a need to enable and disable these features or bugs. From Oracle version 10.2.0.2 , Oracle  introduced underscore parameter “_FIX_CONTROL” to enable and disable certain features or bugs mainly related to optimizer. This parameter can be specified at session or system level or at init.ora level. 

The syntax is “_FIX_CONTROL”=’BUG#:OFF’ or “_FIX_CONTROL”=’BUG#:ON’.  Incorrect syntax may prevent the database from being started or cause hangs if issued with alter system command.

Any patch that you want to disable or enable must be installed and viewable in the V$SYSTEM_FIX_CONTROL. The  view V$SYSTEM_FIX_CONTROL provides brief description of the  patch and the Oracle version it was introduced. At session level , you can query V$SESSION_FIX_CONTROL.

Note: Please consult with Oracle support before setting this parameter