To prepare for stored outline migration:

1. Connect SQL*Plus to the database with SYSDBA privileges or the EXECUTE privilege on the DBMS_SPM package.

2. Query the stored outlines in the database.

   The following example queries all stored outlines that have not been migrated to SQL plan baselines:

   SELECT NAME, CATEGORY, SQL_TEXT
   FROM   DBA_OUTLINES
   WHERE  MIGRATED = 'NOT-MIGRATED';
   

3. Determine which stored outlines meet the following prerequisites for migration eligibility:

   • The statement must not be a run-time INSERT AS SELECT statement.

   • The statement must not reference a remote object.

   • This statement must not be a private stored outline.

4. Decide whether to migrate all outlines, specified stored outlines, or outlines belonging to a specified outline category.

   If you do not decide to migrate all outlines, then identify the outlines or categories that you intend to migrate.

5. Decide whether the stored outlines migrated to SQL plan baselines use fixed plans or nonfixed plans:

   • Fixed plans

     A fixed plan is frozen. If a fixed plan is reproducible using the hints stored in plan baseline, then the optimizer always chooses the lowest-cost fixed plan baseline over plan baselines that are not fixed. Essentially, a fixed plan baseline acts as a stored outline with valid hints.

     A fixed plan is reproducible when the database can parse the statement based on the hints stored in the plan baseline and create a plan with the same plan hash value as the one in the plan baseline. If one of more of the hints become invalid, then the database may not be able to create a plan with the same plan hash value. In this case, the plan is nonreproducible.

     If a fixed plan cannot be reproduced when parsed using its hints, then the optimizer chooses a different plan, which can be either of the following:

     • Another plan for the SQL plan baseline

     • The current cost-based plan created by the optimizer

     In some cases, a performance regression occurs because of the different plan, requiring SQL tuning.

   • Nonfixed plans

     If a plan baseline does not contain fixed plans, then SQL Plan Management considers the plans equally when picking a plan for a SQL statement.

6. Before beginning the actual migration, ensure that the Oracle database meets the following prerequisites:

   • The database must be Enterprise Edition.

   • The database must be open and not in a suspended state.

   • The database must not be in restricted access (DBA), read-only, or migrate mode.

   • Oracle Call Interface (OCI) must be available.

The goals of this task are as follows:

• To allow SQL Plan Management to select from all plans in a plan baseline for a SQL statement instead of applying the same fixed plan after migration

• To allow the SQL plan baseline to evolve in the face of database changes by adding new plans to the baseline

Assumptions

This tutorial assumes the following:

• You migrate all outlines.

  To migrate specific outlines, use the DBMS_SPM.MIGRATE_STORED_OUTLINE function.

• You want the module names of the baselines to be identical to the category names of the migrated outlines.

• You do not want the SQL plans to be fixed.

  By default, generated plans are not fixed and SQL Plan Management considers all plans equally when picking a plan for a SQL statement. This situation permits the advanced feature of plan evolution to capture new plans for a SQL statement, verify their performance, and accept these new plans into the plan baseline.

To migrate stored outlines to SQL plan baselines:

1. Connect SQL*Plus to the database with the appropriate privileges.

2. Call PL/SQL function MIGRATE_STORED_OUTLINE.

   The following sample PL/SQL block migrates all stored outlines to fixed baselines:

   DECLARE
     my_report CLOB;
   BEGIN
     my_outlines := DBMS_SPM.MIGRATE_STORED_OUTLINE( 
                      attribute_name => 'all' );
   END;
   /

A fixed plan has higher priority over other plans for the same SQL statement. If a plan is fixed, then the plan baseline cannot be evolved. The database does not add new plans to a plan baseline that contains a fixed plan.

Assumptions

This tutorial assumes the following:

• You want to migrate only the stored outlines in the category named firstrow.

• You want the module names of the baselines to be identical to the category names of the migrated outlines.

To migrate stored outlines to plan baselines:

1. Connect SQL*Plus to the database with the appropriate privileges.

2. Call PL/SQL function MIGRATE_STORED_OUTLINE.

   The following sample PL/SQL block migrates stored outlines in the category firstrow to fixed baselines:

   DECLARE
     my_report CLOB;
   BEGIN
     my_outlines := DBMS_SPM.MIGRATE_STORED_OUTLINE( 
       attribute_name => 'category', 
       attribute_value => 'firstrow',
       fixed => 'YES' );
   END;
   /
   

   After migration, the SQL plan baselines is in module firstrow and category DEFAULT.

