Showing posts with label oracle stored outline example. Show all posts
Showing posts with label oracle stored outline example. Show all posts

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);