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