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