Using Stored Outlines for Upgrade to 10g

First give grant to create stored outlines for users

CONN sys/password AS SYSDBA


-- Give grant to all app users

SELECT 'GRANT CREATE ANY OUTLINE TO ' || username || ';'
FROM dba_users
WHERE username NOT IN ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'MASTER')
AND ( username not LIKE 'ZU%' )
AND ( username not LIKE 'U0%' );

GRANT CREATE ANY OUTLINE TO APPUSER;
GRANT EXECUTE_CATALOG_ROLE TO APPUSER;


-- Start to Collect outlines

alter system set CREATE_STORED_OUTLINES=OLDPLAN;

-- Check Outlines

-- Check Database performance

select name, sql_text, category from dba_outlines;

select count(*) from dba_outlines where category='OLDPLAN'

-- Stop Collecting Outlines

alter system set CREATE_STORED_OUTLINES=false;

--------------------------------------------------------------------------------

-- After collecting outlines upgrade the database
-- set USE_STORED_OUTLINES to correct execution plan


Alter system set USE_STORED_OUTLINES=FIXPLAN;

-- And create temporary tables for queries

Create table match_outlines (
owner varchar2(30),
name varchar2(30),
sql_text clob);

Insert into match_outlines
Select owner,name, to_lob(sql_text)
From dba_outlines ;

-- if there is any problem find the Query and change the execution plan with outlines.

-- Get the stored outline name

select name,sql_text from master.match_outlines where upper(sql_text) like '%Part of the SQL%';

alter outline OUTLINE_NAME change category to FIXPLAN;

-- Or fix the plan by Hash_value if you know it.

BEGIN
DBMS_OUTLN.create_outline(
hash_value => 3909283366,
child_number => 0,
category => 'FIXPLAN');
END;
/




----------- SOME INFORMATION ABOUT OUTLINES ------------------

-- views --
select * from dba_outlines;
select * from dba_outline_hints;
select * from OL$HINTS;

-- packages that you can use

DBMS_OUTLINE.*
DBMS_OUTLINE_EDIT.*


-- Create an outline for a specific SQL statement by CREATE OUTLINE command
-- white spaces are not important
-- But the name of the bind wariables are important
- Capital or small letters are not important


CREATE OUTLINE my_outline FOR CATEGORY oldplan
ON SELECT col1,col2,col4 FROM tablename WHERE col3='BB' and col4= :b1 ;


reading about the subject

http://www.oracle-base.com/articles/misc/Outlines.php