Data Management

Database Optimization: Tweaking Oracle SQL performance parameters

Oracle's cost-based SQL optimizer is a sophisticated tool for tuning database performance, but the dynamic nature of database environments means that the default values are not always the best. Optimization may require CBO parameter tweaking.


Oracle Corporation has invested millions of dollars in making the cost-based SQL optimizer (CBO) one of the most sophisticated tools ever created. The job of the CBO is to always choose the most optimal execution plan for any SQL statement.

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.

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.

If you are having slow performance because the CBO first_rows optimizer mode is favoring too many full-table scans, you can reset the optimizer_index_cost_adj parameter to immediately tune all of the SQL in your database to favor index scans over full-table scans. This is a "silver bullet" that can improve the performance of an entire database in cases where the database is OTLP and you have verified that the full-table scan costing is too low.

The parameter is an initialization parameter that can be enabled at the session level by using the alter session set optimizer_index_cost_adj = nn syntax. This parameter lets you tune the optimizer behavior for access path selection to be more or less index-friendly, and it is very useful when you feel that the default behavior for the CBO favors full-table scans over index scans.

When the CBO has minimal or too small a statistics sample, the CBO sometimes falsely determines that the cost of full-table scan is less than the cost of an index access. The optimizer_index_cost_adj parameter is a great approach to whole-system SQL tuning, but you will need to evaluate the overall effect by slowly resetting the value down from 100 and observing the percentage of full-table scans.

You can also slowly bump down the value of optimizer_index_cost_adj when you bounce the database and then either use the access.sql scripts or reexamine SQL from the STATSPACK stats$sql_summary table to see the net effect of index scans on the whole database.

Listing A is a script that interrogates the v$system_event view and displays a suggested starting value for optimizer_index_cost_adj.

Here is the output from the script in Listing A.

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, as you can see in this output.

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 Creating a Self-Tuning Oracle Database by Rampant TechPress. For SQL tuning scripts, Mike Ault, one of the world's top Oracle experts, has released his complete collection of more than 450 Oracle scripts, covering every possible area of Oracle administration and management.

This article shows you the dynamic nature of an active database and demonstrates the value of being able to dynamically change important parameters as the processing load on the system changes.

Editor's Picks

Free Newsletters, In your Inbox