If you have been using stored outlines for plan stability in versions prior to Oracle 11g, you should migrate them to use SQL Plan Baselines instead. Stored outlines is, according to oracle, "a legacy technique for providing plan stability".
My database had a number of stored outlines:
SELECT OWNER, CATEGORY, USED, MIGRATED,COUNT(*)
FROM DBA_OUTLINES
GROUP BY OWNER,CATEGORY,USED,MIGRATED
ORDER BY MIGRATED;
| OWNER | CATEGORY | USED | MIGRATED | COUNT(*) |
| USER1 | DEFAULT | USED | MIGRATED | 3 |
| USER2 | DEFAULT | USED | MIGRATED | 1 |
| USER3 | DEFAULT | USED | NOT-MIGRATED | 7 |
| USER1 | DEFAULT | USED | NOT-MIGRATED | 7 |
| USER4 | DEFAULT | USED | NOT-MIGRATED | 1 |
| USER2 | DEFAULT | USED | NOT-MIGRATED | 36 |
I created the following pl/sql to convert them to SQL Plan Baselines using the package DBMS_SPM.MIGRATE_STORED_OUTLINE:
SET TRIMSPOOL ON
SET LINES 200
SET PAGES 200
ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; <-- to get English messages during execution
SPOOL CONVERT_OUTLINES.LOG
SET SERVEROUTPUT ON
DECLARE
L_CLOB CLOB; -- will display the resulting report
CURSOR C1 IS
SELECT OWNER,NAME
FROM DBA_OUTLINES
WHERE MIGRATED = 'NOT-MIGRATED';
C1_REC C1%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE( 1000000 );
IF NOT C1%ISOPEN THEN
OPEN C1;
END IF;
LOOP
FETCH C1 INTO C1_REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Now converting: ' || C1_REC.OWNER || '.' || C1_REC.NAME);
L_CLOB := DBMS_SPM.MIGRATE_STORED_OUTLINE( ATTRIBUTE_NAME=>'OUTLINE_NAME', ATTRIBUTE_VALUE=>C1_REC.NAME, FIXED=>'NO');
DBMS_OUTPUT.PUT_LINE( L_CLOB );
END LOOP;
CLOSE C1;
END;
/
EXIT
The resulting log files shows that several of the stored outlines could not be converted:
Now converting: USER2.SYS_OUTLINE_11021513055564321
-------------------------------------------------------------------------------
Migrate Stored Outline to SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Summary:
--------
Number of stored outlines to be migrated: 1
Stored outlines migrated successfully: 0
Stored outlines failed to be migrated: 1
Summary of Reasons for failure:
-------------------------------
Number of invalid stored outlines: 1
Details on stored outlines not migrated or name changes during migration:
-------------------------------------------------------------------------
* Notes on name change:
* New SQL plan baselines are assigned the same names as their original stored
* outlines. If a stored outline has the same name as an existing
* SQL plan baseline, a system generated name is used for the new
* SQL plan baseline.
I then checked with the developers. It turned out that the outlines that didn't convert properly were remnants from the database when it was totally different laid out - the schemas had by now diverged and I could simply ignore these obsolete outlines.
So the last step was simply to generate a drop-script for the non-migrated outlines and then execute these:
SELECT 'DROP OUTLINE ' || NAME || ';'
FROM DBA_OUTLINES
WHERE MIGRATED = 'NOT-MIGRATED';
