Oracle sql tuning guide 翻译 Part 7 --- 启用优化器功能、选择优化器目标 - 实践

19.2.2 Enabling Optimizer Features

The OPTIMIZER_FEATURES_ENABLE initialization parameter (or hint) controls a set of optimizer related features, depending on the database release.

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 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.

19.2.2 启用优化器功能

OPTIMIZER_FEATURES_ENABLE初始化参数（或提示符）控制着一组与优化器相关的功能，具体功能集取决于数据库发行版本。

该参数接受与版本号对应的有效字符串值列表，例如11.2.0.2或12.2.0.1。您可以使用此参数在数据库升级后保留优化器的旧行为。例如，假如将Oracle Database 12c Release 1 (12.1.0.2)升级到Oracle Database 12c Release 2 (12.2.0.1)，则OPTIMIZER_FEATURES_ENABLE参数的默认值将从12.1.0.2变为12.2.0.1。

出于向后兼容性考虑，您可能不希望执行计划因新版本中的优化器新功能而改变。在此种情况下，可以将OPTIMIZER_FEATURES_ENABLE设置为较早的版本。如果您升级到新版本并希望启用新版本中的功能，则无需显式设置OPTIMIZER_FEATURES_ENABLE初始化参数。

Caution:

Oracle does not recommend explicitly setting the OPTIMIZER_FEATURES_ENABLE initialization parameter to an earlier release. To avoid SQL performance regression that may result from execution plan changes, consider using SQL plan management instead.

注意：

Oracle 不建议将 OPTIMIZER_FEATURES_ENABLE初始化参数显式设置为早期版本。为避免因执行计划变更可能导致的 SQL 性能退化，请考虑改用 SQL 计划管理效果。

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:

假设

本教程基于以下假设：

• 您最近将数据库从 Oracle Database 12c Release 1 (12.1.0.2) 升级至 Oracle Database 12c Release 2 (12.2.0.1)。

• 您希望保留早期版本中的优化器行为。

要为特定版本启用查询优化器功能：

1. 使用适当权限登录数据库，随后查询当前优化器功能设置。

例如，运行以下 SQL*Plus 命令：

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:

2. 在实例或会话级别设置优化器功能
例如，执行以下SQL语句将优化器版本设置为12.1.0.2：

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

上述语句将恢复Oracle Database 12c Release 1 (12.1.0.2)版本中存在的优化器功能。

See Also:

• "Managing SQL Plan Baselines"

• Oracle Database Reference to learn about optimizer features enabled when you set OPTIMIZER_FEATURES_ENABLE to different release values

另请参阅：
• 《管理SQL计划基线》了解如何维护和使用SQL执行计划的基准配置
• 《Oracle数据库参考》查阅设置OPTIMIZER_FEATURES_ENABLE参数不同版本值时启用的优化器功能详情

19.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.

19.2.3 选择优化器目标

指优化器对资源运用方式的优先级设定。就是优化器目标

凭借 OPTIMIZER_MODE 初始化参数，可以设置以下优化目标：

• 最佳吞吐量（默认）

当 OPTIMIZER_MODE 值设置为 ALL_ROWS 时，数据库采用最少必要资源来处理语句访问的所有行。

对于批处理应用程序（如 Oracle Reports），应优化最佳吞吐量。通常在批处理应用中，吞吐量更为重要，源于用户只关注应用程序完成所需的时间。响应时间相对次要，因为用户不会在应用运行时检查单个语句的结果。

• 最佳响应时间

当 OPTIMIZER_MODE 值设置为 FIRST_ROWS_n 时，数据库以最佳响应时间为目标进行优化，返回前 n 行（n 可为 1、10、100 或 1000）。

对于 Oracle Forms 或 SQL*Plus 等交互式应用程序，应优化响应时间。通常响应时间至关重要，因为交互式用户要求等待查看语句访问的第一行或前几行信息。

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:

假设
本教程基于以下假设：

• 主应用程序为交互式应用，因此应该将数据库实例的优化器目标设置为最小化响应时间

• 仅针对当前会话，您要求运行报表并优化吞吐量

启用特定版本的查询优化器效果：

1. 使用适当权限通过SQLPlus连接数据库，然后查询当前优化器模式
   例如，执行以下SQLPlus命令：

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:

2. 在实例级别优化响应时间。

例如，运行以下 SQL 语句将系统配置为尽可能快速地检索前 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:

3. 仅在会话级别优化报表执行的吞吐量
例如，执行以下SQL语句，配置仅当前会话针对吞吐量进行优化：

See Also:

Oracle Database Reference to learn about the OPTIMIZER_MODE initialization parameter

另请参阅：
《Oracle数据库参考》了解OPTIMIZER_MODE初始化参数的详细说明

19.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. 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

19.2.4 控制自适应优化

在Oracle数据库中，自适应查询优化是指优化器根据运行时收集的统计信息调整执行计划的过程。当满足以下初始化参数设置时，自适应计划功能将被启用：

• OPTIMIZER_ADAPTIVE_PLANS 为 TRUE（默认值）
• OPTIMIZER_FEATURES_ENABLE 为 12.1.0.1 或更高版本
• OPTIMIZER_ADAPTIVE_REPORTING_ONLY 为 FALSE（默认值）

若将 OPTIMIZER_ADAPTIVE_REPORTING_ONLY 设置为 true，自适应优化将以仅报告模式运行。此时数据库会收集自适应优化所需的信息，但不会更改执行计划。自适应计划始终选择默认计划，但同时会采集参数设为 false 时的执行信息。

当满足以下初始化参数设置时，自适应统计机制将被启用：

• OPTIMIZER_ADAPTIVE_STATISTICS 为 TRUE（默认值为 FALSE）
• OPTIMIZER_FEATURES_ENABLE 为 12.1.0.1 或更高版本

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.

假设
本教程基于以下假设：

• OPTIMIZER_FEATURES_ENABLE初始化参数已设置为12.1.0.1或更高版本
• OPTIMIZER_ADAPTIVE_REPORTING_ONLY初始化参数设置为false（默认值）
• 您希望出于测试目的禁用自适应计划能力，使数据库仅生成报告

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:

禁用自适应计划步骤：

1. 使用SYSTEM账户连接SQLPlus至数据库，随后查询当前参数设置例如，执行以下SQLPlus命令：

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:
2. 在会话级别将OPTIMIZER_ADAPTIVE_REPORTING_ONLY初始化参数设置为 true
例如，在SQL*Plus中运行以下SQL语句

3. Run a query.
3. 执行查询语句
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.
4. 使用+REPORT参数运行DBMS_XPLAN.DISPLAY_CURSOR过程

See Also:

• "About Adaptive Query Optimization"

• Oracle Database Reference to learn about the OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter

• Oracle Database PL/SQL Packages and Types Reference to learn

另请参阅：
• 《自适应查询优化原理》了解自适应优化机制
• 《Oracle数据库参考》查阅OPTIMIZER_ADAPTIVE_REPORTING_ONLY初始化参数详解
• 《Oracle数据库PL/SQL程序包与类型参考》学习DBMS_XPLAN包的启用办法