Amit Bansal

PLAN STABILITY

How many times you have noticed a query using Index X when you wanted it to use index Y or query performing Nested Loop join when Hash Join would have completed the query much faster.Or take a scenario when the application suddenly starts using wrong plan after database restart. To solve all these issues oracle provides feature called Plan stability. As per docs

“Plan stability prevents certain database environment changes from affecting the performance characteristics of applications. Such changes include changes in optimizer statistics, changes to the optimizer mode settings, and changes to parameters affecting the sizes of memory structures, such as SORT_AREA_SIZE and BITMAP_MERGE_AREA_SIZE. “

Oracle provides following ways to ensure it

1) Stored outlines
2) Sql Profiles
3)Sql Plan Management

Stored outlines

A stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
You can read about outlines at ORACLE-BASE

Sql Profiles

As per Jonathan Lewis post,

SQL Profile consists of a stored SQL statement, and a set of hints that will be brought into play when that SQL has to be optimised. Unlike Stored Outlines, the hints for SQL Profiles do not attempt to dictate execution mechanisms directly. Instead they supply arithmetical correction factors to the optimizer as it does its arithemetic as, even with a 100% sample size, it is still possible for the optimizer to misinterpret your statistics and produce an unsuitable execution path.

In principle, if the data distributions do not change, then a stored profile will ensure that the optimizer “understands” your data and does the right thing – even when the data volume changes.

Difference between stored outline and stored profiles

Quoting Tom kyte post 

Stored outlines are a set of hints that say “use this index, do this table first, do that next, use this access path, perform this filter then that filter”….

Sql profiles are more like extended statistics – they are the result of “analyzing a query”, the information provided to the optimizer is not HOW to perform the query – but rather better information about how many rows will flow out of a step in the plan, how selective something is.

Say you have a query, you generate a stored outline for it. You now add an index to the underlying table. This index would be GREAT for the query. A stored outline won’t use it, the stored outline says “use this index – query that table – then do this”. Since the GREAT index did not exist when the outline was generated – it won’t be used.

Say you have a query, you generate a profile for it. You now add an index to the underlying table. This index would be GREAT for the query. A profile can use it – since the profile is just more information for the CBO – better cardinality estimates.

So Stored outlines will ensure that whatever plan you fix, it will be used whereas a Sql profile might use different plan if we see some change in data distribution or new indexes.

There are two ways of generating sql profiles

1)Using Sql Tuning Advisor – A lot has been written about this, so I won’t discuss this.
2)Manually – This might be a surprising for lot of people as they might not be aware that we can generate a sql profile manually.I will be discussing this in detail below

Oracle provides a script coe_xfr_sql_profile.sql as part of Note 215187.1 – SQLT (SQLTXPLAIN) – Tool That Helps To Diagnose SQL Statements Performing Poorly which can be used to manually create Sql profiles.

It’s usage is pretty simple.

a) You need to execute the script and it will prompt you to pass sql_id for statement.
b)Then it will scan AWR repository and return Plan hash value for all possible execution plans for query along with average elapsed time.
c)You need to select the plan hash value which you think corresponds to good plan.
d)This will generate a script in current working directory which can be run as sys user and it will create the sql profile

This script is also useful if we wish to move the good plan from Non production environment to Production environment. To give a demo, I will create a table PLAN_STABILITY and index IDX_PLAN_STABI_OWNER on owner column.

SQL> create table plan_stability as select * from dba_objects;
Table created.

SQL> insert into plan_stability select * from dba_objects;
75048 rows created.

SQL> insert into plan_stability select * from plan_stability;
300192 rows created.
..
..
SQL> insert into plan_stability select * from plan_stability;
1200768 rows created.
SQL> commit;

22:15:05 SQL> create index IDX_PLAN_STABI_OWNER on plan_stability(owner);

Index created.
SQL> select count(*) from plan_stability;

COUNT(*)
----------
2401536

Let’s query the table for count of objects owned by SYS

SQL> select count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 0
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 3082746383

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 57 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| IDX_PLAN_STABI_OWNER | 23092 | 135K| 57 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS')

