SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines

In the example in Part 2, we saw that the optimizer used an accepted plan instead of a brand new plan. The statement has two plans in its plan history, but only one is accepted and thus in the SQL plan baseline:

SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT                                 PLAN_NAME                      ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES NO
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
  and s.time_id = t.time_id
  and p.prod_id < :pid

select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
  and s.time_id = t.time_id
  and p.prod_id < :pid

Non-accepted plans can be verified by executing the evolve_sql_plan_baseline function. This function will execute the non-accepted plan and compare its performance to the best accepted plan. The execution is performed using the conditions (e.g., bind values, parameters, etc.) in effect at the time the non-accepted plan was added to the plan history. If the non-accepted plan's performance is better, the function will make it accepted, thus adding it to the SQL plan baseline. Let's see what happens when we execute this function:

SQL> var report clob;

SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();

PL/SQL procedure successfully completed.

SQL> print :report

REPORT
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE =
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_fcc170b08cbcb825
-----------------------------------
  Plan was verified: Time used .1 seconds.
  Passed performance criterion: Compound improvement ratio >= 10.13
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:               960            960
  Elapsed Time(ms):              19             15              1.27
  CPU Time(ms):                  18             15               1.2
  Buffer Gets:                 1188            116             10.24
  Disk Reads:                     0              0
  Direct Writes:                  0              0
  Fetches:                        0              0
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.

The plan verification report shows that the new plan's performance was better and so it was made accepted and became part of the SQL plan baseline. We can confirm it by looking in the dba_sql_plan_baselines view:

SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT                                 PLAN_NAME                      ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
  and s.time_id = t.time_id
  and p.prod_id < :pid

select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
  and s.time_id = t.time_id
  and p.prod_id < :pid

The SQL plan baseline now has two accepted plans: SYS_SQL_PLAN_fcc170b08cbcb825 is now accepted.

You can either execute the evolve_sql_plan_baseline() function manually or schedule it to run automatically in a maintenance window.

Another way of evolving a SQL plan baseline is to use the SQL Tuning Advisor. Instead of executing evolve_sql_plan_baseline, suppose we start from the original state where we have one accepted and one non-accepted plan:

SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT                                 PLAN_NAME                      ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES NO
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
  and s.time_id = t.time_id
  and p.prod_id < :pid

select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
  and s.time_id = t.time_id
  and p.prod_id < :pid

You can execute the SQL Tuning Advisor on the cursor in the cursor cache:

SQL> var tname varchar2(30);

SQL> exec :tname := dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');

PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.execute_tuning_task(task_name => :tname);

PL/SQL procedure successfully completed.

SQL> select dbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TNAME,'TEXT','BASIC')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_505
Tuning Task Owner  : SH
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 11/11/2008 16:43:12
Completed at       : 11/11/2008 16:43:13

-------------------------------------------------------------------------------
Schema Name: SH
SQL ID     : bfbr3zrg9d5cc
SQL Text   : select p.prod_name, s.amount_sold, t.calendar_year
             from sales s, products p, times t
             where s.prod_id = p.prod_id
               and s.time_id = t.time_id
               and p.prod_id < :pid

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- A potentially better execution plan was found for this statement.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2787970893


----------------------------------------------------------------
| Id  | Operation                             | Name           |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |
|   1 |  NESTED LOOPS                         |                |
|   2 |   NESTED LOOPS                        |                |
|   3 |    HASH JOIN                          |                |
|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |
|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |
|   6 |     PARTITION RANGE ALL               |                |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   8 |       BITMAP CONVERSION TO ROWIDS     |                |
|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |
|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |
----------------------------------------------------------------

2- Original With Adjusted Cost
------------------------------
Plan hash value: 2787970893


----------------------------------------------------------------
| Id  | Operation                             | Name           |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |
|   1 |  NESTED LOOPS                         |                |
|   2 |   NESTED LOOPS                        |                |
|   3 |    HASH JOIN                          |                |
|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |
|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |
|   6 |     PARTITION RANGE ALL               |                |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   8 |       BITMAP CONVERSION TO ROWIDS     |                |
|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |
|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |
----------------------------------------------------------------

3- Using SQL Profile
--------------------
Plan hash value: 2361178149


------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | SELECT STATEMENT      |          |
|   1 |  HASH JOIN            |          |
|   2 |   HASH JOIN           |          |
|   3 |    PARTITION RANGE ALL|          |
|   4 |     TABLE ACCESS FULL | SALES    |
|   5 |    TABLE ACCESS FULL  | TIMES    |
|   6 |   TABLE ACCESS FULL   | PRODUCTS |
------------------------------------------

-------------------------------------------------------------------------------


SQL> exec dbms_sqltune.accept_sql_profile(task_name => :tname);

PL/SQL procedure successfully completed.

SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT                                 PLAN_NAME                      ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825  YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
  and s.time_id = t.time_id
  and p.prod_id < :pid

select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d  YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
  and s.time_id = t.time_id
  and p.prod_id < :pid

SQL> select sql_text, type, status from dba_sql_profiles;

SQL_TEXT                                 TYPE    STATUS
---------------------------------------- ------- --------
select p.prod_name, s.amount_sold, t.cal MANUAL  ENABLED
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
  and s.time_id = t.time_id
  and p.prod_id < :pid

What we see here is that SQL Tuning Advisor found a tuned plan (that coincidentally happened to be the non-accepted plan in our plan history). When we accepted the recommended SQL profile, the SQL Tuning Advisor created a SQL profile and also changed the non-accepted plan to accepted status, thus evolving the SQL plan baseline to two plans.

Note that the SQL Tuning Advisor may also find a completely new tuned plan, one that is not in the plan history. If you then accept the recommended SQL profile, the SQL Tuning Advisor will create a SQL profile and also add the tuned plan to the SQL plan baseline.

Thus, you can evolve a SQL plan baseline either by executing the evolve_sql_plan_baseline function or by using the SQL Tuning Advisor. New and provably better plans will be added by either of these methods to the SQL plan baseline.