The goals of this task are as follows:

• To configure the database to use plan baselines instead of stored outlines for stored outlines that have been migrated to SQL plan baselines

• To create SQL plan baselines instead of stored outlines for future SQL statements

• To drop the stored outlines that have been migrated to SQL plan baselines

This section explains how to set initialization parameters relating to stored outlines and plan baselines. The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES and CREATE_STORED_OUTLINES initialization parameters determine how and when the database creates stored outlines and SQL plan baselines. Table 29-5 explains the interaction between these parameters.

The USE_STORED_OUTLINES session parameter (it is not an initialization parameter) and OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter determine how the database uses stored outlines and plan baselines. Table 29-6 explains how these parameters interact.

Assumptions

This tutorial assumes the following:

• You have completed the basic steps in the stored outline migration.

• Some stored outlines may have been created before Oracle Database 10g.

  Hints in releases before Oracle Database 10g use a local hint format. After migration, hints stored in a plan baseline use the global hints format introduced in Oracle Database 10g.

To place the database in the proper state after the migration:

1. Connect SQL*Plus to the database with the appropriate privileges, and then check that SQL plan baselines have been created as the result of migration.

   Ensure that the plans are enabled and accepted. For example, enter the following query (partial sample output included):

   SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE
   FROM   DBA_SQL_PLAN_BASELINES;
   
   SQL_HANDLE                PLAN_NAME  ORIGIN         ENA ACC FIX MODULE
   ------------------------- ---------- -------------- --- --- --- ------
   SYS_SQL_f44779f7089c8fab  STMT01     STORED-OUTLINE YES YES NO  DEFAULT
   .
   .
   .
   

2. Optionally, change the attributes of the SQL plan baselines.

   For example, the following statement changes the status of the baseline for the specified SQL statement to fixed:

   DECLARE
     v_cnt PLS_INTEGER;
   BEGIN 
     v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(               
                              sql_handle=>'SYS_SQL_f44779f7089c8fab', 
                              attribute_name=>'FIXED', 
                              attribute_value=>'NO');
     DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
   END;
   /
   

3. Check the status of the original stored outlines.

   For example, enter the following query (partial sample output included):

   SELECT NAME, OWNER, CATEGORY, USED, MIGRATED 
   FROM   DBA_OUTLINES
   ORDER BY NAME;
   
   NAME       OWNER      CATEGORY   USED   MIGRATED
   ---------- ---------- ---------- ------ ------------
   STMT01     SYS        DEFAULT    USED   MIGRATED
   STMT02     SYS        DEFAULT    USED   MIGRATED
   .
   .
   .
   

4. Drop all stored outlines that have been migrated to SQL plan baselines.

   For example, the following statements drops all stored outlines with status MIGRATED in DBA_OUTLINES:

   DECLARE
     v_cnt PLS_INTEGER;
   BEGIN 
     v_cnt := DBMS_SPM.DROP_MIGRATED_STORED_OUTLINE();
     DBMS_OUTPUT.PUT_LINE('Migrated stored outlines dropped: ' || v_cnt);
   END;
   /
   

5. Set initialization parameters so that:

   • When executing a SQL statement, the database creates plan baselines but does not create stored outlines.

   • The database only uses stored outlines when the equivalent SQL plan baselines do not exist.

   For example, the following SQL statements instruct the database to create SQL plan baselines instead of stored outlines when a SQL statement is executed. The example also instructs the database to apply a stored outline in category allrows or DEFAULT only if it exists and has not been migrated to a SQL plan baseline. In other cases, the database applies SQL plan baselines instead.

   ALTER SYSTEM 
     SET CREATE_STORED_OUTLINE = false SCOPE = BOTH;
   
   ALTER SYSTEM 
     SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true SCOPE = BOTH;
   
   ALTER SYSTEM 
      SET OPTIMIZER_USE_SQL_PLAN_BASELINES = true SCOPE = BOTH;
   
   ALTER SESSION
      SET USE_STORED_OUTLINES = allrows SCOPE = BOTH;