The Oracle SQL Optimizers Oracle Tips by Burleson Consulting

One of the first things the Oracle Remote DBA looks at is the default optimizer mode for their database. There are two classes of optimizer modes: the rule-based optimizer (RBO) and the cost-based optimizer (CBO). The Oracle init.ora parameters offer four values for the optimizer_mode parameter.

optimizer_mode = RULE

The first, and oldest optimizer mode is RULE. Under the rule-based optimizer, Oracle uses heuristics from the data dictionary in order to determine the most effective way to service an Oracle query and translate the declarative SQL command into an actual navigation plan to extract the data. In many pre-Oracle8i systems, rule-based optimization is faster than cost-based. In fact, Oracle Applications used rule-based optimization until release 11i.

optimizer_mode = FIRST_ROWS

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 inline systems where an end user wants to see some results as quickly as possible.

optimizer_mode = ALL_ROWS

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.

To illustrate the difference between ALL_ROWS and FIRST_ROWS with an oversimplistic example, consider the following query:

L 11-4

This query can be serviced in two ways:

•  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, as shown in Figure 11-1.

          Figure 11-104: Perform a full table scan and a sort (ALL_ROWS)

• 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, as shown in Figure 11-2.

          Figure 11-105:Perform an index retrieval (FIRST_ROWS)

optimizer_mode = CHOOSE

Oracle's default optimizer mode is called CHOOSE. In the CHOOSE optimizer mode, Oracle will execute the rule-based optimizer if there are no statistics present for the table, or execute the cost-based optimizer if statistics are present. The danger with using the CHOOSE optimizer mode is in cases where one Oracle table in a complex query has statistics and the other tables do not have statistics. When only some tables contain statistics, Oracle will use the cost-based optimization and estimate statistics (by sampling 5,000 rows) for the other tables in the query at runtime. This can cause significant slowdown in the performance of the individual query. Be careful when using the CHOOSE option. When you give Oracle the ability to choose the optimizer mode, Oracle will favor the cost-based approach if any of the tables in the query have statistics. (Statistics are created with the analyze table command.) For example, if a three-table JOIN is specified in CHOOSE mode and statistics exist for one of the three tables, Oracle will decide to use the cost-based optimizer. When this happens, the Oracle CBO will inspect the num_rows column of the Remote DBA_tables view. If num_rows is null, Oracle still estimates statistics based on a 5,000-row sample. If the num_rows column is zero, Oracle will not perform a runtime table analysis. In short, if Oracle analyzes the table at runtime, this action will dramatically slow down the query.

Also, if a cost-based query is issued (i.e., with a hint) and there are no statistics of any tables or indexes, the CBO will choose a driving table—just like the RBO. However, the driving table for the CBO is the first table in the FROM clause, the exact opposite of the RBO.

Disadvantages of Optimizer Modes

Now, let's talk about each of these default modes and observe their relative advantages and disadvantages.

There are major shortcomings in both major optimization modes. The existence of hints indicates that the Oracle optimizers do not always make the most effective execution decision.

CAUTION: The use of any SQL hint except the RULE hint forces the cost-based optimizer to be invoked. Hence, make sure you have analyzed your tables and indexes prior to using any hints.

• Rule-based shortcomings Often chooses the wrong index to retrieve rows

• Cost-based shortcomings Often performs unnecessary full tables scans, especially when more than three tables are being joined

The tuning of rule-based SQL is quite different from tuning cost-based SQL. With the rule-based optimizer, the indexing of tables and order of clauses within the SQL statement control the access path in rule-based optimization. The cost-based optimizer automatically determines the most efficient execution path, and the programmer is given hints that can be added to the query to alter the access path.

Remember, the foremost tenet of Oracle SQL tuning is avoiding an unnecessary large-table full table scan. One of the hallmarks of an inefficient SQL statement is the failure of the SQL statement to use all of the indexes that are present within the Oracle database in order to speed up the query.

Of course, there are times when a full table scan is appropriate for a query, such as when you are doing aggregate operations such as a sum or an average, and the majority of the rows (greater than 40 percent for row-sequenced tables and greater than 7 percent for unsequenced tables) within the Oracle table must be read to get the query results. The task of the SQL tuning expert is to evaluate each full table scan and see if the performance can be improved by adding an index.

Most Oracle SQL queries will only be retrieving a small subset of the rows within the table and full table scans are only appropriate when more than 40 percent of the table rows are required. The Oracle optimizers are programmed to check for indexes, and to use indexes whenever possible to avoid excessive I/O. However, if the formulation of a query is inefficient, the cost-based optimizer becomes confused about the best access path to the data, and the cost-based optimizer will sometimes choose to do a full table scan against the table. Again, the general rule is for the Oracle database administrator to interrogate the SQL, and always look for full table scans.

Determining the Threshold for a Full Table Scan

When making the decision to change a full table scan to an index range scan, the primary concern is the speed of the query. In some cases, the full table scan may have more physical disk I/Os, but the full table scan will be faster because of a high degree of parallelism.

In other cases, you need to consider the number of rows retrieved as a function of the clustering of the rows in the table. For example, if your table is clustered or you have manually resequenced the rows in primary-key order, a great many adjacent rows can be read in a single I/O and an index range scan will be faster than a full table scan for queries that retrieve more than 20-40 percent of the table rows. On the other hand, if your table is totally unordered, a request for 10 percent of the table rows may cause the majority of the table data blocks to be read. Of course, you also need to consider the degree of parallelism on the table and the setting for the db_file_multi_ block_read_count init.ora parameter. Hence, the general guideline for replacing an index range scan is:

•  For row-sequenced tables Queries that retrieve less than 40 percent of the table rows should use an index range scan. Conversely, queries that read more than 40 percent of the rows should use a full table scan.

• For unordered tables Queries that retrieve less than 7 percent of the table should use an index range scan. Conversely, queries that read more than 7 percent of the table rows will probably be faster with a full table scan.

Your mileage may vary, so it is always a good idea to test the execution speed in SQL*Plus by issuing the set timing on command.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

---

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.