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 |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
|
|
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