We see that we are using index IDX_PLAN_STABI_OWNER. Since data is less , Index access is best approach.But for this demo I want to force a Full table scan . Let’s generate a plan with FTS for plan_stability. To do this we can use  Invisible indexes (11g feature)  so that optimizer ignores index.

SQL> alter index IDX_PLAN_STABI_OWNER invisible;

Index altered.
SQL> select count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 0
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')
19 rows selected.
--Let's make it index visible again

SQL> alter index IDX_PLAN_STABI_OWNER visible;

Index altered.

We will have to take manual AWR snapshots before and end of the test, so that sql plans goes into AWR repository . This is necessary as coe_xfr_profile.sql will look at AWR data for plan history for a sql. Now we run coe_xfr_profile.sql and it will prompt us for sql_id. On passing the sql_id, it reports that there are two plans. We choose plan 363261562 (having higher elapsed time) as we wish to force a Full table scan

SQL>@coe_xfr_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: g3wubsadyrt37
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3082746383 .064
363261562 .184

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 363261562

Values passed:
~~~~~~~~~~~~~
SQL_ID : "g3wubsadyrt37"
PLAN_HASH_VALUE: "363261562"
Execute coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql
on TARGET system in order to create a custom SQL Profile
with plan 363261562 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.

This has generated a file coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql in same directory which can be run to create the sql profile.Note that script has force_match => FALSE which means that if sql varies in literal, sql_profile will not work. To force it ,we need to set the paramter to force.

SQL>@coe_xfr_sql_profile_g3wubsadyrt37_363261562
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql 11.4.1.4 2011/12/18 csierra $
SQL>REM
SQL>REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID g3wubsadyrt37 based on plan hash
SQL>REM value 363261562.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_g3wubsadyrt37_363261562');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 BEGIN
5 sql_txt := q'[
6 select count(*) from plan_stability where owner='SYS'
7 ]';
8 h := SYS.SQLPROF_ATTR(
9 q'[BEGIN_OUTLINE_DATA]',
10 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
11 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
12 q'[DB_VERSION('11.2.0.3')]',
13 q'[OPT_PARAM('_b_tree_bitmap_plans' 'false')]',
14 q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
15 q'[ALL_ROWS]',
16 q'[OUTLINE_LEAF(@"SEL$1")]',
17 q'[FULL(@"SEL$1" "PLAN_STABILITY"@"SEL$1")]',
18 q'[END_OUTLINE_DATA]');
19 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
20 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
21 sql_text => sql_txt,
22 profile => h,
23 name => 'coe_g3wubsadyrt37_363261562',
24 description => 'coe g3wubsadyrt37 363261562 '||:signature||'',
25 category => 'DEFAULT',
26 validate => TRUE,
27 replace => TRUE,
28 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
29 END;
30 /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

SIGNATURE
---------------------
4782703451567619555
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_g3wubsadyrt37_363261562 completed

Let’s verify the plan again now

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 1
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

Note
-----
- SQL profile coe_g3wubsadyrt37_363261562 used for this statement

In the Note section we can see that Sql Profile “coe_g3wubsadyrt37_363261562” has been used for this statement.

Suppose we now have to create a sql profile, when we don’t have good plan in AWR, then we will have to do a hack. There are two ways to do it

a)You can use coe_xfr_profile.sql to do same. You will have to run the script twice, one for original statement and secondly for hinted statement. Once done you need to copy the values corresponding to h := SYS.SQLPROF_ATTR from hinted sql and replace it in original script.e.g

I am creating a sql profile for following hinted statement

SQL> select /*+ full(plan_stability) */ count(*) from plan_stability where owner='SYS';

  COUNT(*)
----------
   1020384

 SQL> select /*+ full(plan_stability) */ count(*) from plan_stability where owner='SYS';

  COUNT(*)
----------
   1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID	9p07a64q8fgrn, child number 0
