動态采樣(Dynamic Sampling)是在ORACLE 9i Release 2中開始引入的一個技術，引入它的目的是為了應對資料庫對象沒有分析（統計資訊缺失）的情況下，優化器生成更好的執行計劃。簡單的說，在資料庫段（表、索引、分區）對象沒有分析的情況下，為了使CBO優化器得到足夠多的資訊以保證優化器做出正确執行計劃而發明的一種技術。它會分析一定數量段對象上的資料塊擷取CBO需要的統計資訊。動态采樣技術僅僅是統計資訊的一種補充，它不能完全替代統計資訊分析。
注意：動态采樣在Oracle 11g之前稱為 Dynamic Sampling, ORACLE 12c之後改名為Dynamic Statistic.
Oracle11G R2 預設的采樣級别：
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL> show parameter Dynamic Statistic
動态采樣的級别有11個級别：請自行檢視官方文檔
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30101
動态采樣實驗：
1、建立測試表test
SQL> create table test as select * from dba_objects;
Table created.
SQL> select count(1) from test;
COUNT(1)
----------
86259
2、不使用動态采樣，檢視執行計劃
SQL> set autotrace traceonly explain;
SQL> select /*+ dynamic_sampling(test 0) */ * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 100K| 19M| 336 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 100K| 19M| 336 (1)| 00:00:05 |
Note
-----
- dynamic sampling used for this statement (level=2)
從上面可以看出，次數優化器估計表test的行數顯示為100K，我們再看下面使用動态采樣的執行計劃，優化器會估算多少行：
3、使用動态采樣，檢視執行計劃（下面是直接查詢的，因為在11G 是預設啟用動态采樣的）
SQL> select * from test;
| 0 | SELECT STATEMENT | | 72258 | 14M| 336 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 72258 | 14M| 336 (1)| 00:00:05 |
如果啟用動态采樣(預設情況下，動态采樣級别為2)，優化器根據動态采樣得到一些資料資訊猜測、估計表TEST的記錄行數為86259，已經接近實際記錄行數72258了。比不做動态采樣分析要好很多了。
如果我們将動态采樣的級别提高為3，如下所示，發現優化器根據動态采樣得到的資訊比預設（預設情況下，動态采樣級别為2）情況獲得的資訊更準确。優化器估計表TEST的行數為92364，比72258又接近實際情況一步了。
SQL> select /*+ dynamic_sampling(test 3) */ * from test;
| 0 | SELECT STATEMENT | | 92364 | 18M| 336 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 92364 | 18M| 336 (1)| 00:00:05 |
4、在Tom大師的這篇文章中提到，在沒有動态采樣的情況下，如果删除了該表資料，CBO優化器估算的結果集和沒有删除之前是一樣的。
這是因為當一個表的資料被删除後，這個表所配置設定的extent和block是不會自動回收的（高水位線不變），是以CBO如果沒有采樣資料塊做分析，隻是從資料字典中擷取extend等資訊，就會誤認為任然還有那麼多資料。下面我們把test表資料清空，看看執行計劃如何
SQL> delete from test;
86259 rows deleted.
SQL> commit;
SQL> select /*+ dynamic_sampling(test 0) */ * from test; ----不使用動态采樣
SQL> select * from test; -----使用動态采樣
| 0 | SELECT STATEMENT | | 1 | 207 | 335 (0)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 207 | 335 (0)| 00:00:05 |
從上面的檢視可以看出，不采用動态采樣和采用動态采樣的差別；
5、我們對test表收集下統計資訊：再次查詢，該表的執行計劃就會少了：dynamic sampling
SQL>
第二種情況：當表TEST即使被分析過，如果查詢腳本裡面包含臨時表，就會使用動态采樣技術。因為臨時表是不會被分析，它是沒有統計資訊的。如下所示：
SQL> drop table test;
SQL> create table test as select * from dba_objects;
SQL> exec dbms_stats.gather_table_stats(ownname =>'SYS',tabname =>'TEST',cascade=>TRUE);
SQL> create global temporary table tmp (object_type varchar2(19));
SQL> insert into tmp select distinct object_type from dba_objects;
44 rows created.
然後檢視下面查詢語句的執行計劃：
SQL> select t.owner,l.object_type from test t inner join tmp l on t.object_type=l.object_type;
Plan hash value: 19574435
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 26 | 338 (1)| 00:00:05 |
|* 1 | HASH JOIN | | 1 | 26 | 338 (1)| 00:00:05 |
| 2 | TABLE ACCESS FULL| TMP | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 86260 | 1263K| 336 (1)| 00:00:05 |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_TYPE"="L"."OBJECT_TYPE")
從上面可以看到 雖然是對tmp表執行的而是全表掃描，但是優化器隻是估算了1行資料
6、動态采樣還有一個獨特能力，可以對不同列之間的相關性做統計。
表統計資訊都是相對獨立的。當查詢涉及列之間的相關性時,統計資訊就顯得有些不足了,請看Tom大師的例子
6.1、建立一個特殊的表t，然後對字段flag1、flag2建立索引t_idx，然後分析收集統計資訊
SQL> create table t as select decode(mod(rownum,2),0,'N', 'Y') flag1, decode(mod(rownum,2),0,'Y', 'N') flag2, a.* from all_objects a;
SQL> create index t_idx on t(flag1, flag2);
SQL> begin
dbms_stats.gather_table_stats(user, 'T',
method_opt =>'for all indexed columns size 254');
end;
/
PL/SQL procedure successfully completed.
6.2、檢視表的行數：
SQL> select num_rows, num_rows/2, num_rows/2/2 from user_tables where table_name='T';
NUM_ROWS NUM_ROWS/2 NUM_ROWS/2/2
---------- ---------- ------------
84396 42198 21099
6.3、看看對flag1過濾條件的SQL語句的執行計劃：
SQL> select * from t where flag1='N';
Plan hash value: 1601196873
| 0 | SELECT STATEMENT | | 42937 | 4276K| 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 42937 | 4276K| 342 (1)| 00:00:05 |
1 - filter("FLAG1"='N')
從上面的執行計劃可以看出：CBO優化器猜測、估計的行數42937， 相當接近42198記錄數了
6.4、看看對flag2過濾條件的SQL語句的執行計劃：
SQL> select * from t where flag2='N';
| 0 | SELECT STATEMENT | | 41459 | 4129K| 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 41459 | 4129K| 342 (1)| 00:00:05 |
1 - filter("FLAG2"='N')
從上面的執行計劃可以看出：CBO優化器猜測、估計的行數41459， 相當接近42198記錄數了
6.5、如果條件flag1 = 'N' and flag2 = 'N',我們根據邏輯推理判斷這樣的記錄肯定是不存在的，這也是苦心構造這個特例的初衷。下面看看CBO優化器怎麼探測、預測的
SQL> select * from t where flag1 = 'N' and flag2 = 'N';
| 0 | SELECT STATEMENT | | 21093 | 2101K| 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 21093 | 2101K| 342 (1)| 00:00:05 |
1 - filter("FLAG2"='N' AND "FLAG1"='N')
從上面看：CBO估計的記錄數為12468，和實際情況相差非常遠。其實是CBO優化器這樣估算來的：
flag1=‘N' 的記錄數占總數的1/2
flag2= 'N' 的記錄數占總數的1/2
6.6、根據NUM_ROWS/2/2 =12468.這樣顯然是不合理的。下面我們通過提升動态采樣級别，來看看動态采樣是否能避免CBO的錯誤：
SQL> select /*+ dynamic_sampling(t 3) */ * from t where flag1 = 'N' and flag2 = 'N';
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 6 | 612 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 612 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 6 | | 1 (0)| 00:00:01 |
2 - access("FLAG1"='N' AND "FLAG2"='N')
注意：
①：采樣級别越高，采樣的資料塊越多，得到的分析資料就越接近于真實，但同時伴随着資源消耗的開銷也增加了。這時一個需要權衡考慮的東西。ORACLE 10 g & 11g的預設采樣級别都為2,一般使用在會話中使用dynamic_sampling提示來修改動态采樣級别。
②：凡事有利必有弊，動态采樣也不是神器。它采樣的資料塊越多，系統開銷就越大，這樣會增加SQL硬解析的時間，如果是資料庫倉庫（DW、OLAP）環境，SQL執行時間相當長，硬解析時間隻占整個SQL執行時間的一小部分，那麼可以适當的提高動态采樣級别，這樣是有利于優化器擷取更加正确的資訊。一般設定為3或4比較合适。
③：在并發比較嚴重的OLTP系統中，每秒中有成千上萬的SQL語句執行，它要求SQL語句短小、執行時間短，是以在OLTP系統中應該減低動态采樣級别或不用動态采樣。
本文轉自一個笨小孩51CTO部落格，原文連結：http://blog.51cto.com/fengfeng688/1920186 ，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)