We have several new features in the 11g version related to change management. One of them is SQL Plan Baselining. That means that you intend a very conservative approach regarding the usage of new execution plans computed by the optimizer. In other words: You suspect that after a change, the newly computed execution plans may be bad and lead to a performance degradation.
Before the introduction of SQL Plan Baselines, you where able to fix the ‚good old execution plans‘ with hints or with stored outlines. Both have the disadvantage that they make it impossible for the optimizer to come up with even better execution plans than the ‚good old‘ ones. By using SQL Plan Baselines, you let the optimizer compute the new plans and you store them, with the possibility to later on test them and allow them for usage, if they have proved to be good. That process is called evolvement.
I have developed a little scenario to demonstrate that. First we see a statement with the ‚good old‘ execution plan of ACCESS BY INDEX ROWID:
SQL> select channel_id,count(*) from sales group by channel_id;
CHANNEL_ID COUNT(*)
---------- ----------
1 1
2 2064200
4 947328
3 4322623
9 16592
SQL> create bitmap index sales_bix on sales (channel_id) nologging;
Index created.
SQL> begin
dbms_stats.gather_table_stats('ADAM','SALES',cascade=>true,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 5');
end;
/
SQL> select sum(amount_sold) from sales where channel_id=1;
SUM(AMOUNT_SOLD)
----------------
1232.16
Execution Plan
----------------------------------------------------------
Plan hash value: 3388944652
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1107 (0)| 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | SALES | 5774 | 46192 | 1107 (0)| 00:00:14 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_BIX | | | |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CHANNEL_ID"=1)
Now I want to create a SQL Baseline for that statement with that execution plan.
One possibility to create it is the following:
SQL> show parameter baseline NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE SQL> select * from dba_sql_plan_baselines; no rows selected SQL> alter session set optimizer_capture_sql_plan_baselines=true; Session altered.
The following (repeating) statements in this session are getting recorded together with their execution plans:
SQL> select sum(amount_sold) from sales where channel_id=1; SUM(AMOUNT_SOLD) ---------------- 1232.16 SQL> select sum(amount_sold) from sales where channel_id=1; SUM(AMOUNT_SOLD) ---------------- 1232.16 SQL> alter session set optimizer_capture_sql_plan_baselines=false; Session altered.
Following shows that there was one statement with one plan was recorded. This one plan
(the index access) is the only accepted one.
SQL> column sql_text format a29
SQL> select SQL_TEXT,SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines;
SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC FIX
----------------------------- ------------------------------ ------------------------------ --- --- ---
select sum(amount_sold) from SYS_SQL_ec28978ecd0909c3 SYS_SQL_PLAN_cd0909c333c3099a YES YES NO
sales where channel_id=1
Now we change the conditions, so that a Full Table Scan becomes attractive to the optimizer:
SQL> alter table sales nologging;
SQL> insert /*+ append */ into sales select PROD_ID,CUST_ID,TIME_ID,1,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD from sales;
7350744 rows created.
SQL> commit;
Commit complete.
SQL> delete from sales where channel_id=3;
4322623 rows deleted.
SQL> commit;
Commit complete.
SQL> alter table sales enable row movement;
Table altered.
SQL> alter table sales shrink space cascade;
Table altered.
SQL> begin dbms_stats.gather_table_stats('ADAM','SALES',cascade=>true,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 5');
end;
/
PL/SQL procedure successfully completed.
The majority of rows now has the channel_id 1. The optimizer would like to do FTS now, but the baseline restricts it to the usage of the ‚good old‘ index access:
SQL> set autotrace on explain
SQL> select sum(amount_sold) from sales where channel_id=1;
SUM(AMOUNT_SOLD)
----------------
785647882
Execution Plan
----------------------------------------------------------
Plan hash value: 3388944652
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 59594 (1)| 00:11:56 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | SALES | 7340K| 56M| 59594 (1)| 00:11:56 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_BIX | | | | |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CHANNEL_ID"=1)
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_cd0909c333c3099a" used for this statement
The new plan, however, got recorded, but not accepted:
SQL> select SQL_TEXT,SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines; SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC FIX ----------------------------- ------------------------------ ------------------------------ --- --- --- select sum(amount_sold) from SYS_SQL_ec28978ecd0909c3 SYS_SQL_PLAN_cd0909c333c3099a YES YES NO sales where channel_id=1 select sum(amount_sold) from SYS_SQL_ec28978ecd0909c3 SYS_SQL_PLAN_cd0909c335032dee YES NO NO sales where channel_id=1
An evolvement has to prove a strong improvement compared to the old
plan in order to get the new plan accepted for usage by the optimizer:
set serverout on DECLARE report clob; BEGIN report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( sql_handle => 'SYS_SQL_ec28978ecd0909c3', plan_name=>'SYS_SQL_PLAN_cd0909c335032dee' ); dbms_output.put_line(report); END; / ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SYS_SQL_ec28978ecd0909c3 PLAN_NAME = SYS_SQL_PLAN_cd0909c335032dee TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SYS_SQL_PLAN_cd0909c335032dee ----------------------------------- Plan was verified: Time used 14.7 seconds. Failed performance criterion: Compound improvement ratio <= 1.01. Baseline Plan Test Plan Improv. Ratio ------------- --------- ------------- Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time(ms): 10883 3750 2.9 CPUTime(ms): 9697 2959 3.28 Buffer Gets: 49997 50091 1 Disk Reads: 46911 47217 .99 Direct Writes: 0 0 Fetches: 46911 1089 43.08 Executions: 1 1 ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of SQL plan baselines verified: 1. Number of SQL plan baselines evolved: 0. PL/SQL procedure successfully completed.
Although an improvement is visible, it is not big enough to lead to acceptance of the
new plan. This SQL Plan Baseline approach is really conservative.
But we can force the acceptance:
set serverout on DECLARE report clob; BEGIN report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( sql_handle => 'SYS_SQL_ec28978ecd0909c3', plan_name=>'SYS_SQL_PLAN_cd0909c335032dee', verify=>'NO' ); dbms_output.put_line(report); END; / ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SYS_SQL_ec28978ecd0909c3 PLAN_NAME = SYS_SQL_PLAN_cd0909c335032dee TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = NO COMMIT = YES Plan: SYS_SQL_PLAN_cd0909c335032dee ----------------------------------- Plan was changed to an accepted plan. ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of SQL plan baselines verified: 0. Number of SQL plan baselines evolved: 1. PL/SQL procedure successfully completed.
Now the optimizer is free to choose this accepted plan – or the other accepted ones.
SQL> select SQL_TEXT,SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines;
SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC FIX
----------------------------- ------------------------------ ------------------------------ --- --- ---
select sum(amount_sold) from SYS_SQL_ec28978ecd0909c3 SYS_SQL_PLAN_cd0909c333c3099a YES YES NO
sales where channel_id=1
select sum(amount_sold) from SYS_SQL_ec28978ecd0909c3 SYS_SQL_PLAN_cd0909c335032dee YES YES NO
sales where channel_id=1
SQL> set autotrace on explain
SQL> select sum(amount_sold) from sales where channel_id=1;
SUM(AMOUNT_SOLD)
----------------
785647882
Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 13795 (2)| 00:02:46 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| SALES | 7340K| 56M| 13795 (2)| 00:02:46 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CHANNEL_ID"=1)
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_cd0909c335032dee" used for this statement
We can even fix one execution plan – for example for runtime comparison.
First we demand index access:
declare numplans pls_integer; begin numplans:= DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle=>'SYS_SQL_ec28978ecd0909c3', plan_name=>'SYS_SQL_PLAN_cd0909c333c3099a', attribute_name=>'FIXED', attribute_value=>'YES'); dbms_output.put_line(numplans); end; / SQL> set autotrace on explain SQL> select sum(amount_sold) from sales where channel_id=1; SUM(AMOUNT_SOLD) ---------------- 785647882 Execution Plan ---------------------------------------------------------- Plan hash value: 3388944652 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 59594 (1)| 00:11:56 | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID | SALES | 7340K| 56M| 59594 (1)| 00:11:56 | | 3 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 4 | BITMAP INDEX SINGLE VALUE | SALES_BIX | | | | | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("CHANNEL_ID"=1) Note ----- - SQL plan baseline "SYS_SQL_PLAN_cd0909c333c3099a" used for this statement SQL> set autotrace off SQL> set timing on SQL> select sum(amount_sold) from sales where channel_id=1; SUM(AMOUNT_SOLD) ---------------- 785647882 Elapsed: 00:00:07.08
Now we demand Full Table Scan:
SQL> declare
2 numplans pls_integer;
begin
numplans:=
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SYS_SQL_ec28978ecd0909c3',
plan_name=>'SYS_SQL_PLAN_cd0909c333c3099a',
attribute_name=>'FIXED',
attribute_value=>'NO');
dbms_output.put_line(numplans);
end;
/ 3 4 5 6 7 8 9 10 11 12
1
PL/SQL procedure successfully completed.
declare
numplans pls_integer;
begin
numplans:=
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SYS_SQL_ec28978ecd0909c3',
plan_name=>'SYS_SQL_PLAN_cd0909c335032dee',
attribute_name=>'FIXED',
attribute_value=>'YES');
dbms_output.put_line(numplans);
end;
/
SQL> select sum(amount_sold) from sales where channel_id=1;
SUM(AMOUNT_SOLD)
----------------
785647882
Elapsed: 00:00:02.04
As you see, the new execution plan is really faster in this case – it was the right decision to allow its usage.But first, we saw a proof of it during the evolvement. So we did not trust the optimizer blindly. We had a strong control over the change process!
Addendum: Please see this excellent series of Articles regarding SQL Plan Baselines from the Optimizer Development Group:
http://optimizermagic.blogspot.com/2009/01/plan-regressions-got-you-down-sql-plan.html
http://optimizermagic.blogspot.com/2009/01/sql-plan-management-part-2-of-4-spm.html
http://optimizermagic.blogspot.com/2009/01/sql-plan-management-part-3-of-4.html
http://optimizermagic.blogspot.com/2009/02/sql-plan-management-part-4-of-4-user.html
Second Addendum: Migrate Stored Outlines to SQL Plan Baselines – Why & How
http://blogs.oracle.com/optimizer/entry/how_do_i_migrate_stored
Pickleball spielen 
#1 von Azhar Syed am Juli 21, 2014 - 18:44
Uwe Thanks for this post.. However I had a query regarding Baselines … What about a query that runs for both a very small amount of data and a very large amount of data.If the baseline is set for large amount of data then when we run for a small amount of data it will take minutes instead of seconds. The other way around (set the baseline for small and then run large) could make the large query go from hours to days. Is this possible and if it is so , is there anyway that this can be avoided ?