Oracle’s Cost-Based Optimizer has been with us now for a long time. Choosing the right mode setting can improve the average performance of your database queries and is often overlooked.

The Optimizer tries to determine the best execution plan for a database query based on many factors including table statistics, query predicates, joins, indexes, partitions and the Optimizer mode.

When setting the initialization parameter OPTIMIZER_MODE the typical workload of the database should be considered. By default it is set to ALL_ROWS.

Let’s consider the options:

• CHOOSE: Still allowed but obsolete. Uses Cost-Based optimization where statistics are available, otherwise uses Rule.
• RULE: Still allowed for backwards compatibility. Uses a set of Rules to determine the best query plan.
• ALL_ROWS: This is the default setting. Causes the optimizer to determine the best query plan to return the complete result set. This generally favours scans over index lookups.
• FIRST_ROWS: Causes the optimizer to determine the best query plan to return the first row of the query. This generally favours index lookups over scans.
• FIRST_ROWS_n (where n=1, 10, 100 or 1000): Causes the optimizer to determine the best query plan to return the first n rows of the query. This generally favours index lookups over scans.

Generally, the default setting of ALL_ROWS will be the best option, but for some OLTP applications where only the first n rows are displayed on the screen it may be worth considering using FIRST_ROWS_n.

It is possible to test this out by setting the OPTIMIZER_MODE at a session or query level

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_100;

or by using the hint

SELECT /*+ opt_param(‘optimizer_mode’,’first_rows_100′) */ ….

No matter what mode is decided upon it is important to keep statistics up to date to ensure the optimizer has the latest information to base its calculations on and to stand the best chance of generating the most optimal query plan. It should be noted that the default Oracle statistics gathering jobs are often not appropriate for larger or more complex data sets, and may lead to incomplete or inconsistent statistics.

As the Optimizer evolves, the same query plans may not be generated between releases and the plans generated may not always be an improvement. Tools such as RAT (Real Application Testing) can be used to check performance prior to a production upgrade to prevent unpleasant surprises, by allowing the capture and replay of Production workloads against representative test environments, including “what if” analysis of different Optimizer modes.

By analyzing your workload, indexing, partitioning and other variables Cintra are able to make recommendations to improve your database query performance and advise on the use of Oracle tools like RAT.

Written by Ian Fergusson, Principal DBA, Cintra UK – October 2015