14 Influencing the Optimizer

14.2.1 About Optimizer Initialization Parameters

Oracle Database includes several initialization parameters that can influence optimizer behavior. Table 14-1 lists some of the most important.

14.2.2 Enabling Optimizer Features

The OPTIMIZER_FEATURES_ENABLE initialization parameter controls a set of optimizer-related features, depending on the release. The parameter accepts one of a list of valid string values corresponding to the release numbers, such as 10.2.0.1 or 11.2.0.1.

You can use this parameter to preserve the old behavior of the optimizer after a database upgrade. For example, if you upgrade Oracle Database 11g Release 1 (11.1.0.7) to Oracle Database 11g Release 2 (11.2.0.2), then the default value of the OPTIMIZER_FEATURES_ENABLE parameter changes from 11.1.0.7 to 11.2.0.2. This upgrade results in the optimizer enabling optimization features based on Oracle Database 11g Release 2 (11.2.0.2).

For backward compatibility, you may not want the execution plans to change because of new optimizer features in a new release. In such cases, you can set OPTIMIZER_FEATURES_ENABLE to an earlier version. If you upgrade to a new release, and if you want to enable the features in the new release, then you do not need to explicitly set the OPTIMIZER_FEATURES_ENABLE initialization parameter.

Assumptions

This tutorial assumes the following:

• You recently upgraded the database from Oracle Database 10g Release 2 (10.2.0.5) to Oracle Database 11g Release 2 (11.2.0.2).

• You want to preserve the optimizer behavior from the earlier release.

To enable query optimizer features for a specific release: 

1. Connect SQL*Plus to the database with the appropriate privileges, and then query the current optimizer features settings.

   For example, run the following SQL*Plus command:

   SQL> SHOW PARAMETER optimizer_features_enable
    
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   optimizer_features_enable            string      11.2.0.2
   

2. Set the optimizer features setting at the instance or session level.

   For example, run the following SQL statement to set the optimizer version to 10.2.0.5:

   SQL> ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='10.2.0.5';
   

   The preceding statement restores the optimizer functionality that existed in Oracle Database 10g Release 2 (10.2.0.5).

14.2.3 Choosing an Optimizer Goal

The optimizer goal is the prioritization of resource usage by the optimizer. Using the OPTIMIZER_MODE initialization parameter, you can set the following optimizer goals:

• Best throughput (default)

  When you set the OPTIMIZER_MODE value to ALL_ROWS, the database uses the least amount of resources necessary to process all rows that the statement accessed.

  For batch applications such as Oracle Reports, optimize for best throughput. Usually, throughput is more important in batch applications because the user is only concerned with the time necessary for the application to complete. Response time is less important because the user does not examine the results of individual statements while the application is running.

• Best response time

  When you set the OPTIMIZER_MODE value to FIRST_ROWS_n, the database optimizes with a goal of best response time to return the first n rows, where n equals 1, 10, 100, or 1000.

  For interactive applications in Oracle Forms or SQL*Plus, optimize for response time. Usually, response time is important because the interactive user is waiting to see the first row or rows that the statement accessed.

Assumptions

This tutorial assumes the following:

• The primary application is interactive, so you want to set the optimizer goal for the database instance to minimize response time.

• For the current session only, you want to run a report and optimize for throughput.

To enable query optimizer features for a specific release: 

1. Connect SQL*Plus to the database with the appropriate privileges, and then query the current optimizer mode.

   For example, run the following SQL*Plus command:

   dba1@PROD> SHOW PARAMETER OPTIMIZER_MODE
   
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   optimizer_mode                       string      ALL_ROWS
   

2. At the instance level, optimize for response time.

   For example, run the following SQL statement to configure the system to retrieve the first 10 rows as quickly as possible:

   SQL> ALTER SYSTEM SET OPTIMIZER_MODE='FIRST_ROWS_10';
   

3. At the session level only, optimize for throughput before running a report.

   For example, run the following SQL statement to configure only this session to optimize for throughput:

   SQL> ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
   

14.2.4 Controlling Adaptive Optimization

In Oracle Database, adaptive query optimization is the process by which the optimizer adapts an execution plan based on statistics collected at run time (see "About Adaptive Query Optimization"). Adaptive optimization is enabled under the following conditions:

• The OPTIMIZER_FEATURES_ENABLE initialization parameter is set to 12.1.0.1 or later.

• The OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter is set to false (default).

If OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to true, then adaptive optimization runs in reporting-only mode. In this case, the database gathers information required for adaptive optimization, but does not change the plans. An adaptive plan always chooses the default plan, but the database collects information about the execution as if the parameter were set to false.

Assumptions

This tutorial assumes the following:

