Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Monthly Archives: April 2011

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