Oracle動態採樣詳解

動態採樣概述

動態採樣（Dynamic Sampling）技術的最初提出是在Oracle 9i R2，在段（表，索引，分區）沒有分析的情況下，為了使CBO 最佳化器得到足夠的資訊以保證做出正確的執行計畫而發明的一種技術，可以把它看做分析手段的一種補充。
當段對象沒有統計資訊時（即沒有做分析），動態採樣技術可以通過直接從需要分析的對象上收集資料區塊（採樣）來獲得CBO需要的統計資訊。

一個簡單的例子：
建立表：
SQL>  create table t as select  owner,object_type from dba_objects;
Table created.
查看錶的記錄數：
SQL> select count(*) from t
  COUNT(*)
----------
    50419  -- 記錄數
這裡建立了一張普通表，沒有做分析，我們在hint中用0級來限制動態採樣，此時CBO唯一可以使用的資訊就是表格儲存體在資料字典中的一些資訊，如有多少個extent，有多少個block，但是這些資訊是不夠的。
SQL> set autotrace trace exp
SQL> select /*+ dynamic_sampling(t 0) */ * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      | 12007 |  328K|    34  (0)| 00:00:01 |
|  1 |  TABLE ACCESS FULL| T    | 12007 |  328K|    34  (0)| 00:00:01 |
--------------------------------------------------------------------------
在沒有做動態分析的情況下 ，CBO估計的記錄數是 12007條，與真實的 50419相差甚遠。
動態分析來後：
SQL> select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      | 45596 |  1246K|    35  (3)| 00:00:01 |
|  1 |  TABLE ACCESS FULL| T    | 45596 |  1246K|    35  (3)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
  - dynamic sampling used for this statement
