||Oracle Tips by Burleson
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.
Let's call the SQL statement to optimize 'ORIGINALSQL'
Find the hints to optimize the original SQL statement. Let's
call the same SQL statement with hints 'HINTSQL'
Create the OUTLINE for ORIGINALSQL
Create the OUTLINE for HINTSQL
Exchange the OUTLINE plan between the two OUTLINES
Drop the OUTLINE for HINTSQL
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
Under the sys or outln user, simply exchange outline names used in
step 2 and 3:
WHERE OL_NAME IN ('HINTSQL','ORIGINALSQL');
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA