The following table lists some of the most important optimizer parameters.

The parameter accepts one of a list of valid string values corresponding to the release numbers, such as 11.2.0.2 or 12.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 12c Release 1 (12.1.0.2) to Oracle Database 12c Release 2 (12.2.0.1), then the default value of the OPTIMIZER_FEATURES_ENABLE parameter changes from 12.1.0.2 to 12.2.0.1.

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 12c Release 1 (12 1.0.2) to Oracle Database 12c Release 2 (12.2.0.1).

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

To enable query optimizer features for a specific release:

1. Log in 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      12.2.0.1
   

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 12.1.0.2:

   SQL> ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='12.1.0.2';

   The preceding statement restores the optimizer functionality that existed in Oracle Database 12c Release 1 (12.1.0.2).

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';

Adaptive plans are enabled when the following initialization parameters are set:

• OPTIMIZER_ADAPTIVE_PLANS is TRUE (default)

• OPTIMIZER_FEATURES_ENABLE is 12.1.0.1 or later

• OPTIMIZER_ADAPTIVE_REPORTING_ONLY is 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.

Adaptive statistics are enabled when the following initialization parameters are set:

• OPTIMIZER_ADAPTIVE_STATISTICS is TRUE (the default is FALSE)

• OPTIMIZER_FEATURES_ENABLE is 12.1.0.1 or later

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 plans for testing purposes so that the database generates only reports.

To disable adaptive plans:

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 with the +REPORT parameter.

   When the +REPORT parameter is set, the report shows the plan the optimizer would have picked if automatic reoptimization had been enabled.

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.