关于Oracle 11g动态采样

关于Oracle 11g动态采样

最近遇到一个动态采样带来的性能问题,让我着实有点不可理解,通过查看文档以及做实验,现总结如下:
我们先来看看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中动态采用的情况

1. SQL> create table leo(id number,  
2.   2  leo_date date,  
3.   3  value varchar2(20),  
4.   4  name varchar2(30)) tablespace users;  
5.   
6. Table created.  
7.   
8. <SPAN style="FONT-FAMILY: 'Comic Sans MS'">用随机数生成100w数据，插入表中。</SPAN>  
9. SQL> insert into leo  
10.   2  select rownum id,sysdate-dbms_random.value(1,500) leo_create,  
11.   3  dbms_random.string('1',15) value,  
12.   4  dbms_random.string('1',20) name  
13.   5  from dual  
14.   6* connect by level<=1e6;  
15. 1000000 rows created.  
16. SQL> commit;  
17. Commit complete  

注:不要收集统计信息

1. SQL> show parameter optimizer  
2. NAME                                 TYPE          VALUE  
3. ------------------------------------ ------------- ------------  
4. optimizer_dynamic_sampling           integer       2  
5. optimizer_features_enable            string        11.2.0.3  
6.   
7. SQL> explain plan for select*from leo;  
8. Explained.  
9.   
10. SQL> @explain  
11. PLAN_TABLE_OUTPUT  
12. ----------------------------------------------------------------------------  
13. Plan hash value: 1174476904  
14. --------------------------------------------------------------------------  
15. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
16. --------------------------------------------------------------------------  
17. |   0 | SELECT STATEMENT  |      |   897K|    43M|  2105   (2)| 00:00:26 |  
18. |   1 |  TABLE ACCESS FULL| LEO  |   897K|    43M|  2105   (2)| 00:00:26 |  
19. --------------------------------------------------------------------------  
20. Note  
21. -----  
22.    - dynamic sampling used for this statement (level=2)  
23. 12 rows selected.  
24. SQL> explain plan for select/*+parallel*/* from leo;  
25. Explained.  
26.   
27. SQL> @explain  
28. PLAN_TABLE_OUTPUT  
29. -----------------------------------------------------------------------------------------------------------------  
30. Plan hash value: 3240177498  
31. -------------------------------------------------------------------------------------------------------------  
32. | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
33. --------------------------------------------------------------------------------------------------------------  
34. |   0 | SELECT STATEMENT     |          |   897K|    43M|  1166   (2)| 00:00:14 |        |      |            |  
35. |   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |  
36. |   2 |   PX SEND QC (RANDOM)| :TQ10000 |   897K|    43M|  1166   (2)| 00:00:14 |  Q1,00 | P->S | QC (RAND)  |  
37. |   3 |    PX BLOCK ITERATOR |          |   897K|    43M|  1166   (2)| 00:00:14 |  Q1,00 | PCWC |            |  
38. |   4 |     TABLE ACCESS FULL| LEO      |   897K|    43M|  1166   (2)| 00:00:14 |  Q1,00 | PCWP |            |  
39. --------------------------------------------------------------------------------------------------------------  
40. Note  
41. -----  
42.    - dynamic sampling used for this statement (level=6)  
43.    - - automatic DOP: skipped because of IO calibrate statistics are missing  
44. 16 rows selected.