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

 

 

   
  Making Permanent Changes to Tuned SQL

Oracle Tips by Burleson
 

Tuning the SQL statement is only half of the battle. Once tuned, the DBA must be able to make the tuning change permanent. For some changes, such as adding a new index, the change to the optimizer behavior is automatic. Other changes such as adding hints to SQL require more work to become permanent. There are two approaches to doing this:

  • Store an outline for the SQL (Oracle8i).

  • Locate the SQL source code and add the hint.

If we have a database where all of the SQL is encapsulated into packages, it is very easy for the DBA to extract the package and change the SQL. If the SQL arrives into Oracle from an external location such as a Pro*C program or a client/server call, we have the time-consuming task of locating the program that sent the SQL statement.

Using Stored Outlines for SQL

If we are using Oracle8i, we have another great option. Oracle8i provides a new package called outline that allows the DBA to store a ready-to-run execution plan for any SQL statement. This utility is called optimizer plan stability and has several features:

  • Parsing and execution time is reduced because Oracle will quickly grab and execute the stored outline for the SQL.

  • Tuning of SQL statements can easily be made permanent without locating the source code.

  • SQL from third-party products (e.g., SAP, PeopleSoft) can be tuned without touching the SQL source code.

Plan stability allows you to maintain the same execution plans for the same SQL statements, regardless of changes to the database such as reanalyzing tables, adding or deleting data, modifying a table's columns, constraints, or indexes, changing the system configuration, or even upgrading to a new version of the optimizer.

To use optimizer plan stability you must run the dbmsol.sql script from $ORACLE_HOME/rdbms/admin. When executed, a new Oracle user called OUTLN is created (with DBA privileges) and a package called outln_pkg is installed to provide procedures used for managing stored outlines.

Oracle provides the create outline statement to create a stored outline. The stored outline contains a set of attributes that the optimizer uses to create an execution plan. Stored outlines can also be created automatically by setting the init.ora parameter create_stored_outlines=TRUE.

For more details on using stored outlines, see your Oracle8i documentation.


This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

  
 

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. 

Hit Counter