Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



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.


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:






The above text is an excerpt from:

Using Oracle SQL Stored Outlines & Optimizer Plan Stability
ISBN 0-9740716-8-4

by Mike Ault

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.