• The OPTIMIZER_FEATURES_ENABLE initialization parameter is set to 12.1.0.1 or later.

• The OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter is set to false (default).

• You want to disable adaptive optimization for testing purposes so that the database generates only reports.

To disable adaptive optimization and view reports: 

1. Connect SQL*Plus to the database as SYSTEM, and then query the current settings.

   For example, run the following SQL*Plus command:

   SHOW PARAMETER OPTIMIZER_ADAPTIVE_REPORTING_ONLY
   
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   optimizer_adaptive_reporting_only    boolean     FALSE
   

2. At the session level, set the OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter to true.

   For example, in SQL*Plus run the following SQL statement:

   ALTER SESSION SET OPTIMIZER_ADAPTIVE_REPORTING_ONLY=true;
   

3. Run a query.

4. Run DBMS_XPLAN.DISPLAY_CURSOR to view the report.

   Note:

   The format argument that you pass to DBMS_XPLAN.DISPLAY_CURSOR must include the +REPORT parameter. When this parameter is set, the report shows the plan the optimizer would have picked if automatic reoptimization had been enabled.

14.3.1 About Optimizer Hints

Use hints to influence the optimizer mode, query transformation, access path, join order, and join methods. For example, Figure 14-2 shows how you can use a hint to tell the optimizer to use a specific index for a specific statement. Oracle Database SQL Language Reference lists the most common hints by functional category.

Figure 14-2 Optimizer Hint

Description of "Figure 14-2 Optimizer Hint"

The advantage of hints is that they enable you to make decisions normally made by the optimizer. In a test environment, hints are useful for testing the performance of a specific access path. For example, you may know that an index is more selective for certain queries, as in Figure 14-2. In this case, the hint may cause the optimizer to generate a better plan.

The disadvantage of hints is the extra code that you must manage, check, and control. Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Because changes in the database and host environment can make hints obsolete or have negative consequences, a good practice is to test using hints, but use other techniques to manage execution plans.

Oracle provides several tools, including SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to address performance problems not solved by the optimizer. Oracle strongly recommends that you use these tools instead of hints because they provide fresh solutions as the data and database environment change.

SELECT /*+ FIRST_ROWS(10) */ prod_id, time_id FROM 2010_sales
UNION ALL
SELECT /*+ ALL_ROWS */ prod_id, time_id FROM current_year_sales;

SELECT /*+ hint_text */ ...

SELECT   /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk) 
           USE_MERGE(j) FULL(j) */
         e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM     employees e1, employees e2, job_history j
WHERE    e1.employee_id = e2.manager_id
AND      e1.employee_id = j.employee_id
AND      e1.hire_date = j.start_date
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;

14.3.2 Guidelines for Join Order Hints

The join order can have a significant effect on the performance of a SQL statement. In some cases, you can specify join order hints in a SQL statement so that it does not access rows that have no effect on the result.

The driving table in a join is the table to which other tables are joined. In general, the driving table contains the filter condition that eliminates the highest percentage of rows in the table.

Consider the following guidelines:

• Avoid a full table scan when an index retrieves the requested rows more efficiently.

• Avoid using an index that fetches many rows from the driving table when you can use a different index that fetches a small number of rows.

• Choose the join order so that you join fewer rows to tables later in the join order.

The following example shows how to tune join order effectively:

SELECT *
FROM   taba a, tabb b, tabc c
WHERE  a.acol BETWEEN   100 AND   200

AND    b.bcol BETWEEN 10000 AND 20000
AND    c.ccol BETWEEN 10000 AND 20000
AND    a.key1 = b.key1
AND    a.key2 = c.key2;

1. Choose the driving table and the driving index (if any).

   Each of the first three conditions in the previous example is a filter condition that applies to a single table. The last two conditions are join conditions.

   Filter conditions dominate the choice of driving table and index. In general, the driving table contains the filter condition that eliminates the highest percentage of rows. Thus, because the range of 100 to 200 is narrow compared with the range of acol, but the ranges of 10000 and 20000 are relatively large, taba is the driving table, all else being equal.

   With nested loops joins, the joins occur through the join indexes, which are the indexes on the primary or foreign keys used to connect that table to an earlier table in the join tree. Rarely do you use the indexes on the non-join conditions, except for the driving table. Thus, after taba is chosen as the driving table, use the indexes on b.key1 and c.key2 to drive into tabb and tabc, respectively.

2. Choose the best join order, driving to the best unused filters earliest.

   You can reduce the work of the following join by first joining to the table with the best still-unused filter. Thus, if bcol BETWEEN ... is more restrictive (rejects a higher percentage of the rows) than ccol BETWEEN ..., then the last join becomes easier (with fewer rows) if tabb is joined before tabc.

3. You can use the ORDERED or STAR hint to force the join order.