However, there are some things that the CBO cannot detect, which is where the DBA comes in. The types of SQL statements, the speed of the disks and the load on the CPUs, all affect the "best" execution plan for a SQL statement. For example, the best execution plan at 4:00 A.M. when 16 CPUs are idle may be quite different from the same query at 3:00 P.M. when the system is 90 percent utilized.

Despite the name "Oracle", the CBO is not psychic, and Oracle can never know, a priori, the exact load on the Oracle system. Hence the Oracle professional must adjust the CBO behavior periodically. Most Oracle professionals make these behavior adjustments using the instance-wide CBO behavior parameters such as optimizer_index_cost_adj and optimizer_index_caching.

However, Oracle does not recommend changing the default values for many of these CBO settings because the changes can affect the execution plans for thousands of SQL statements.

Here are some of the major adjustable parameters that influence the behavior of the CBO:
 

• optimizer_index_cost_adj: This parameter alters the costing algorithm for access paths involving indexes. The smaller the value, the cheaper the cost of index access.
   
• optimizer_index_caching: This is the parameter that tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching affects the CBO's decision to use an index for a table join (nested loops), or to favor a full-table scan.
   
• optimizer_max_permutations: This controls the maximum number of table join permutations allowed before the CBO is forced to pick a table join order. For a six-way table join, Oracle must evaluate 6-factorial, or 720, possible join orders for the tables.
   
• db_file_multiblock_read_count: When set to a high value, the CBO recognizes that scattered (multi-block) reads may be less expensive than sequential reads. This makes the CBO friendlier to full-table scans.
   
• parallel_automatic_tuning: When set "on", full-table scans are parallelized. Because parallel full-table scans are very fast, the CBO will give a higher cost to index access, and be friendlier to full-table scans.
   
• hash_area_size (if not using pga_aggregate_target): The setting for hash_area_size parameter governs the propensity of the CBO to favor hash joins over nested loop and sort merge table joins.
   
• sort_area_size (if not using pga_aggregate_target): The sort_area_size influences the CBO when deciding whether to perform an index access or a sort of the result set. The higher the value for sort_area_size, the more likely that a sort will be performed in RAM, and the more likely that the CBO will favor a sort over pre-sorted index retrieval.

The parameter optimizer_index_cost_adj controls the CBO's propensity to favor index scans over full-table scans. As we will see, in a dynamic system, the "ideal" value for optimizer_index_cost_adj may change radically in just a few minutes, as the type of SQL and load on the database changes.

Using optimizer_index_cost_adj

The optimizer_index_cost_adj is the most important parameter of all, and the default setting of 100 is incorrect for most Oracle systems. However, for OLTP systems, resetting this parameter to a smaller value (between 10 and 30) may result in huge performance gains.

10g Note:  In Oracle 10g, you can achieve a similar result to reducing the value of optimizer_index_cost_adj by analyzing your workload statistics (dbms_stats.gather_system_stats).  Also note that utilizing CPU costing (_optimizer_cost_model) may effect the efficiency of plans with lower values for optimizer_index_cost_adj.

Is it possible to query the Oracle environment and intelligently determine the optimal setting for optimizer_index_cost_adj? Let's examine the issue.

The optimizer_index_cost_adj parameters default to a value of 100, and can range in value from 1 to 10,000. A value of 100 means that equal weight is given to index vs. multiblock reads. In other words, optimizer_index_cost_adj can be thought of as a "how much do I like full-table scans?" parameter.

With a value of 100, the CBO likes full-table scans and index scans equally, and a number lower than 100 tells the CBO that index scans are faster than full-table scans. However, even with a super-low setting (optimizer_index_cost_adj=1), the CBO will still choose full-table scans for no-brainers, like tiny tables that reside on two blocks.

optimizer_index_cost_adj.sql

Here is the output from the script in Listing A.

                                                                         Starting
                                                                          Value
                                                                           for
                                                                        optimizer
                                          Percent of      Percent of     index
 Average waits for   Average waits for     I/O waits       I/O waits      cost
 full scan read I/O   index read I/O   for full scans   for index scans   adj

 ------------------ ------------------- ---------------- --------------- -------
       1.473              .289                 .02             .98          20

As you can see, the suggested starting value for optimizer_index_cost_adj may be too high because 98 percent of the data waits are on index (sequential) block access. How we can "weight" this starting value for optimizer_index_cost_adj to reflect the reality that this system has only two percent waits on full-table scan reads (a typical OLTP system with few full-table scans). As a practical matter, we never want an automated value for optimizer_index_cost_adj to be less than one or more than 100.

Also, these values change constantly, As the I/O waits accumulate and access patterns change, this same script may give a very different result at a different time of the day.

Optimization

The Oracle Cost-based SQL optimizer is one of the world's most sophisticated software achievements, but it is the job of the Oracle professional to provide valid statistics for the schema and understand how the Oracle parameters affect the overall performance of the SQL optimizer. Remember, suboptimal SQL execution plans are a major reason for poorly performing Oracle databases, and because the CBO determines the execution plans, it is a critical component in Oracle optimization.

Additional Information
For more information, see Don Burleson's book Oracle Tuning: The Definitive Reference by Rampant TechPress. For SQL tuning scripts, Mike Ault, one of the world's most widely-read Oracle experts, has released his complete collection of more than 450 Oracle scripts, covering every possible area of Oracle administration and management.