Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Adaptive Cursor Sharing and SPM

Starting from Oracle version 11g there is new feature called Adaptive Cursor Sharing, that provides dynamic plan adaptation depending on the bind variable values. SQL Plan Management (SPM) it is the way Oracle 11g controls the plan stability of the sql statements by capturing the plans of sqls each time there is plan change, You need manually accept the plan to optimizer can use it. There is many articles over internet to read about this features. I want to share some of my thoughts and experience with this new possibilities.

If You check internet there is many articles about ACS, but in all of them You can find examples like “select count(*) from tab where id=:v” and how optimizer marks the statement as bind sensitive and after some manipulations with bind variable values that cause different selectivity, optimizer marks the cursor as bind aware. But what about real world systems, let get for example mixed OLTP/DSS system or just DSS. Any way in just DSS systems there is also can be some applications, at least some GUI interfaces, that executes huge number of small sql statements.

First of all I want to list the restrictions of the Adaptive Cursor Sharing in 11gR2, published by Oracle.

ACS will not mark the cursor as bind sensitive if:

  • It is using Parallel query
  • Hints are in use
  • Outlines are being used
  • It is not recursive query
  • The number of binds is more than 14

Only if all requirements is met, Oracle will mark the query bind sensitive and will monitor its selectivity. But if not, it will handle it as in the past versions.

In cases when we will use CURSOR_SHARING parameter to replace literals with binds and share cursor between different sessions, in 11gR2 we need to set it to FORCE to effectively use ACS. But what if we can’t use ACS because as you understand, in DSS or mixed systems, huge number of queries runs in parallel or will have more than 14 bind variables? Then our queries can’t use different plans for different bind values because optimizer will use only first parsed plan for all subsequent executions and it is not acceptable! Then, if we set CURSOR_SHARING=SIMILAR, we will face the problems related to huge number of child cursors and library cache mutex waits, that will hang our OLTP part of the system. Also, Oracle announced that SIMILAR setting will be deprecated in Oracle 12. You can say why to not set CURSOR_SHARING to FORCE and to forget about long running DSS queries, because they can’t cause problems with library cache contention because they are executes rarely. I can answer, because we want use another beautiful feature of 11g SQL Plan Management, to prevent plans of such long running queries from changing suddenly, because in such queries the plan change can cause query to run much longer than usual, it is DSS! And plan stability is very important in DSS systems.

In other words, if our system is mixed or DSS and our main report generating application is not using bind variables, then we can’t use this SQL Plan Management to guarantee plan stability. Because we can’t set CURSOR_SHARING to FORCE, because parallel queries will not use Adaptive Cursor Sharing, and we can not use CURSOR_SHARING=SIMILAR because it can affect other OLTP applications that share same system and more important this parameter value will be deprecated in future release.

The solution can be using the CURSOR_SHARING=FORCE for OLTP sessions and CURSOR_SHARING=SIMILAR for the DSS sessions, and hope that when You will migrate to 12 the Adaptive Cursor Sharing will be completed yet to consider Parallel Queries and all other listed conditions.

To do that I can suggest to set CURSOR_SHARING to FORCE at system level and use AFTER LOGON ON DATABASE trigger to set CURSOR_SHARING variable to SIMILAR or EXACT for DSS sessions depending on username or module, or whatever You want. For example something like this:

CREATE OR REPLACE TRIGGER SYS.after_logon after logon on database
 v_usr varchar2(64);
 v_module varchar2(364);
 v_code varchar2(20);
 v_errm varchar2(1000);
   if v_usr in ('SYSTEM','CRMUSR','DWHUSER','SCOTT') or v_module in ('CRM App', 'Reporting App') then
        execute immediate 'alter session set cursor_sharing=SIMILAR';
   end if;
  when others then
        v_code := sqlcode;
        v_errm := sqlerrm;
        sys.dbms_system.ksdwrt(2, 'ORA-20001: '||'The next message is from sys.after_logon trigger'||chr(10)||v_errm);

Also don’t forget that after setting CURSOR_SHARING=SIMILAR, You will need to extensively monitor sql baselines that will be captured by SQl Plan Manager. It is critical, because for example if Your have some big query to generate some report, and regarding on the date period given in the query it must use different plans, for example in one case it must use plan with hash value 4050579244 and in the other the optimal will be plan with hash value 3335976579. If first plan that was captured by the SQL Plan Manager will be for example 4050579244, then after You will change the date period in the query and optimizer will find that plan 3335976579 is more optimal for this bind values, it will capture it and save in the SPM Repository, but will not use it (optimizer_use_sql_plan_baselines is set to TRUE)! You will need to manually accept this new plan to optimizer can use it in the future. Same behavior is true for FORCE option or bind variables that was originally came with statement. And this makes it difficult to use Adaptive Cursor Sharing in conjunction with SPM. It means that You will need to monitor Your system and accept all plans that can benefit from different values of bind variables. I think that Oracle must consider that and develop something to automate this process in future releases. At least, I think, it will be better if SPM will consider not just sql text but the pair of sql text + bind selectivity, if plan is changed then it must compare bind selectivity if it is different then it must not be considered as plan change or must be captured but also marked as accepted, and only if for the same bind selectivity plan change occur mark it as not acceptable by default, it will make life much easer!

