Translate

Search This Blog

trick with oracle stored outlines in getting good execution plan

Outlines are deprecated in 11g and may be no longer in existence in future but sometimes when there is no freedom to change the application code and query requiring tuning is performing well on other system then creating outline on good database and copying this outline data to database where query is requiring tuning can be great option.

Below  are steps: 
#1: CREATE OR REPLACE OUTLINE OUTLINE_NAME
FOR CATEGORY  CATEGORY_NAME
ON QUERY;

#2.  Next, export the outline from good database
exp userid=outln/ query="where category = ''" tables=(ol$,ol$hints)

#3 Then, import into the “bad” database, e.g.
imp userid=outln/ full=y ignore=yes

#4. If changing application code to enable use of outline is not possible which is most likely the case then use a trigger to enable the use of  outline for dbuser on bad database:

CREATE OR REPLACE TRIGGER xxxxxxxxx
AFTER LOGON ON DATABASE
DECLARE
BEGIN
IF USER = yyyyyyyyyyyyy
THEN
--
EXECUTE IMMEDIATE 'ALTER SESSION SET USE_STORED_OUTLINES = ;
--
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END;

Better alternate of exporting outline data:
Here we use the EXP utility with the QUERY parameter in order to selectively export the outline data for the Stored Outlines in category MYCAT.This allows us to transfer more than one Stored Outline in one go.

exp system/ file=myoutln.dmp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) query=\"where category='MYCAT'\"

The outline data exists in the OUTLN schema in three tables OL$, OL$HINTS, OL$NODES all of which need to be exported together.

On Unix we have to escape the special characters using a backslash, the command here would be:

exp system/ file=myoutln.dmp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) query=\"where category=\'MYCAT\'\"