oracle stored outline and sql plan baseline, 11gR2,sql plan baseline overrides the stored outline

SQL PLAN BASELINE is wonderful feature of Oracle 11g. SQL plan baselines can be created in many ways; created from sql tuning sets,from the cursor cache, from migrating stored outline.This post shows- how to create stored outline, migrate outline to sql plan baseline, check use of sql plan baseline,check sql plan base line overrides stored outline

One additional point :Many people claims that before version oracle 11g sql profile is good option to gain plan stability but this is not true as sql profiles contains only statistical information like opt_estimate information to drive optimizer towards good plan but over time if statistical composition changes profile may no longer be helping query and execution plan may be sub optimal. In this case stored outlines are viable option from oracle 8i to gain plan stability before oracle 11g. From the evolution of sql plan baseline they are the best options. Further one great compatibility of sql plan baseline with stored outline is they can be created from stored outline using migrate_stored_outline procedure of package dbms_spm.

SQL> conn / as sysdba
Connected.

SQL> grant CREATE ANY OUTLINE to scott;
Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL> create outline ol1 for category categ1 on select * from emp where deptno=10

Outline created.

SQL> select * from user_outlines;

NAME CATEGORY USED TIMESTAMP SIGNATURE COMPATIBLE ENABLED FORMAT

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

MIGRATED

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

OL1 CATEG1 UNUSED 19-JUL-13 mp where deptno=10 0B6183E104E1FA

NOT-MIGRATED

SQL> select name,category,sql_text from user_outlines;

NAME CATEGORY SQL_TEXT

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

OL1 CATEG1 select * from emp

SQL> var mig clob

SQL> exec :mig:=dbms_spm.migrate_stored_outline(attribute_name=>-

> 'outline_name',attribute_value=>'OL1',fixed=>'NO')

BEGIN :mig:=dbms_spm.migrate_stored_outline(attribute_name=> 'outline_name',attribute_value=>'OL 1',fixed=>'NO'); END;
*
ERROR at line 1:

ORA-38171: Insufficient privileges for SQL management object operation

ORA-06512: at "SYS.DBMS_SPM", line 3416

ORA-06512: at line 1

SQL> conn / as sysdba
Connected.

SQL> grant execute on dbms_spm to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL> exec :mig:=dbms_spm.migrate_stored_outline(attribute_name=>-

> 'outline_name',attribute_value=>'OL1',fixed=>'NO')

BEGIN :mig:=dbms_spm.migrate_stored_outline(attribute_name=> 'outline_name',attribute_value=>'OL 1',fixed=>'NO'); END;
*
ERROR at line 1:
ORA-38171: Insufficient privileges for SQL management object operation
ORA-06512: at "SYS.DBMS_SPM", line 3416
ORA-06512: at line 1

SQL> REM it shows granting execute privilege on dbms_spm is not enough

SQL> conn / as sysdba
Connected.

SQL> grant ADMINISTER SQL MANAGEMENT OBJECT to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL> exec :mig:=dbms_spm.migrate_stored_outline(attribute_name=>-
> 'outline_name',attribute_value=>'OL1',fixed=>'NO')

BEGIN :mig:=dbms_spm.migrate_stored_outline(attribute_name=> 'outline_name',attribute_value=>'OL 1',fixed=>'NO'); END;
*
ERROR at line 1:
ORA-18007: ALTER ANY OUTLINE privilege is required for this operation
ORA-06512: at "SYS.DBMS_SPM", line 3416
ORA-06512: at line 1

SQL> conn / as sysdba
Connected.

SQL> grant ALTER ANY OUTLINE to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.

SQL> exec :mig:=dbms_spm.migrate_stored_outline(attribute_name=>-

> 'outline_name',attribute_value=>'OL1',fixed=>'NO')

PL/SQL procedure successfully completed.
SQL> select name,sql_text,migrated from user_outlines;

NAME SQL_TEXT M IGRATED

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

OL1 select * from emp where deptno=10 M IGRATED

SQL> conn / as sysdba
Connected.

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

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC

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

SYS_SQL_5b35ebd3a9ade57e select * from emp where deptno=10 OL1 STORED-OUTLINE YES YES

SQL> conn scott/tiger
Connected.

SQL> set autot on

SQL> select * from emp where deptno=10;

Execution Plan

----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
Note
-----
- SQL plan baseline "OL1" used for this statement


SQL> Now we check sql plan base line overrides stored outline

SQL> alter session set use_stored_outlines=categ1;
Session altered.

SQL> select * from emp where deptno=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
Note
-----
- SQL plan baseline "OL1" used for this statement

Here we see inspite of enabling of use of stored outline of category categ1 execution plan is showing sql plan base line has been used. So we can say 11gR2 sql plan base overrides the stored outline

Stored Outline Quick Reference [ID 67536.1]

How to Use SQL Plan Management (SPM) - Example Usage [ID 456518.1]