在Oracle 10g中預設對沒有分析的段做動態採樣，上面的查詢結果顯示使用了動態採樣，CBO計的結果是 45596與 50419很接近了。 由於動態採樣只是對有限的一些資料區塊做分析，來對整個表做出估算，所以無法和實際值完全吻合也是很正常的。
注意：在沒有動態採樣的情況下，對於沒有分析過的段，CBO也可能錯誤地將結果判斷的程度擴大話 。
見下列
SQL> delete from t;
50419 rows deleted.
SQL> set autotrace trace exp               
SQL> select /*+ dynamic_sampling(t 0) */ * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
-----------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      | 12007 |  328K|    34  (0)| 00:00:01 |
|  1 |  TABLE ACCESS FULL| T    | 12007 |  328K|    34  (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    1 |    28 |    34  (0)| 00:00:01 |
|  1 |  TABLE ACCESS FULL| T    |    1 |    28 |    34  (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
  - dynamic sampling used for this statement
可以看到，在沒有採用動態分析的情況下，CBO對t表估計的還是12007行記錄 ，但是用動態分析就顯示1條記錄。 而表中的資料在查詢之前已經刪除掉了，出現這種情況的原因是因為高水位。 由於沒有採用動態採樣時的表資訊來自 前面提到的資料字典中的 extent和block資訊， 雖然表的資料已經刪除，但是表分配的extent 和block沒有被回收，在這種情況下CBO 依然認為有那麼多的資料存在。
通過這一點，我們可以看出，此時CBO能夠使用的資訊非常有限，也就是這個表有幾個extent，有幾個block。但動態採樣之後，Oracle 立即發現，原來資料區塊中都是空的。
如果是通過設定sql_trace=true來查看執行計畫，動態採樣會體現出如下資訊：
********************************************************************************
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
  NVL(SUM(C2),:"SYS_B_1")
FROM
 (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T1") FULL("T1")
  NO_PARALLEL_INDEX("T1") */ :"SYS_B_2" AS C1, CASE WHEN "T1"."ID"=:"SYS_B_3"
  THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "T1" SAMPLE BLOCK
  (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8") "T1") SAMPLESUB
call    count      cpu    elapsed      disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00      0.00          0          0          0          0
Execute      1      0.00      0.00          0          0          0          0
Fetch        1      0.03      0.09        171        70          0          1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.03      0.10        171        70          0          1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55    (recursive depth: 1)
Rows    Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=70 pr=171 pw=0 time=97308 us)
  14049  TABLE ACCESS SAMPLE T1 (cr=70 pr=171 pw=0 time=720915 us)
********************************************************************************
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled',
  'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE
  */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), NVL(SUM(C3),:"SYS_B_2")
FROM
 (SELECT /*+ NO_PARALLEL("T1") INDEX("T1" T1_INX) NO_PARALLEL_INDEX("T1") */
  :"SYS_B_3" AS C1, :"SYS_B_4" AS C2, :"SYS_B_5" AS C3  FROM "T1" "T1" WHERE
  "T1"."ID"=:"SYS_B_6" AND ROWNUM <= :"SYS_B_7") SAMPLESUB
call    count      cpu    elapsed      disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00      0.00          0          0          0          0
Execute      1      0.00      0.00          0          0          0          0
Fetch        1      0.00      0.00          0          2          0          1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00      0.00          0          2          0          1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55    (recursive depth: 1)
Rows    Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=2 pr=0 pw=0 time=660 us)
      1  VIEW  (cr=2 pr=0 pw=0 time=521 us)
      1    COUNT STOPKEY (cr=2 pr=0 pw=0 time=368 us)
      1    INDEX RANGE SCAN T1_INX (cr=2 pr=0 pw=0 time=156 us)(object id 52550)

動態採樣的作用
1、CBO 依賴的是充分的統計分析資訊，但是並不是每個使用者都會非常認真，及時地去對每個表做分析。為了保證執行計畫都儘可能地正確，Oracle需要使用動態採樣技術來協助CBO 擷取儘可能多的資訊。
2、全域暫存資料表。通常來講，暫存資料表的資料是不做分析的，因為它存放的資料是臨時性的，可能很快就釋放了，但是當一個查詢關聯到這樣的暫存資料表時，CBO要想獲得暫存資料表上的統計資訊分析資料，就只能依賴於動態採樣了。
3、動態採樣除了可以在段對象沒有分析時，給CBO提供分析資料之外，還有一個獨特的能力，它可以對不同列之間的相關性做統計。這點通常發生在表設計不符合3NF的情況下，這個特性在表 符合3NF設計的 情況下少見。

動態採樣的層級
Level 0：不做動態分析
Level 1：Oracle 對沒有分析的表進行動態採樣，但需要同時滿足以下4個條件。
1、SQL中至少有一個未分析的表
2、未分析的表出現在關聯查詢或者子查詢中
3、未分析的表沒有索引
4、未分析的表佔用的資料區塊要大於動態採樣預設的資料區塊（32個）
Level 2：對所有的未分析表做分析，動態採樣的資料區塊是預設資料區塊的2倍，即64個。
Level 3：採樣的表包含滿足Level 2定義的所有表，同時包括，那些謂詞有可能潛在地需要動態採樣的表，這些動態採樣的資料區塊為預設資料區塊，對沒有分析的表，動態採樣的預設塊為預設資料區塊的2倍， 即64個 。
Level 4：採樣的表包含滿足Level 3定義的表，同時還包括一些表，他們包含一個單表的謂詞會引用另外的2個列或者更多的列；採樣的塊數是動態採樣預設資料區塊數；對沒有分析的表，動態採樣的資料區塊為預設資料區塊的2倍。
Level 5，6，7，8，9：採樣的表包含滿足Level 4定義的表，同時分別使用動態採樣預設資料區塊的2，4，8，32，128 倍的數量來做動態分析。
Level 10：採樣的表包含滿足Level 9定義的所有表，同時對錶的所有資料進行動態採樣。
採樣的資料區塊越多，得到的分析資料就越接近與真實，但同時伴隨著資源消耗的也越大。
什麼時候使用動態採樣
儘管看到動態採樣的優點，但是它的缺點也是顯而易見，否則Oracle一定會一直使用動態採樣來取代資料分析：
1、 在OLAP或者資料倉儲環境下，SQL執行消耗的資源要遠遠大於SQL解析，那麼讓解析在消耗多一點資源做一些動態採樣分析，從而做出一個最優的執行計畫是非常值得的。 實際上在這樣的環境中，硬分析消耗的資源幾乎是可以忽略的。但是 採樣的資料區塊有限，對于海量資料的表，結果難免有偏差。 所以一般在OLAP 或者資料倉儲環境中，將動態採樣的level 設定為3或者4比較好 。
2、 動態採樣需要額外的消耗資料庫資源，所以，如果 SQL被反覆執行，變數被綁定，硬分析很少，在這樣一個環境中，是不宜使用動態採樣的。 動態採樣發生在硬分析時，如果很少有硬分析發生，動態採樣的意義就不大。 當然如果沒有使用綁定變數，導致頻繁進行硬解析和動態採樣消耗過多資源也是不可行的，故OLTP系統非常不適宜使用動態採樣