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


 

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;


The above text is an excerpt from:

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

by Mike Ault
 

http://www.rampant-books.com/ebook_plan_stability.htm


Download your Oracle scripts now:

www.oracle-script.com

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.