Stored Outlines Migration Through SPM

In earlier releases of Oracle Database, stored outlines were the only mechanism available to preserve an execution plan. With stored outlines, only one plan could be used for a given SQL statement, and no plan evolution was possible. Stored outlines were deprecated in Oracle Database 11g, and it's strongly recommended that any existing stored outlines be migrated to SPM.

Stored outlines can be migrated to SQL plan baselines using the PL/SQL procedure DBMS_SPM.MIGRATE_STORED_OUTLINE. You can specify stored outlines to be migrated based on their name, category, or associated SQL text, or you can simply migrate all stored outlines in the system.

Example of migrating stored outlines to SQL plan baselines:

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

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;
/