14.6 How do I…Set the optimizer mode?

Problem

I know that Oracle8 can optimize queries in more than one way and that DBAs and developers can help Oracle8 make the best choice among the available optimizer modes. How do I tell Oracle8 which optimizer mode to use?

Technique

Oracle8 users can instruct the optimizer to base its behavior on either the contents of the database or the syntax of SQL statements. The first way, in general, results in better performance, but it comes at a price. DBAs or developers, specifically, have to issue ANALYZE commands against database objects so that the Oracle8 data dictionary contains the information that the optimizer needs.

You can choose an optimizer mode in three ways: at the instance level via the OPTIMIZER_MODE initialization parameter, at the session level via the OPTIMIZER_GOAL option, or at the statement level via optimizer hints (these are covered in depth in How-To 14.7).

Steps

1. To set the optimizer goal at the instance level, use a text editor to edit the initialization parameter file. Under Windows NT, the default location for this file is the \ORANT\DATABASE subdirectory. The OPTIMIZER_GOAL parameter controls the optimizer’s behavior and can assume any of the values summarized in Table 14.10. To activate a new OPTIMIZER_GOAL setting, shut down the database and restart it.

Table 14.10 Possible values for the OPTIMIZER_GOAL initialization parameter

Value Effect
CHOOSE Use cost-based optimization if table statistics exist. Otherwise, use rule-based optimization.
RULE Use rule-based optimization regardless of the existence of statistics.
FIRST_ROWS Use cost-based optimization with the objective of minimizing query response time.
ALL_ROWS Use cost-based optimization with the objective of minimizing total resource consumption.

2. To modify the optimizer’s behavior at the session level, use the ALTER SESSION SET OPTIMIZER_GOAL command. The possible values of OPTIMIZER_GOAL are the same as those listed in Table 14.8 for the OPTIMIZER_MODE initialization parameter. The script CHP14_13.SQL sets the optimizer mode at the session level. Figure 14.13 displays the script and its results.

3. To modify the optimizer’s behavior at the statement level, use optimizer hints on individual SQL statements like this:

SELECT /*+ RULE */ ename FROM emp14_5 WHERE empno > 0;

The hint on this SQL statement forces Oracle8 to use rule-based optimization regardless of the presence or absence of ANALYZE statistics or the values of the OPTIMIZER_MODE initialization parameter or the OPTIMIZER_GOAL option. This is only one way to use optimizer hints; more methods of SQL statement tuning using optimizer hints are covered in How-To 14.7.

How It Works

Step 1 shows how to modify the initialization parameter file to set the default optimizer mode for an entire instance. Step 2 shows how to set the optimizer mode at the session level using the ALTER SESSION command. Step 3 shows how to choose an optimizer mode at the statement level using an optimizer hint (continue to the next How-To for more information on optimizer hints).

Comments

Oracle7 supported two optimizer modes and Oracle8 continues to do so, but this is temporary. In some future release of the database, developers will cease to have a choice in this area. In fact, the Oracle8 documentation urges developers and DBAs to convert rule-based legacy applications to cost-based optimization as soon as possible. This entails implementing a schedule for database object analysis to support cost-based optimization decisions.

Optimizer settings at the session level override instance level settings. Settings at the statement level, indicated via statement hints, override all other optimizer directives.