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

 

 

   
 

The Philosophy Behind Optimizer Plan Stability
Oracle Tips by Burleson
 

The use of stored outlines depends heavily upon the nature of the SQL that you have in your shop. If your system is characterized by dynamic tables where the number of rows may change daily from 1,000,000 to 100, then you would probably not want to use optimizer plan stability, because you would want the CBO to reevaluate the execution plan for each SQL statement based on the current CBO statistics.

On the other hand, if your tables are relatively stable, using stored outlines will greatly improve the performance of you SQL because the SQL parse phase is bypassed. You also have the peace of mind of knowing that a SQL statement will always use the same execution plan regardless of changes to Oracle initialization parameters and CBO statistics.

Remember, there are many DBAs who feel that for any SQL statement there exists only one optimal execution plan, and once located, it should never change. If you are one of these DBAs, then stored outlines can greatly aid your SQL tuning effort.

If you choose to implement stored outlines for all of your SQL, you must also remember that CBO statistics will only be used by new queries. Hence, you may want to reduce the frequency of your reanalysis of tables and indexes.

The use of stored outlines also aids you when you are migrating from rule-based to cost-based optimization. While you begin to tune SQL statements to use the CBO, you can use optimizer plan stability to ensure that your other SQL statements continue to use their rule-based execution plans. Let’s take a look at how this works.

Using Optimizer Plan Stability When Migrating to the CBO

Because Oracle8i is the first release of Oracle where the CBO has comparable execution speeds to the RBO, many DBAs are looking at methods for safely migrating their SQL into a database with a cost-based optimizer default. If your application was developed using the rule-based optimizer, then you may have already invested a considerable amount of effort into manually tuning the SQL statements to optimize performance.

Most RBO applications use a rule-based default optimizer mode (optimizer_mode=rule) and then tune specific SQL queries with cost-based hints. While the tuned SQL has hints to ensure fast execution speeds, the majority of the SQL using the rule-based default may change execution plans when migrated into a database with a cost-based optimizer default (optimizer_mode=first_rows). You can use optimizer plan stability to preserve your execution plans when upgrading from rule-based to cost-based optimization.

By creating stored outlines for every SQL statement before switching to cost-based optimization, you can use the plans generated by the rule-based optimizer, while statements generated by newly written applications developed after the switch use cost-based plans. To create and store outlines for your system before migrating to the CBO, use the alter system set use_stored_outlines=true command. This will create an execution plan for every SQL statement.

Later, when you are ready to see if your non-hinted rule-based SQL statements will run faster with the Oracle8i CBO, you can run a query against the DBA_OUTLINES to identify those SQL statements that do not contain hints and tune them individually using the procedures described later in this chapter.

Next, let’s get started and see how to prepare your database to allow optimizer plan stability.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

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