Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Replacing a Non-Hinted Outline
 

In many applications you are not allowed to change source code. This means that you cannot, or are unable to, add hints to force behavior. As we have seen OUTLINEs place hints into statements at the parse level, by using an OUTLINE you can force hints into statements. Let’s look at a technique to perform this operation.
 

Technique
 

Let's call the SQL statement to optimize 'ORIGINALSQL'

1. Find the hints to optimize the original SQL statement. Let's call the same SQL statement with hints 'HINTSQL'

2. Create the OUTLINE for ORIGINALSQL

3. Create the OUTLINE for HINTSQL

4. Exchange the OUTLINE plan between the two OUTLINES

5. Drop the OUTLINE for HINTSQL

6. Now the OUTLINE plan for ORIGINALSQL is the same as the execution plan of HINTSQL which uses HINTs.

Only the point 5 (Exchange the OUTLINE plan between the two OUTLINEs) is detailed below. The rest of the steps are covered in the bulk of the paper.

Under the sys or outln user, simply exchange outline names used in step 2 and 3:

UPDATE OUTLN.OL$HINTS

SET OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL')

WHERE OL_NAME IN ('HINTSQL','ORIGINALSQL');

Commit;

Download your Oracle scripts now: www.oracle-script.com The definitive Oracle Script collection for every Oracle professional DBA