This article has the following sections:

• Part 1 - Using SQL CBO Parameters
• Part 2 - Using CBO statistics
• Part 3 - Proper SQL development environment
• Part 4 - Using histograms to tune SQL
• Part 5 - Clustering and SQL tuning
• Part 6 - External costs and SQL execution
• Part 7 - Using hints to tune SQL
• Part 8 - Locating sub-optimal SQL

So let's start by examining the CBO optimizer modes and the Oracle parameters that influence the CBO.

PART 1 - CBO Parameters

The CBO is influenced by many configuration settings. Your settings for important CBO parameters can have a dramatic impact of CBO performance, and this is the place to start when exploring the CBO. Let's start by choosing your CBO optimizer_mode and then examine other important CBO parameters.

The CBO and optimizer modes. In Oracle9i Database there are four optimizer modes, all determined by the value of the optimizer_mode parameter: rule, choose, all_rows, and first_rows. The rule and choose modes reflect the obsolete rule-based optimizer, so we will focus on the CBO modes here.

The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans.  Index access (first_rows) adds additional I/O overhead, but they return rows faster, back to the originating query.
 

The optimizer mode can be set at the system-wide level, for an individual session, or for a specific SQL statement:

alter system set optimizer_mode=first_rows_10;
select /*+ first_rows(100) */ from student;

We need to start by defining what is the "best" execution plan for a SQL statement. Is the best execution plan the one that begins to return rows the fastest, or is the best execution plan the one that executes with the smallest amount of computing resources? Of course, the answer depends on the processing needs of your database.

Troubleshooting tip!  For testing, you can quickly test the effect of another optimizer parameter value at the query level without using an "alter session" command, using the new opt_param SQL hint:

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .

The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans.  Indexes add additional I/O overhead, but they return rows faster, back to the originating query:

Let's take a simple example. Assume the following query:

select customer_name
from
   customer
where
   region = 'south'
order by
   customer_name;

If the best execution plan is the one that starts to return rows the fastest, a concatenated index on region and customer_name could be used to immediately start delivering table rows in their proper order, even though excess I/O will be required to read the nonadjacent data blocks.

Let's assume that this execution plan starts delivering results in .0001 seconds and requires 10,000 db_block_gets. But what if your goal is to minimize computing resources? If this SQL is inside a batch  program, then it is not important to start returning rows quickly, and a different execution plan would take      fewer resources. In this example, a parallel full-table scan followed by a back-end sort will require less       machine resources and less I/O because blocks do not have to be reread to pull the data in sorted order  In this example, we expect the result to take longer to deliver (no rows until the sort is complete), but we will see far less I/O because blocks will not have to be re-accessed to deliver the rows in presorted order.
Let's assume that this execution plan delivers the result in 10 seconds with 5,000 db_block_gets.

Oracle offers several optimizer modes that allow you to choose your definition of the "best" execution plan for you:

• optimizer_mode=first_rows_ This CBO mode will return rows as soon as possible, even if the overall query runs longer or consumes more computing resources than other plans. The first_rows optimizer_mode usually involves choosing an index scan over a full-table scan because index access    will return rows quickly. Because the first_rows mode favors index scans over full-table scans, the first_rows mode is more appropriate for OLTP systems where the end user needs to see small result sets as quickly as possible.

   

• optimizer_mode=all_rows_ This CBO mode ensures that the overall computing resources are minimized, even if no rows are available until the entire query has completed. The all_rows access method often favors a parallel full-table scan over a full-index scan, and sorting over presorted retrieval via an index. Because the all_rows mode favors full-table scans, it is best suited for data warehouses, decision-support systems, and batch-oriented databases where intermediate rows are not required for  real-time viewing.

   

• optimizer_mode=first_rows_n This Oracle9i Database optimizer mode enhancement optimizes queries for a small, expected return set. The values are first_rows_1, first_rows_10, first_rows_100, and first_rows_1000. The CBO uses the n in first_rows_n as an important driver in determining cardinalities for query result sets. By telling the CBO, a priori, that we only expect a certain number of rows back from the query, the CBO will be able to make a better decision about whether to use an index to access the table rows.

   

• Optimizer_mode=rule The rule-based optimizer (RBO) is the archaic optimizer mode from the earliest releases of Oracle Database. The rule-based optimizer has not been updated in nearly a decade and is not recommended for production use because the RBO does not support any new features of  Oracle since 1994 (such as bitmap indexes, table partitions, and function-based indexes).

While the optimizer_mode is the single most important factor in invoking the cost-based optimizer, there are other parameters that influence the CBO behavior. Let's take a quick look at these parameters.

Oracle parameters that influence the CBO. While the optimizer_mode parameter governs the global       behavior of the CBO, there are many other Oracle parameters that have a great impact on CBO behavior. Because of the power of the CBO, Oracle provides several system-level parameters that can adjust the       overall behavior of the CBO. These adjustment parameters generally involve the choice of using an index      versus doing a full-table scan, and the CBO's choice of table join methods.

However, Oracle does not recommend changing the default values for many of these CBO setting because        the changes can affect the execution plans for thousands of SQL statements. Here are the major optimizer  parameters:

• optimizer_index_cost_adj_ This parameter alters the costing algorithm for access paths involving indexes. The smaller the value, the lower the cost of index access.

   

• optimizer_index_caching_ This parameter 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.

   

• db_file_multiblock_read_count_ When this parameter is set to a high value, the CBO  recognizes that scattered (multiblock) reads may be less expensive than sequential reads. This makes the CBO friendlier to full-table scans. (deprecated in 11g and beyond)
  
  
• hash_area_size (if not using pga_aggregate_target, sga_target or memory_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 presorted index retrieval.

The idea optimizer settings depend on your environment and are heavily influenced by your system's costs for scattered disk reads versus sequential disk reads. Listing 1 contains a great script you can use to measure these I/O costs on your database.

LISTING 1: optimizer_index_cost_adj.sql

col c1 heading 'Average Waits|forFull| Scan Read I/O'        format 9999.999
col c2 heading 'Average Waits|for Index|Read I/O'            format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans'        format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans'       format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999

select
   a.average_wait                                 c1,
   b.average_wait                                 c2,
   a.total_waits /(a.total_waits + b.total_waits) c3,
   b.total_waits /(a.total_waits + b.total_waits) c4,
  (b.average_wait / a.average_wait)*100           c5
from
   v$system_event a,
   v$system_event b
where
   a.event = 'db file scattered read'
and
   b.event = 'db file sequential read'
;

Now that we understand the CBO parameters, let's look at how we can help the CBO make good execution-plan decisions by providing the CBO with information about our schema.

The Ion tool is the easiest way to analyze Oracle performance and Ion allows you to spot hidden performance trends.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.