Introduction

This blog post deals with SQL plan management capture, SQL statements with multiple plans and SPM evolution.

For core concepts, see Part 1.

For automatic SQL plan management, see here.

Plan Capture

At some point you will want to capture initial SQL plan baselines for SQL statements: we want the workload to continue to use an initial set of ‘good’ plans. An easy method is to set the following database parameter when the system is working well:

optimizer_capture_sql_plan_baselines = TRUE

Plans are captured for any SQL statement that is executed more than once. If a SQL statement has more than one plan, then the first plan hard parsed will be ACCEPTED=YES and the rest will be ACCEPTED=NO. There is a small hard-parse overhead using this capture method because the database must persist SQL plan baseline details each time a new SQL statement or execution plan is seen.

There is a risk that you will capture more than you intend, but you can set the parameter at the session level to limit its scope or use filters to control the scope of which SQL statements are captured (using DBMS_SPM.CONFIGURE).

Other approaches allow you to take plans from AWR, SQL tuning sets, or even individual plans directly from the cursor cache. For example, with load_plans_from_cursor_cache you can load and accept plans for SQL statements based on any criteria you choose. You don’t even have to enable them immediately, either. For example:

declare
   n number;
begin
   for rec in (select sql_id
               from   v$sqlarea
               where  executions > 10000
               and    parsing_schema_name = 'THE_APPUSER')
   loop
      n := dbms_spm.load_plans_from_cursor_cache(
                 sql_id   => rec.sql_id,
                 enabled  => 'NO');
   end loop;
end;
/

In this way, you could have some SQL plan baselines ready to resolve plan issues very quickly.

There are other approaches you could consider:

  • Capture an application workload in a SQL tuning set and create SQL plan baselines from this.
  • Capture plans from AWR. In this way, you can easily capture plans for the most resource-intensive queries in your workload.

All plans from these sources will be marked ACCEPTED=YES immediately, but you can control whether or not they are enabled.

SQL Statements with Multiple Plans

SQL statements marked bind sensitive may require multiple SQL execution plans depending on bind variable values. This feature is known as adaptive cursor sharing. SQL plan management works with this feature because it allows multiple plans to be associated with a single SQL statement. Consider the following scenario, where an application query filters a highly-skewed dataset:

SQL> — Capture SQL plan baselines for our query
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
SQL> — A bind value of 1 matches only one row in the table
SQL> exec :bind1 := 1;
SQL> — The SQL statement needs to be executed more than once to be auto-captured
SQL> select sum(num) from tab1 where code = :bind1;
SQL> select sum(num) from tab1 where code = :bind1;

  SUM(NUM)   COUNT(*)
———- ———-
         1          1

SQL> — Finish auto capture
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
SQL> — A SQL plan baseline has been created
SQL> select plan_name, accepted, sql_text
     from dba_sql_plan_baselines;

PLAN_NAME                      ACC SQL_TEXT
—————————— — ——————————————————
SQL_PLAN_769kr0bj4gfj8a077eb5c YES select sum(num),count(*) from tab1 where code = :bind1

SQL> — A bind value of 0 matches 10000 rows so in this case it is
SQL> — better to use a plan that does not use the index
SQL> exec :bind1 := 0;
SQL> — The query is executed multiple times and ACS will identify a different plan
SQL> select sum(num),count(*) from tab1 where code = :bind1;
SQL> …

  SUM(NUM)   COUNT(*)
———- ———-
  50005000      10000

SQL> — The new plan is in the SQL plan history
SQL> select plan_name, accepted, sql_text
     from dba_sql_plan_baselines;

PLAN_NAME                      ACC SQL_TEXT
—————————— — ——————————————————
SQL_PLAN_769kr0bj4gfj8a077eb5c YES select sum(num),count(*) from tab1 where code = :bind1
SQL_PLAN_769kr0bj4gfj8ec1c7603 NO  select sum(num),count(*) from tab1 where code = :bind1

The new plan is now visible in the SQL plan history (ACCEPTED=NO). The alternative plan is captured because the SQL statement has an existing (enabled) SQL plan baseline. You can see that in the example above, a SQL plan history entry appears in dba_sql_plan_baselines even though optimizer_capture_sql_plan_baselines is set to FALSE.

SPM Evolution

The process of SPM evolution is responsible for confirming that a new plan in the SQL plan history performs better than existing SQL plan baseline plans before it is accepted.

There are a number of things to bear in mind:

  • New plans will not be captured in the SQL plan history (for a given SQL statement) if all SQL plan baselines for the statement are FIXED and ENABLED.
  • A SQL plan baseline with FIXED=YES and ENABLED=NO is not considered a fixed plan.
  • New plans will be captured in the SQL plan history even if a statement’s existing (non-fixed) SQL plan baselines have ENABLED=NO.
  • In the example above, the optimum plan for bind = 0 will not be used until plan SQL_PLAN_769kr0bj4gfj8ec1c7603 has been evolved and accepted (usually during the maintenance window). For this reason, a period of time will pass before a better plan newly discovered by the optimizer is available for use.

Evolution is the process of test executing new plans in the background to establish whether they perform better than existing plans. The ACCEPTED status will be set to YES for plans that are proven to perform better than existing ones. In other words, the database does not rely on cost estimates, it tests and compares plan performance to figure out which are best.

Evolution can be initiated manually:

SQL> var tname varchar2(100)
SQL> …
declare
   ename varchar2(100);
begin
   :tname := dbms_spm.create_evolve_task();
   ename  := dbms_spm.execute_evolve_task(:tname);
end;
/

If the plan in the SQL plan history yields a non-negligible performance improvement, then a new plan baseline will be created:

