Store a good exec plan with OUTLINES

Main article: http://www.oracle-base.com/articles/misc/Outlines.php
Metalink Doc ID: 463288.1

basics:
ALTER SESSION SET create_stored_outlines=TRUE; -- will make each executed SQL saved in the outlines

CREATE ANY OUTLINE -- privilege is needed for storing the outlines

Use CREATE OUTLINE statement or DBMS_OUTLN package.

Note: in my case DBMS_OUTLN.CREATE_OUTLINE only worked from under user SYS
select sql_text, sql_id, plan_hash_value, HASH_VALUE, CHILD_NUMBER from v$sql where sql_text like '&sqltext';
alter session set create_stored_outlines = true; -- <- to workaround a bug, fixed in 10.2.0.4
exec dbms_outln.CREATE_OUTLINE(1850253776,0,'XXX_OUTLINES');
to check your outlines:
select name,owner,category,sql_text,used,enabled from dba_outlines;
if used column shows 'UNUSED', then check that:
query_rewrite_enabled=TRUE;
use_stored_outlines=[TRUE|CATEGORY];

Note: a huge drawback is that use_stored_outlines is not a spfile parameter. It can only be set by running ALTER SESSION or ALTER SYSTEM command.

To set the parameter forever a startup trigger must be created:
create or replace trigger enable_outlines_trig
after startup on database
begin
execute immediate('alter system set use_stored_outlines=XXX_OUTLINES');
end;

How to Transfer Stored Outlines from One Database to Another (9i and above):
exp system/ file=myoutln.dmp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) query="where category='MYCAT'" statistics=none
imp system/ file=myoutln.dmp full=y ignore=y