The Optimizer Modes Oracle Tips by Burleson Consulting

Within these two optimizers, Oracle provides several optimizer modes. An optimizer mode may be set for the entire database, or it may be set for a specific Oracle session. The optimizer modes include rule, first_rows, all_rows and choose. Let’s take a closer look at each of these goals.

Rule mode

The rule hint will ignore the CBO and the statistics and generate an execution plan based solely of the basic data dictionary information. It is always a good idea to try tuning a specific SQL statement by using the RBO by using a rule hint or by issuing the alter session set optimizer_goal=rule. However, always remember that the RBO may choose a less than ideal index to service a query.

Optimizer_mode=choose

The choose optimizer mode allows Oracle to choose the most appropriate optimizer goal. This is the default optimizer mode within Oracle, and it generally uses the presence of statistics to determine which optimizer to invoke. If no statistics exist, Oracle will use the rule goal. If statistics exist, Oracle will choose first_rows or all_rows, depending on the presence of indexes and the setting for parallelism and db_file_multiblock_read_count.

Warning: Having incomplete statistics with the choose optimizer mode can cause huge slowdowns. If Oracle detects a single table in a query with statistics, it will use the cost-based optimizer, and compute statistics for the other tables at run-time. This can cause huge slowdown of SQL execution. This problem is very common with Oracle Remote DBA beginners because the default mode is choose, and because beginner Remote DBAs sometimes selectively issue the analyze command, not realizing that they are effecting SQL execution times.

first_rows mode

This is a cost-based optimizer mode that will return rows as soon as possible, even if the overall query runs longer or consumes more resources. The first_rows optimizer mode usually involves choosing a full-index scan over a parallel full-table scan. Because the first_rows mode favors index scans over full-table scans, the first_rows mode is most appropriate for online systems where the end userwants to see some results as quickly as possible.

all_rows mode

This is a cost-based optimizer mode that ensures that the overall query time is minimized, even if it takes longer to receive the first row. This usually involves choosing a parallel full-table scan over a full-index scan. Because the all_rows mode favors full-table scans, the all_rows mode is best suited for batch-oriented queries where intermediate rows are not required for viewing.

Which is “better”? Fast execution vs. minimal resource usage.

To understand Oracle’s philosophy regarding fast execution and minimal resource consumption we need to look at a simple example. To illustrate the difference between all_rows and first_rows with an over-simplistic example, consider the following query:

select
   last_name
from
   customer
order by
   last_name;

This query can be serviced in two ways:

•  Full-table scan -  Here we perform a full-table scan in parallel and sort the rows in the TEMP tablespace. For the sake of illustration, let’s assume that this execution plan produces the fastest overall execution time and minimal use of resources (Figure 2).

Figure 2: Perform a full-table scan and a sort (all_rows)

• Full Index scan -  Here we retrieve the rows in last_name order by using a last_name_index. This technique results in more physical reads, but begins to return sorted rows almost immediately (Figure 3).

Figure 3: Perform an index retrieval (first_rows)

Again, assuming that we accept the full-table scan and sort is less I/O intensive than the full-index scan, then we can clearly see the difference in the optimizer goals. The full-index scan will begin to return rows almost immediately at the expense of extra I/O, while the full-table scan will require less resources, but the result set will not be available until the end of the query.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.