SQL> select plan_name, accepted, sql_text
     from dba_sql_plan_baselines;

PLAN_NAME                      ACC SQL_TEXT
—————————— — ——————————————————
SQL_PLAN_769kr0bj4gfj8a077eb5c YES select sum(num),count(*) from tab1 where code = :bind1
SQL_PLAN_769kr0bj4gfj8ec1c7603 YES select sum(num),count(*) from tab1 where code = :bind1

The SPM report allows you to see what happened during evolution.
SQL> set long 100000
SQL> select dbms_spm.report_evolve_task(task_name=>:tname) report
     from dual;

Auto Evolution

An auto task evolves plans in the SQL plan history for you. Nevertheless, if you prefer to closely control which new plans are accepted, then you can always:

BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
      parameter => 'ACCEPT_PLANS',
      value     => 'FALSE');
END;
/

This allows the database to capture new plans and test them in the background but none will be accepted automatically.You will have a report at your disposal to tell you which plans are better:

report := dbms_spm.report_auto_evolve_task( … );

You can choose which plans you would like to accept based on the recommendations in the report.

SQL Statements with Multiple Plans – Adaptive Cursor Sharing and Statistics Feedback

As mentioned above, adaptive cursor sharing (ACS) allows the Oracle Database to use different SQL execution plans based on bind variable values. This is most useful in situations where data values in the database have range or value skews. The result is that some SQL statements have more than one child cursor and multiple SQL execution plans. SPM caters for this because each SQL statement can have multiple accepted plans.

When a SQL statement is parsed, the optimizer chooses a plan based on the lowest predicted cost – without a particular plan being imposed by a SQL plan baseline. If there is a SQL plan baseline that matches the optimizer’s chosen plan, then this plan will be used. If the chosen plan does not match a SQL plan baseline (and multiple plan baselines are available), then a SQL plan baseline plan is chosen based on the lowest predicted cost (but also giving fixed SQL plan baselines priority). In this way, SQL plan baselines act as a mechanism for constraining what plans are available for use.

Over the course of multiple SQL statement executions, the optimizer’s ACS mechanism may use bind variable values to influence costing. This can result in the creation of new child cursors, each with a different plan. A different feature called statistics feedback can influence the optimizer over multiple executions too. These mechanisms work together and are transparent. The net effect is that an individual SQL statement may transition through a number of different plans on its way to a final destination. In addition, a number of different plans may be created over time to cater for different bind variable values. Nevertheless, during these operations, SQL plan baselines will continue to constrain which plans are available for use.

In summary, ACS and statistics feedback adjust plans over time and these plans can be used only as long as a matching SQL plan baseline exists. A SQL statement with multiple plans therefore allows ACS and statistics feedback ‘room to breathe’. It becomes possible for an individual SQL statement to change plan over time and have child cursors that account for different bind values.

A corollary of this is that there may be some variation in performance while plans settle, but this is normal and is not an indication that SPM is working incorrectly.

Nevertheless, in rare cases, a transition through several plans might be enough to have a negative and noticeable effect on the application performance. In situations such as this, it is usually best to minimize the choices of plan available and disable SQL plan baselines that are known to be yield suboptimal performance. If a SQL statement is selecting a sub-optimal plan from the SQL plan baselines available, you can track this down as follows:

SQL> — Examine the Note section in the explain plan…
SQL> select *
     from   table(dbms_xplan.display_cursor(sql_id=>’0tzpp5x6zd4cr’));
…
…
Note
—–
   – SQL plan baseline SQL_PLAN_1usb1uwtxd1nta077eb5c used for this statement

SQL> — Locate the SQL plan baseline
SQL> select *
     from  dba_sql_plan_baselines
     where plan_name = ‘SQL_PLAN_1usb1uwtxd1nta077eb5c’;

SQL> — See if there are multiple possible plans…
SQL> select count(*)
     from dba_sql_plan_baselines
     where accepted = ‘YES’
     and   enabled  = ‘YES’
     and   signature = (select signature
                        from   dba_sql_plan_baselines
                        where  plan_name = ‘SQL_PLAN_1usb1uwtxd1nta077eb5c’);

SQL> — You can disable a specific SQL plan baseline
SQL> declare
        n  number;
     begin
        n := dbms_spm.alter_sql_plan_baseline(
                 plan_name      =>’SQL_PLAN_1usb1uwtxd1nta077eb5c’,   
                 attribute_name =>’enabled’,attribute_value=>’no’);
     end;
     /

It is true that you might be disabling a plan that performs well for some bind variable values, but using a single or small number of good-enough SQL plan baselines can be expected to yield good results in the vast majority of cases. SQL statements resistant to this approach will probably require more traditional SQL tuning approaches to identify a different way to avoid sub-optimal plans.

SQL Statements with Multiple Plans – Hard Parse Time

Some SQL statements have many potential execution plans. Arguably, you’ll see this most often in cases where there are a large number of table joins and skew in the data sets. For bind values, different join orders and access methods may be beneficial. As mentioned in the previous section, adaptive cursor sharing takes care of this.

Imagine the scenario where a SQL statement has a large number of accepted SQL plan baselines, but periodically, the optimizer chooses a non-accepted plan. When this happens, each accepted plan is parsed to figure out which has the lowest cost (for the specific bind variable values used). The plan with the lowest cost will be chosen (because the plan originally chosen by the optimizer is not accepted). The net result of this is the hard parse phase will take longer than usual.

This is a rare scenario, but it is sometimes beneficial to disable some plans and keep the number of accepted plan baselines (for an individual SQL statement) down to a core set of ‘good enough’ plans.