Theory on SQLPLAN BASELINES Using Stored Outlines

SQLPLAN BASELINES Using Stored Outlines

If you don’t have access to SQL Tuning Sets you can capture your existing execution plan using
Stored Outlines. There are two ways to capture Stored Outlines, you can either manually create
one for each SQL statement using the CREATE OUTLINE command or let Oracle
automatically create a Stored Outline for each SQL statement that is executed. Below are the
steps needed to let Oracle automatically create the Stored Outlines for you.

1. Start a new session and issue the following command to switch on the automatic
capture of a Stored Outline for each SQL statement that gets parsed from now on until
you explicitly turn it off.
SQL > alter system set CREATE_STORED_OUTLINES=OLDPLAN;

NOTE: Ensure that the user for which the Stored Outlines are to be created has the CREATE
ANY OUTLINE privilege. If they don’t the Stored Outlines will not be captured.

2. Now execute your workload either by running your application or manually issuing SQL
statements. NOTE: if you manually issue the SQL statements ensure you use the exact
SQL text used by the application, if it uses bind variables you will have to use them too.

3. Once you have executed your critical SQL statements you should turn off the automatic
capture by issuing the following command:
SQL > alter system set CREATE_STORED_OUTLINES=false;

4. To confirm you have captured the necessary Stored Outlines issue the following SQL
statement.
SQL> select name, sql_text, category from user_outlines;

NOTE: Each Stored Outline should be in the OLDPLAN category.

5. The actual Stored Outlines are stored in the OUTLN schema. Before you upgrade you
should export this schema as a backup.
exp outln/outln file=soutline.dmp owner=outln rows=y

6. After the upgrade to Oracle Database 11gR2, you can migrate stored outlines for
one or more SQL statements to SQL plan baselines using
DBMS_SPM.MIGRATE_STORED_OUTLINE or through Oracle Enterprise Manager (EM).
You can specify which stored outline(s) to be migrated based on outline name, SQL
text, or outline category, or migrate all stored outlines in the system to SQL plan
baselines.

SQL> variable report clob;
-- Migrate a single Stored Outline by name
SQL> exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE( -
attribute_name=>'OUTLINE_NAME', attribute_value =>
'stmt01');

-- Migrate all Stored Outlines
SQL> exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE( -
attribute_name=>'ALL');

Note: If you are not planning on doing an in-place upgrade you will have to move the STS, SQL
Trace files or Stored Outlines to the Oracle Database 11g system.

reference:  A practical session on migrating outline blog post - practical session migrating stored outlines to sql plan baseline

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]

creating stored outline from cached execution plan

Beginning from oracle 10gR2, The DBMS_OUTLN package contains a procedure called CREATE_OUTLINE that can be used to create a Stored Outline containing the execution plan of a Cursor in the Shared Pool using its Hash Value and Child Number.

PROCEDURE create_outline(hash_value IN NUMBER,
child_number IN NUMBER,
category IN VARCHAR2 DEFAULT 'DEFAULT');

In order to use the procedure, a user needs to be able to uniquely identify the Cursor for which
the outline is to be stored. In the following example, the text string: "OL_TEST" is used to do this
in order to store an outline of an illustrative query SELECT FROM V$SQL.

Example:

SQL> GRANT CREATE ANY OUTLINE TO SCOTT;

Grant succeeded.
SQL> GRANT EXECUTE_CATALOG_ROLE TO SCOTT;
select hash_value, child_number, sql_text
from v$sql
where sql_text like '%OL_TEST%';
HASH_VALUE CHILD_NUMBER SQL_TEXT
--------- ---------- -------
1556463076 0 select hash_value, child_number, sql_text from v$sql where sql_text like '%OL_TEST%'

SQL> alter session set create_stored_outlines = true; -- This step is to avoid Bug:5454975 fixed 10.2.0.4
Session altered.

SQL> exec dbms_outln.create_outline(1556463076,0);
SQL> select name from dba_outlines;
NAME
------------------------------
SYS_OUTLINE_07072313512890701

MORE on such outlines: example-

SQL> exec dbms_outln.create_outline(1556463076,0,'MYCAT');


PL/SQL procedure successfully completed.We can find the name of the newly created Stored Outline by querying e.g. DBA_OUTLINES. This will be a system-generated name at first, we can change it to a more human-friendly format using:

SQL> alter outline SYS_OUTLINE_07072313512890701 rename to MYOUTLN;

Note: prior to Oracle10g you will have to use one of the alternative methods i.e. manually create a Stored Outline using the CREATE OUTLINE command or capture it using ALTER SESSION SET CREATE_STORED_OUTLINES = .

If the Stored Outline already exists, we can change its category name to MYCAT as follows:

SQL> alter outline MYOUTLN change category to MYCAT;

trick with oracle stored outlines in getting good execution plan

Outlines are deprecated in 11g and may be no longer in existence in future but sometimes when there is no freedom to change the application code and query requiring tuning is performing well on other system then creating outline on good database and copying this outline data to database where query is requiring tuning can be great option.

Below  are steps: 
#1: CREATE OR REPLACE OUTLINE OUTLINE_NAME
FOR CATEGORY  CATEGORY_NAME
ON QUERY;

#2.  Next, export the outline from good database
exp userid=outln/ query="where category = ''" tables=(ol$,ol$hints)

#3 Then, import into the “bad” database, e.g.
imp userid=outln/ full=y ignore=yes

#4. If changing application code to enable use of outline is not possible which is most likely the case then use a trigger to enable the use of  outline for dbuser on bad database:

CREATE OR REPLACE TRIGGER xxxxxxxxx
AFTER LOGON ON DATABASE
DECLARE
BEGIN
IF USER = yyyyyyyyyyyyy
THEN
--
EXECUTE IMMEDIATE 'ALTER SESSION SET USE_STORED_OUTLINES = ;
--
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END;

Better alternate of exporting outline data:
Here we use the EXP utility with the QUERY parameter in order to selectively export the outline data for the Stored Outlines in category MYCAT.This allows us to transfer more than one Stored Outline in one go.

exp system/ file=myoutln.dmp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) query=\"where category='MYCAT'\"

The outline data exists in the OUTLN schema in three tables OL$, OL$HINTS, OL$NODES all of which need to be exported together.

On Unix we have to escape the special characters using a backslash, the command here would be:

exp system/ file=myoutln.dmp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) query=\"where category=\'MYCAT\'\"