-------------------------------------
select /*+ full(plan_stability) */ count(*) from plan_stability where
owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id  | Operation	   | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		    |	    |	    |  2476 (100)|	    |
|   1 |  SORT AGGREGATE    |		    |	  1 |	  6 |		 |	    |
|*  2 |   TABLE ACCESS FULL| PLAN_STABILITY | 23092 |	135K|  2476   (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

--Creating Sql profile now

SQL>@coe_xfr_profile.sql 9p07a64q8fgrn 363261562

Parameter 1:
SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      363261562        .193

Parameter 2:
PLAN_HASH_VALUE (required)

Values passed:
~~~~~~~~~~~~~
SQL_ID	       : "9p07a64q8fgrn"
PLAN_HASH_VALUE: "363261562"

Execute coe_xfr_sql_profile_9p07a64q8fgrn_363261562.sql
on TARGET system in order to create a custom SQL Profile
with plan 363261562 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

Now we create sql profile script for original statement using plan_hash_value corresponding to index access

20:47:52 SQL> @coe_xfr_profile.sql g3wubsadyrt37 3082746383

Parameter 1:
SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3082746383        .084
      363261562        .184

Parameter 2:
PLAN_HASH_VALUE (required)

Values passed:
~~~~~~~~~~~~~
SQL_ID	       : "g3wubsadyrt37"
PLAN_HASH_VALUE: "3082746383"

Execute coe_xfr_sql_profile_g3wubsadyrt37_3082746383.sql
on TARGET system in order to create a custom SQL Profile
with plan 3082746383 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

Now copy following section from coe_xfr_sql_profile_9p07a64q8fgrn_363261562.sql and replace in the coe_xfr_sql_profile_g3wubsadyrt37_3082746383.sql. Also change the name from coe_g3wubsadyrt37_3082746383 to coe_g3wubsadyrt37_363261562

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
q'[DB_VERSION('11.2.0.3')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "PLAN_STABILITY"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

This has again created a sql profile with FTS plan.

SQL> select name,SIGNATURE,SQL_TEXT,FORCE_MATCHING,STATUS from dba_sql_profiles;

NAME						    SIGNATURE SQL_TEXT								     FOR STATUS
------------------------------ ------------------------------ ---------------------------------------------------------------------- --- --------
coe_g3wubsadyrt37_363261562		  4782703451567619555 select count(*) from plan_stability where owner='SYS'		     NO  ENABLED

b) You can use scripts provided by Kerry Osborne in following article
http://kerryosborne.oracle-guy.com/2009/10/how-to-attach-a-sql-profile-to-a-different-statement-take-2/

SQL Plan Management

Let’s use the 11g feature SQL Plan Management to implement plan stability.SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient.
The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system. We can capture plans

a)Automatically – We can use OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true and capture the sql baseline plans. This is pretty useful if you are upgrading database as you can capture good plans and then upgrade the database without worrying about plan change. e.g Post 11g upgrade from 10.2.0.4, set optimizer_features_enable=10.2.0.4 and capture all the plans.Once done you can set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false and also set optimizer_features_enable back to 11.1/11.2. You can then enable/disable the plans or mark them Fixed. Fixed plans get preference over non-fixed plans.

Note that only plans for repeatable statements are stored in the SPM

b)Manually – We can use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE and DBMS_SPM.LOAD_PLANS_FROM_SQLSET to load plans from cursor cache and Sqlset respectively.

Loading plan from Cursor Cache

We will discuss fucntion DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to load plans from the cursor cache.In our test,we need to fix plan 363261562 for sql_id g3wubsadyrt37.Since the plan is available in cursor cache,  we will use following syntax

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => 'g3wubsadyrt37',
PLAN_HASH_VALUE =>363261562,
FIXED =>'YES');
dbms_output.put_line('Value is '||my_plans);
END;
/

It will return number of plans loaded.Let’s verify baseline by querying dba_sql_plan_baselines

SQL>select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3621540b0 YES YES YES

We can plan corresponding to this baseline

SQL> select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SQL_425f937308b8fde3',
format=>'basic')); 

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------