Or may be it can be helpful, if considered system is not in the development stage, I mean no changes are made to existing sqls or no new sqls will come up in near future. To set  optimizer_capture_sql_plan_baselines parameter to TRUE and optimizer_use_sql_plan_baselines parameter to FALSE, let the system to work some days or may be weeks, to capture most of possible plans, then after analyzing all captured possibilities accept useful ones and let optimizer use them, set optimizer_use_sql_plan_baselines to TRUE. But sure, it does not relieve the administrator from monitoring of new captured plans, much administrative time any way! Imagine, that You have 10 such databases, it will be nightmare.

Or, You can use CURSOR_SHARING=EXACT for DSS queries, then You will need to support Your system with always up to date and exact statistics, histograms to assure some plan stability! Because SPM will be completely useless in this case.

Will be interesting to hear your thoughts about that, how to make all this work together:)

(c) Aychin Gasimov, 04/2011, Azerbaijan Republic


5 responses to “Adaptive Cursor Sharing and SPM

  1. Kerry Osborne April 8, 2011 at 00:43

    Hi Aychin,

    Interesting post. I am not a fan of setting CURSOR_SHARING in general and definitely not a fan of setting it to SIMILAR (too many bugs over the years). I consider ACS to be a step in the right the direction but obviously it doesn’t go far enough to solve all the problems introduced by bind variable peeking. (it has to run statements badly to figure out that it needs another plan and it doesn’t save it’s work) You make a couple of good observations in this post. One of the main ones is that for ACS to be really helpful, it needs to be able to persist the bind variable information necessary to differentiate between multiple plans for a single statement. It works pretty well after the system has been running for a while, so why not save that information instead of re-penalizing the users when the system is bounced. By the way, I don’t know of any systems where SPM is being used in a major way yet (baselines on every statement). But maybe version 12 will solve these minor annoyances.


  2. aychin April 8, 2011 at 12:52

    Thank You for commenting Kerry,

    I think You absolutely right, we need to wait for new release and hope that this annoyances will be solved. It is shame that we can’t use this features completely, but there is great idea behind them.

    • Chinar May 13, 2011 at 05:23

      Hi Aychin.

      Yes this is interesting and important point, thank you for this.Because firstly it is a big feature of CBO which provide generating/selecting more efficient plan for a query based of bind values,secondly it is important for who want planning upgrade database to 11g(due to in this case there they can be met library cache latches(mutex or cursor pin) wait events and this can be significantly impact performance of database,to getting efficient/good plan oracle engine need perform extra hard parsing and result we get above waits).This feature works with restrictions when CURSOR_SHARING=SIMILAR(depend bind values states) and fully CURSOR_SHARING=FORCE.There are two important matter, one of these is reducing library cache latches/mutexes and second generating more efficient plan.In early releases using CURSOR_SHARING we can reduce this event but there rests CBO`s problem (which it can not 100% guarantee generated plan is good for all bind values,yes there in 9i introduced bind variable peeking but in this case CBO estimate selectivity of your query/predicate on first execution of cursor and this cursor`s bind values).As result we will get significantly more waits in 11g.And i agree with you and Kerry which you explained restrictions and enhancements of this feature.As you mentioned CURSOR_SHARING=SIMILAR will deprecate(or will remove) in next release ,so setting this parameter to SIMILAR is not recommend in 11g(need change to FORCE if we used that).Also general recommendation is to setting this parameter FORCE for OLTP systems and EXACT for DSS systems.But i think we need individually approach for specific systems(so its depend how designed database and application? do you use complex queries and how much? etc).But as you above said we can enable(or set CURSOR_SHARING) for specific sessions or modules level using LOGON trigger and this is can be good approach of such systems.In additionally note that one restriction of this feature is that is not available for queries which its predicates contain LIKE operator.

      • aychin May 13, 2011 at 06:39

        Thank You for interest. LIKE predicates is considered in, in older versions can be fixed by applying patch.

  3. medium May 28, 2013 at 23:01

    When some one searches for his vital thing, so he/she wishes to be available that
    in detail, therefore that thing is maintained over here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: