Load SQL Baselines

1. Create a SQL Tuning set
BEGIN
dbms_sqltune.create_sqlset(sqlset_name => 'EOD_856CDNTC4XDRG', description =>'Performance issue eod', sqlset_owner =>'SYS');
END;
/
2. Load the SQL into the set
DECLARE sqlset_cur dbms_sqltune.sqlset_cursor; bf VARCHAR2(37);
BEGIN
bf := 'UPPER(SQL_ID) = ''856CDNTC4XDRG'' ';
OPEN sqlset_cur FOR
SELECT VALUE(P) FROM TABLE(
dbms_sqltune.select_workload_repository( '7485', '7653', bf, NULL, NULL, NULL, NULL, 1, NULL, 'ALL')
) P;
dbms_sqltune.load_sqlset(
sqlset_name=>'EOD_856CDNTC4XDRG'
, populate_cursor=>sqlset_cur
, load_option => 'MERGE'
, update_option => 'ACCUMULATE'
, sqlset_owner=>'SYS');
END;
/

3. Check the SQL Tuning set
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET( 'EOD_856CDNTC4XDRG' ) );

4. Load the BASELINE from SQL Tuning set
set serveroutput on
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
sqlset_name => 'EOD_856CDNTC4XDRG'
,sqlset_owner => 'SYS');
dbms_output.put_line ('loaded '||l_plans_loaded);
END;
/
5. Use EM Server->SQL Plan Control to manage the baselines

prefetch

Execution plan looking like this:

---------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=322 Card=319 Bytes=11803)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TARGET' (Cost=2 Card=1 Bytes=29)
2 1 NESTED LOOPS (Cost=322 Card=319 Bytes=11803)
3 2 TABLE ACCESS (FULL) OF 'DRIVER' (Cost=3 Card=319 Bytes=2552)
4 2 INDEX (RANGE SCAN) OF 'T_PK' (UNIQUE) (Cost=1 Card=1)


is using a feature called 'prefetch'.

More about it:
http://www.oracle.com/technology/products/oracle9i/daily/dec17.html
http://forums.oracle.com/forums/message.jspa?messageID=4236898

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