優化器革命之-Dynamic Sampling（四）

不過有趣的是，這種表級別的動態取樣跟session/system和cursor level是不同的。
第一個不同：session/system和cursor level的動態取樣不是一定發生的，而是需要滿足條件才發生的。
第二個不同：取樣的塊數也與session/system和cursor level不同。
session/system和cursor level的取樣塊數前面已經有過表格說明，這裡不再給出。我們看下錶級別的取樣塊數，一個簡單的公式：
2^(level - 1)*_optimizer_dyn_smp_blks (_optimizer_dyn_smp_blks的預設值為 32)

因此level 1的取樣塊數為

2^0*32 = 1*32 = 32

level 4的取樣塊數為

2^3*32 = 8 * 32 = 256

level 9的取樣塊數為

2^8*32 = 256 * 32 = 8192

level 10再次出現了跳躍，有2的32次方個塊，也就是一個表中所有的塊。讀者可以自己對比下，表級別的取樣塊數與session/system和cursor level的不同。
這裡還需要注意一點，不知道是不是由於BUG，在使用表級的動態取樣後，執行計劃的輸出Note部分並不能真正的顯示SQL使用的取樣級別，而只是預設顯示系統當前的optimizer_dynamic_sampling引數設定的取樣級別。

test@DLSP>alter session set events '10053 trace name context forever, level 1';


Session altered.


test@DLSP>select   /*+ dynamic_sampling(t,5) */
  2            count(*) as cnt
  3    from
  4            t
  5    where
  6            attr1  = 19
  7    and id>1;

alter session set events '10053 trace name context off';
       CNT
----------
         0




Execution Plan
----------------------------------------------------------


Plan hash value: 2966233522


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |  3536   (1)| 00:00:43 |
|   1 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    22 |   572 |  3536   (1)| 00:00:43 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("ATTR1"=19 AND "ID">1)


Note
-----
   - dynamic sampling used for this statement (level=2)
test@DLSP>show parameter dy


NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_dynamic_sampling           integer                2

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),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."ATTR1"=19 AND "T"."ID">1 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (3.186183 , 1) SEED (1) "T") SAMPLESUB


*** 2014-07-17 16:33:08.779
** Executed dynamic sampling query:
    level : 5
    sample pct. : 3.186183
    actual sample size : 31062
    filtered sample card. : 0
    orig. card. : 1309984
    block cnt. table stat. : 16038
    block cnt. for sampling: 16038
    max. sample block cnt. : 512
    sample block cnt. : 511
    min. sel. est. : 0.00050000
** Using dynamic sampling card. : 974897
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.00002231
  Table: T  Alias: T
    Card: Original: 974896.978474  Rounded: 22  Computed: 21.75  Non Adjusted: 21.75

雖然我說過表級別的動態取樣會無條件的被執行，但是取樣的結果會被做檢查，如果發現不符合要求，取樣的結果會被拋棄。
最簡單的看到這種現象發生的情況是，構造一個表，分析統計資訊，然後SQL中不要任何謂詞如：
sys@DLSP>@tabstat
Please enter Name of Table Owner: test
Please enter Table Name : t

**********************************************************
Table Level
**********************************************************

Table                                  Number                        Empty    Chain Average Global         Sample Date
Name                                  of Rows          Blocks       Blocks    Count Row Len Stats            Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
T                                   1,000,000          160,38            0        0     109 YES         1,000,000 07-17-2014


Column                             Distinct              Number       Number         Sample Date
Name                                 Values     Density Buckets        Nulls           Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
ID                                1,000,000   .00000100       1            0      1,000,000 07-17-2014
ATTR1                                    10   .10000000       1            0      1,000,000 07-17-2014
FILLER                                    1  1.00000000       1            0      1,000,000 07-17-2014

alter session set events '10053 trace name context forever, level 1';
select /*+ DYNAMIC_SAMPLING(t 4) */
        count(*) as cnt
from
        t;
       CNT
----------
   1000000


1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522


-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  3531   (1)| 00:00:43 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000K|  3531   (1)| 00:00:43 |
-------------------------------------------------------------------


alter session set events '10053 trace name context off';
好像是動態取樣沒有發生，但是事實不是這樣的，由於是表級別的動態取樣，所以一定會發生，會無條件的發生！我們看下trace檔案的資訊：

** Generated dynamic sampling query:
    query text :
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),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2 FROM "TEST"."T" SAMPLE BLOCK (1.589974 , 1) SEED (1) "T") SAMPLESUB


*** 2014-07-17 13:56:08.564
** Executed dynamic sampling query:
    level : 4
    sample pct. : 1.589974
    actual sample size : 4159
    filtered sample card. : 4159
    orig. card. : 1000000
    block cnt. table stat. : 16038
    block cnt. for sampling: 16038
    max. sample block cnt. : 256
    sample block cnt. : 255
    min. sel. est. : -1.00000000
** Not using dynamic sampling for single table sel. or cardinality.

請注意跟蹤檔案裡的輸出，確實做了塊取樣，取樣的塊數為255，但是塊取樣的結果並沒有被採納。
還可以舉出一些其他案例，例如，動態取樣在處理一些表中極小的結果集的時候可能會存在一些異常，這些異常可能會導致非常不準確的基數估計或者動態取樣的結果不被採用。
select distinct attr1 from t; 


     ATTR1
----------
         1
         6
         2
         4
         5
         8
         3
         7
         9
        10

begin
  dbms_stats.gather_table_stats(ownname          =>'test',
                                tabname          => 't',
                                no_invalidate    => FALSE,
                                estimate_percent => 100,
                                force            => true,
                                degree         => 5,
                                method_opt       => 'for  columns attr1 size 254',
                                cascade          => true);
end;
/
收集了直方圖

alter session set optimizer_dynamic_sampling=4;
alter session set events '10053 trace name context forever, level 1';