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 Problem of Using Choose as the Default Mode
Oracle Tips by Burleson
 

One of the most confounding problems with Oracle databases happens when they do not explicitly set their optimizer_mode parameter in their init.ora file. As you may know, when no optimizer_mode is specified, Oracle8i defaults to optimizer_mode=choose.

The choose default mode is very dangerous for several reasons. The choose mode evaluates whether to use the RBO or the CBO according to the presence or absence of CBO statistics. If statistics exist, then use the CBO; else use the RBO. The problem comes in when table and indexes are “accidentally” analyzed. For example, a DBA might want to know the average row length for a table and issue the analyze table command to populate the avg_row_len column in the DBA_TABLES view, not realizing that he or she has just changed the execution plans for dozens of SQL statements.

The real problem with the choose mode is that whenever any table or index contains statistics, the choose mode assumes that you want to use the CBO and uses internal statistics (such as the number of data blocks allocated to the tables) to estimate statistics for the other tables in the query. These estimates are quite poor and usually result in a suboptimal execution plan for the query. This statistics estimation is done during the parse phase of SQL execution, and the time required to gather statistics for the CBO can be very significant.

It is very common in shops that are new to Oracle and use the default optimizer_mode=choose. One of the first things that I look at when a shop complains of poor SQL response time is the default optimizer mode and the existence of complete statistics for the CBO. Most Oracle shops do not use choose as their default, and conduct a test of performance using each optimizer mode and settle upon a default of rule, first_rows, or all_rows as their default optimizer mode.

Using a Rule-Based Default Optimizer Mode

Some Oracle8i databases continue to use a rule-based default (optimizer_mode=rule), and the DBA tunes selected queries by adding cost-based hints and selectively analyzing tables and indexes. This allow the best of both worlds because the reliable and predictable rule-based optimizer is used for the simple SQL, while queries that required specialized access methods such as bitmap indexes or hash joins can still use cost-based optimization.

The choice about using a rule-based default is generally made by running the production database for one day in rule mode and another day in first_rows mode, and then choosing the appropriate default in accordance with performance feedback from end users and internal performance measurements from STATSPACK. The goal is to minimize the amount of manual SQL tuning by choosing a default optimizer mode with the best overall performance. This empirical approach ensures that a minimal number of SQL statements will have a sub-optimal execution plan.

One problem with the first__rows or all__rows optimizer_mode is that it forces all statements into CBO whether they have statistics or not. In the preceding scenario, the DBA should also make sure all indexes and tables are analyzed.

When using a rule-based default, you can still use all of the robust features of Oracle8i, including optimizer plan stability (stored outlines), cursor sharing, and the advanced cost-based hints. However, you always need to keep in mind the differences between cost-based and rule-based optimization. The driving table for cost-based optimization (when using the ordered hint) is the reverse of that for rule-based optimization, and rule-based queries are generally tuned by resequencing table names in the from clause and predicates in the where clause. Conversely, cost-based tuning relies on hints, CBO statistics, and the settings for numerous initialization parameters to determine the optimal table join order and overall execution plan.


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