SQL handle: SQL_425f937308b8fde3
SQL text: select count(*) from plan_stability where owner='SYS'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_44rwmfc4bjzg3621540b0	  Plan id: 1645559984
Enabled: YES	 Fixed: NO	Accepted: YES	  Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 363261562

---------------------------------------------
| Id  | Operation	   | Name	    |
---------------------------------------------
|   0 | SELECT STATEMENT   |		    |
|   1 |  SORT AGGREGATE    |		    |
|   2 |   TABLE ACCESS FULL| PLAN_STABILITY |
---------------------------------------------

20 rows selected.

We can check if our original query is using  the plan

SQL> select count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 1
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

Note
-----
- SQL plan baseline SQL_PLAN_44rwmfc4bjzg3621540b0 used for this statement

We see from Note section  that SQL plan baseline SQL_PLAN_44rwmfc4bjzg3621540b0 has been used for the statement.

Loading plan from AWR/SQL Tuning set

If you know that good plan of the query is available in AWR, then you can use dbms_spm.load_plans_from_sqlset. To do this we need to first create a sql tuning set. This can be done by using DBMS_SQLTUNE

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'sqlset_g3wubsadyrt37',
description => 'Example to show dbms_spm.load_plans_from_sqlset');
END;
/

Let’s load the sql_id g3wubsadyrt37 in this Sql tuning set from AWR. We need to pass begin_snap and end_snap for SQL. We can also use basic_filter clause to restrict this sqlset to only one particular sql_id.

DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
BEGIN_SNAP => 641,
END_SNAP => 667,
BASIC_FILTER => 'sql_id = ''g3wubsadyrt37''',
ATTRIBUTE_LIST =>'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'sqlset_g3wubsadyrt37',
populate_cursor => baseline_cursor);
END;
/

Lets verify that both plans are loaded in the sqlset

SELECT SQL_ID,PLAN_HASH_VALUE FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(
'sqlset_g3wubsadyrt37'));

SQL_ID PLAN_HASH_VALUE
------------- ---------------
g3wubsadyrt37 363261562
g3wubsadyrt37 3082746383

Next we create sql plan baseline using DBMS_SPM.LOAD_PLANS_FROM_SQLSET

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
SQLSET_NAME => 'sqlset_g3wubsadyrt37'
);
dbms_output.put_line('Value is '||my_plans);
END;
/

We now see that two plans are loaded

SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3621540b0 YES YES NO
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3ec110d89 YES YES NO

We can disable the SQL_PLAN_44rwmfc4bjzg3ec110d89 as we only want FTS plan

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle =>'SQL_425f937308b8fde3',
PLAN_NAME =>'SQL_PLAN_44rwmfc4bjzg3ec110d89',
attribute_name =>'enabled',
attribute_value=>'NO');
dbms_output.put_line('Value is '||my_plans);
END;
/

Above statement will mark the index plan as disabled and our query will use only FTS plan.

To drop the baselines, we can use following syntax

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_425f937308b8fde3'
);
dbms_output.put_line('Value is '||my_plans);
END;
/

Till now we have  discussed  scenarios when we have good plan in cursor cache/AWR. Suppose there is no good plan available but we can generate a good plan using hints. With SPM we can easily transfer profile from hinted sql to our original statement. Let’s see it in action

First we need to create a baseline with existing plan for query. This is required as it would generate a sql_handle which can be used to assign a plan. We are using PLAN_HASH_VALUE =>3082746383 i.e Indexed access path to create the sql baseline

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => 'g3wubsadyrt37',
PLAN_HASH_VALUE =>3082746383
);
dbms_output.put_line('Value is '||my_plans);
END;
/

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

SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ------------------------------ --- --- --- --------------------------------------------------------------------------------
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3ec110d89 YES YES NO select count(*) from plan_stability where owner='SYS'

Querying dba_sql_plan_baselines we get sql_handle as SQL_425f937308b8fde3.Now let’s create a plan for query using hints.

SQL> select /*+ full(plan_stability) */ count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select /*+ full(plan_stability) */ count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID 9p07a64q8fgrn, child number 0
-------------------------------------
select /*+ full(plan_stability) */ count(*) from plan_stability where
owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

We need to now copy plan corresponding to sql_id=9p07a64q8fgrn and plan_hash_value=363261562. This can be done by using sql_handle for original statement and using sql_id/plan_hash_value of hinted statement

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '9p07a64q8fgrn',
sql_handle =>'SQL_425f937308b8fde3',
PLAN_HASH_VALUE =>363261562,
FIXED =>'YES'
);
dbms_output.put_line('Value is '||my_plans);
END;
/

Note that I have used FIXED=>YES argument which would mark this plan as Fixed.A fixed plan takes precedence over a non-fixed plan. We can drop the old baseline plan now by passing sql_handle and plan_name. If you use only sql_handle, it will drop both the plans

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_425f937308b8fde3',
PLAN_NAME =>'SQL_PLAN_44rwmfc4bjzg3ec110d89');
dbms_output.put_line('Value is '||my_plans);
END;
/

Instead of dropping plan, you can also disable the above plan using DBMS_SPM.ALTER_SQL_PLAN_BASELINE

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle =>'SQL_425f937308b8fde3',
PLAN_NAME =>'SQL_PLAN_44rwmfc4bjzg3ec110d89',
attribute_name =>'enabled',
attribute_value=>'NO');
dbms_output.put_line('Value is '||my_plans);
END;
/

We now have two plans in baseline but only enabled plan SQL_PLAN_44rwmfc4bjzg3621540b0 will be used by the query.

Note: Whenever we create/drop sql baseline plan, sql is purged from cursor cache and we perform a hard parse.

While testing this , I found that if I create a alias for table , SQL Plan baseline is not working

SQL> select /*+ full(a) */ count(*) from plan_stability a where owner='SYS';

COUNT(*)
----------
1020384

Elapsed: 00:00:00.18
select /*+ full(a) */ count(*) from plan_stability a where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID 85hj3c8570fdu, child number 0
-------------------------------------
select /*+ full(a) */ count(*) from plan_stability a where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

SQL>DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '85hj3c8570fdu',
sql_handle =>'SQL_425f937308b8fde3',
PLAN_HASH_VALUE =>363261562,
FIXED =>'YES'
);
dbms_output.put_line('Value is '||my_plans);
END;
/

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

SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ------------------------------ --- --- --- --------------------------------------------------------------------------------
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3621540b0 YES YES YES select count(*) from plan_stability where owner='SYS'

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 1
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

We can check baseline information using dbms_xplan.display_sql_plan_baseline function.Let’s see why we didnt use FTS even though profile was successfully created

SQL> select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SQL_425f937308b8fde3',
format=>'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------

SQL handle: SQL_425f937308b8fde3
SQL text: select count(*) from plan_stability where owner='SYS'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_44rwmfc4bjzg3621540b0 Plan id: 1645559984
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 3082746383

--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX RANGE SCAN| IDX_PLAN_STABI_OWNER |
--------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_44rwmfc4bjzg3ec110d89 Plan id: 3960540553
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3082746383

--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX RANGE SCAN| IDX_PLAN_STABI_OWNER |
--------------------------------------------------

From above we see that SQL_PLAN_44rwmfc4bjzg3621540b0 is showing plan which uses Index and not FTS.I am not sure if this is correct behavior or a bug. One reason could be that  same alias is not present in parent table.

I believe Sql Plan Management is superior to SQL Profile and outlines as it allows you to have multiple plans for a given sql statement. Also a nightly maintenance job runs automatic SQL tuning task and targets high-load SQL statements.If it thinks that there is better plan , then it adds that SQL Plan baseline to history. This is called Evolving Sql Plan Baselines.

Christian Antognini has written a good article on Automatic Evolution of SQL Plan Baselines which explains this feature.

Licensing

As per Optimizer blog article  ,DBMS_SPM is not licensed until we are loading plans from SQL Tuning set (which requires tuning pack).SQL profiles are  licensed and require diagnostic and tuning pack.