Saturday, September 18, 2010

How to use stored outline?

login as SYS

GRANT CREATE ANY OUTLINE TO <USER>;

ALTER SYSTEM SET USE_STORED_OUTLINES = FALSE;

BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE OUTLINE ORIGINALSQL1 ON <your problematic sql statement>';
END;
/

BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE OUTLINE HINTSQL1 ON <your statement with hints to address above statement>';
END;
/

UPDATE OUTLN.OL$HINTS SET OL_NAME=DECODE(OL_NAME,'HINTSQL1','ORIGINALSQL1','ORIGINALSQL1','HINTSQL1') WHERE OL_NAME IN ('ORIGINALSQL1','HINTSQL1');

COMMIT;

ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE;

set lines 132 pages 2000

explain plan for <your problematic sql statement>;

SELECT * FROM TABLE(dbms_xplan.display);

No comments:

Post a Comment