Oracle 参数 OPTIMIZER_ADAPTIVE_STATISTICS 官方解释,作用,如何配置最优化建议

本站中文解释

OPTIMIZER_ADAPTIVE_STATISTICS是一个oracle参数,其用来启用和禁用自适应统计信息功能,可选值为TRUE或FALSE,默认为FALSE。

当OPTIMIZER_ADAPTIVE_STATISTICS为TRUE时,Oracle在查询执行期间会自动收集统计信息,帮助查询优化器更好地对查询操作执行优化,大大提升了查询的性能。

要正确设置OPTIMIZER_ADAPTIVE_STATISTICS参数,首先要执行ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS=TRUE命令,将此参数值更改为TRUE,以便启用自适应统计信息功能。此外,如果要使设置永久生效,应将此值存入数据库中,可以使用ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS=TRUE SCOPE=SPFILE,将此参数添加到SPFILE中,让其在数据库重启时依旧生效。

官方英文解释

OPTIMIZER_ADAPTIVE_STATISTICS controls adaptive statistics. Some query shapes are too complex to rely on base table statistics alone, so the optimizer augments these statistics with adaptive statistics.

Property Description

Parameter type

Boolean

Default value

false

Modifiable

ALTER SESSION, ALTER SYSTEM

Modifiable in a PDB

Yes

Range of values

true | false

Basic

No

Oracle RAC

The same value must be set on all instances

Setting this parameter to false disables the following adaptive features:

  • SQL plan directives

  • Statistics feedback for joins

  • Adaptive dynamic sampling for parallel execution

Note:

Setting OPTIMIZER_ADAPTIVE_STATISTICS to false preserves the statistics feedback functionality that was introduced in Oracle Database 11g.

OPTIMIZER_ADAPTIVE_STATISTICS does not control the creation of SQL plan directives. SQL plan directives will be created even if this parameter is false, but they will not be used to refine SQL execution plans with dynamic sampling.

See Also:

Oracle Database SQL Tuning
Guide
for information about adaptive plans


数据运维技术 » Oracle 参数 OPTIMIZER_ADAPTIVE_STATISTICS 官方解释,作用,如何配置最优化建议