Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

What is your Oracle SQL optimizer philosophy?

Oracle Tips by Burleson Consulting
April 26, 2001

 

Once your company has made a decision to use the cost-based SQL optimizer (CBO), you must make a very important decision about your philosophy regarding SQL tuning.

To be successful in a migration to the CBO, you need to understand the relationship between the CBO statistics (created with the Oracle ANALYZE command) and the use of the new optimizer plan stability feature in Oracle8i. Before undertaking a migration to the CBO, you must develop a philosophy regarding CBO statistics and determine whether you want Oracle to dynamically change SQL execution plans. This choice is heavily dependent upon the nature of the Oracle database, and either choice may be optimal depending on the type of processing.

Some Oracle professionals subscribe to the theory that for any SQL query, there exists only one optimal execution plan. Once this optimal execution plan is determined, it should be made persistent with optimizer plan stability. In contrast, other shops want their SQL to change execution plans whenever there has been a significant change to the CBO statistics.

Stable shops where the table statistics rarely change will want to employ optimizer plan stability to make execution plans persistent, while shops where the CBO statistics frequently change will tune their queries without optimizer plan stability so that the run-time optimizer is free to choose the most appropriate execution plan based on the CBO statistics.

The choice of SQL philosophy has a dramatic impact on your approach to SQL tuning and statistics maintenance. You need to take the following areas into consideration when making your decision:
  • Table and index statistics—The dynamic philosophy relies heavily on the table and index statistics, and these statistics must be recomputed each time that a table has a significant change. In contrast, the static philosophy does not rely on statistics.
     
  • Optimizer plan stability—The dynamic shop does not use optimizer plan stability because it wants the freedom for the execution plan to change whenever there is a major change to the data inside the tables. Conversely, the static shop relies on optimizer plan stability to make its tuning changes permanent and to improve SQL execution time by avoiding reparsing of SQL statements.
     
  • Cursor sharing—The dynamic shop often has SQL that is generated by ad-hoc query tools with hard-coded literal values embedded within the SQL. As we know, hard-coded literal values make the SQL statements nonreusable unless cursor_sharing=force is set in the Oracle initialization file. Shops that are plagued with nonreusable SQL can adopt either the persistent or the dynamic philosophy. To use optimizer plan stability with nonreusable SQL, the DBA will set cursor_sharing=force and then extract the transformed SQL from the library cache and use optimizer plan stability to make the execution plan persistent.
Let's take a closer look at these competing SQL philosophies so that you can see which one best fits your organization.

The persistent SQL philosophy

If your shop has relatively static tables and indexes, you may want to adopt the persistent SQL philosophy that states that there exists only one optimal execution plan for any SQL statement. Shops that subscribe to this philosophy are characterized by stable applications that have been tuned to use host variables (instead of literal values) in all SQL queries.

Persistent shops also have tables and indexes whose recomputed statistics rarely change the execution plan for their SQL queries, regardless of how often the statistics are recomputed. Many persistent shops have all of their SQL embedded inside PL/SQL packages, and the applications will call their SQL using a standard PL/SQL function of a stored procedure call. This insulates all of the SQL from the application programs and ensures that all applications execute identical SQL. It also ensures that all of the SQL has been properly tuned.

Choosing the persistent approach means that all tuned SQL will utilize optimizer plan stability and that the CBO statistics will be used only for ad-hoc queries and new queries that have not yet been tuned. Of course, there is also a performance benefit to using optimizer plan stability because the SQL statements are preparsed and ready to run. This approach is generally used in shops where experience has found that the execution plans for SQL rarely change after the CBO statistics have been reanalyzed.

The persistent SQL philosophy requires the DBA to write scripts to detect all SQL statements that do not possess stored outlines and to tune these queries on behalf of the developers. The persistent SQL philosophy also requires less reliance on CBO statistics, and the DBA generally analyzes tables only when they are first migrated into the production environment. Since optimizer plan stability does not rely on statistics, the server overhead of periodically recomputing statistics for the CBO is avoided.

The dynamic SQL philosophy


The dynamic SQL philosophy subscribes to the belief that Oracle SQL should change execution plans in accordance with the changes to the CBO statistics. Shops that subscribe to the dynamic SQL philosophy are characterized by highly volatile environments where tables and indexes change radically and frequently. These shops frequently reanalyze their CBO statistics and allow the CBO to choose the execution plan based upon the current status of their CBO statistics.

A good example of a shop that uses the dynamic SQL philosophy would be one where tables grow over a specified period of time and then are purged before new data is reloaded. In these types of environments, the num_rows and avg_row_len for the tables are frequently changing, as are the distributions of index values. This change in statistics, in turn, causes the CBO to choose a different execution plan for the SQL queries.

Decision-support environments and scientific databases often adopt this philosophy because entirely new subsets of data are loaded into tables, the data is analyzed, the tables truncated, and a wholly different set of data is loaded into the table structures.

Another common characteristic of dynamic shops is that the SQL cannot be easily tuned. Oracle databases that are accessed by casual users via ODBC and third-party tools such as Crystal Reports or Microsoft Access are often forced into the dynamic philosophy because the incoming SQL is always different. However, it is very important to note that the use of third-party application suites, such as SAP and PeopleSoft, does not always require the adoption of the dynamic philosophy. The SQL from these types of application suites can be captured in the library cache, and optimizer plan stability can be used to make the execution plan persistent.

These shops require a very different approach to SQL tuning than persistent SQL shops do. Each time new data is loaded or the data changes, the affected tables and indexes must be reanalyzed. These shops often incorporate the dbms_stats package directly into their load routines.

In Oracle, the DBA for dynamic shops must be vigilant for changes to the distribution of index column values. When column values for any index become skewed, the DBA must create column histograms for the index so the optimizer can choose between a full-table scan and an index range scan to service queries. Of course, these shops will benefit greatly with the use of Oracle9i, where the database will automatically create column histograms for index columns with skewed distributions.

Conclusion


Many companies adopt a philosophy without completely realizing the ramifications of their chosen approach. In practice, most shops begin with a dynamic philosophy and then undertake to migrate to the static approach after experience indicates that their execution plans rarely change after a reanalysis of the tables and indexes.

The Oracle 8i feature of cursor_sharing and optimizer plan stability are a godsend to many Oracle administrators and offer a proven method to improve the tuning and persistence of SQL execution plans.

 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and 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 Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational