關於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.  

1 2 下一頁