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