最近遇到一个动态采样带来的性能问题,让我着实有点不可理解,通过查看文档以及做实验,现总结如下:
我们先来看看Online Document关于动态采用的解释
Oracle 10GR2 documentation:
This dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter.
For dynamic sampling to automatically gather the necessary statistics, this parameter should be set to a value of 2 or higher.
The default value is 2. See "Dynamic Sampling Levels" for information about the sampling levels that can be set.
Oracle 11GR2 documentation:
When the Optimizer Uses Dynamic Sampling
During compilation, the optimizer decides whether to use dynamic sampling based on a number of factors,
including whether the statements use parallel processing or serial processing.
For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use.
The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be
resource-intensive,so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the OPTIMIZER_DYNAMIC_SAMPLING setting
unless set to a nondefault value, in which case the value is honored.
For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and
is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time
could have a huge impact on their performance.
注:大家注意到没有11.2.0.*中如果语句是并行执行,那么是否采用dynamic sampling以及level将以表的大小,sql中table join的复杂度来决定,也就是又CBO来决定采样的level值。
CBO将忽略OPTIMIZER_DYNAMIC_SAMPLING的默认值。对于Oracle的各种特性,如果有自动的功能,多多少少都不太稳定,总会出问题的。当然，对于串行执行的语句依旧动态采样的level 将仍然有OPTIMIZER_DYNAMIC_SAMPLING决定,其实大部分时候level 2已经完全足够。
下面来看看11.2.0.3中动态采用的情况