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

wei-xh發表於2014-07-18
優化
我們看看在有索引的情況下，索引的存在是否會對動態取樣有幫助。
test@DLSP>create table t
  2  as
  3  select
  4          rownum as id
  5          , mod(rownum, 10) + 1 as attr1
  6          , rpad('x', 100) as filler
  7  from
  8            dual
  9  connect by
 10            level <= 1000000
 11  ;


Table created.


test@DLSP>show parameter dy


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

test@DLSP>create index tt on t(attr1);




Index created.

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


Session altered.


test@DLSP>select count(*) from t where   attr1=1;



  COUNT(*)
----------
    100000




Execution Plan
----------------------------------------------------------
Plan hash value: 3661641528


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |   205   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE   |      |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| TT   | 90118 |  1144K|   205   (1)| 00:00:03 |
--------------------------------------------------------------------------


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


   2 - access("ATTR1"=1)


Note
-----
   - dynamic sampling used for this statement (level=2)

alter session set events '10053 trace name context off';

*** 2014-07-18 09:17:25.245
** Performing dynamic sampling initial checks. **
  Column (#2): ATTR1(  NO STATISTICS (using defaults)
    AvgLen: 13 NDV: 40937 Nulls: 0 Density: 0.000024
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: TT, blocks=1999
** Dynamic sampling index access candidate : TT
** Dynamic sampling updated table stats.: blocks=16038


*** 2014-07-18 09:17:25.245
** Generated dynamic sampling query:
    query text :
@
                                                                                                                                                     1191,0-1      25%
** 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 /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."ATTR1"=1 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (0.392817 , 1) SEED (1) "T") SAMPLESUB


*** 2014-07-18 09:17:25.247
** Executed dynamic sampling query:
    level : 2
    sample pct. : 0.392817
    actual sample size : 3528
    filtered sample card. : 354
    orig. card. : 1309984
    block cnt. table stat. : 16038
    block cnt. for sampling: 16038
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : 0.01000000
** Using recursive dynamic sampling card. est. : 898128.000000


*** 2014-07-18 09:17:25.247
** Generated dynamic sampling query:
    query text :
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),0), NVL(SUM(C2),0), NVL(SUM(C3),0) FROM (SELECT /*+ NO_PARALLEL("T") INDEX("T" TT) NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2, 1 AS C3  FROM "TEST"."T" "T" WHERE "T"."ATTR1"=1 AND ROWNUM <= 2500) SAMPLESUB


*** 2014-07-18 09:17:25.249
** Executed dynamic sampling query:
    level : 2
    sample pct. : 100.000000
    actual sample size : 898128
    filtered sample card. : 2500
    filtered sample card. (index TT): 2500
    orig. card. : 898128
    block cnt. table stat. : 16038
    block cnt. for sampling: 16038
                                                                                                                                                     1225,1        26%
    block cnt. table stat. : 16038
    block cnt. for sampling: 16038
    max. sample block cnt. : 4294967295
    sample block cnt. : 16038
    min. sel. est. : 0.01000000
** Increasing dynamic sampling selectivity
   for predicate 0 from 0.002784 to 0.100340.
** Increasing dynamic sampling selectivity
   for predicate 1 from 0.002784 to 0.100340.
    index TT selectivity est.: 0.10034014
** Using dynamic sampling card. : 898128
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.10034014

我們看到10053 trace檔案的輸出已經跟以前不一樣了，多了一個取樣SQL出來：
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),0), NVL(SUM(C2),0), NVL(SUM(C3),0) FROM (SELECT /*+ NO_PARALLEL("T") INDEX("T" TT) NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2, 1 AS C3  FROM "TEST"."T" "T" WHERE "T"."ATTR1"=1 AND ROWNUM <= 2500) SAMPLESUB

這個取樣SQL直接查詢了索引，限制條件是小於2500條，那是不是說明如果查詢的索引值多餘2500條，那麼動態取樣的結果跟之前一樣，只是一個大概的估計值，但是如果查詢的索引值小於2500條，根據索引就可以得出非常精準的基數值了？
由於我們這個例子裡查詢的索引值多於了2500條，因此估計的值不是那麼精確，我們看看索引值小於2500的情況。

test@DLSP>select 100000-2500 from dual;         


100000-2500
-----------
      97500


test@DLSP>delete from t where attr1=1 and rownum<97502;


97501 rows deleted.


test@DLSP>commit;


Commit complete.

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


Session altered.


test@DLSP>select     count(*) from t where attr1=1;
alter session set events '10053 trace name context off';


  COUNT(*)
----------
      2499




Execution Plan
----------------------------------------------------------
Plan hash value: 3661641528


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| TT   |  2499 | 32487 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

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


   2 - access("ATTR1"=1)


Note
-----
   - dynamic sampling used for this statement (level=2)



*** 2014-07-18 09:25:31.048
** Performing dynamic sampling initial checks. **
  Column (#2): ATTR1(  NO STATISTICS (using defaults)
    AvgLen: 13 NDV: 40937 Nulls: 0 Density: 0.000024
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: TT, blocks=1999
** Dynamic sampling index access candidate : TT
** Dynamic sampling updated table stats.: blocks=16038


*** 2014-07-18 09:25:31.048
** Generated dynamic sampling query:
    query text :
@
                                                                                                                                                     1191,0-1      25%
*** 2014-07-18 09:25:31.048
** Performing dynamic sampling initial checks. **
  Column (#2): ATTR1(  NO STATISTICS (using defaults)
    AvgLen: 13 NDV: 40937 Nulls: 0 Density: 0.000024
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: TT, blocks=1999
** Dynamic sampling index access candidate : TT
** Dynamic sampling updated table stats.: blocks=16038


*** 2014-07-18 09:25:31.048
** 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 /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN "T"."ATTR1"=1 THEN 1 ELSE 0 END AS C2 FROM "TEST"."T" SAMPLE BLOCK (0.392817 , 1) SEED (1) "T") SAMPLESUB


*** 2014-07-18 09:25:31.050
** Executed dynamic sampling query:
    level : 2
    sample pct. : 0.392817
    actual sample size : 3180
    filtered sample card. : 6
    orig. card. : 1309984
    block cnt. table stat. : 16038
    block cnt. for sampling: 16038
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : 0.01000000
** Using recursive dynamic sampling card. est. : 809537.142857


*** 2014-07-18 09:25:31.050
** Generated dynamic sampling query:
    query text :
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),0), NVL(SUM(C2),0), NVL(SUM(C3),0) FROM (SELECT /*+ NO_PARALLEL("T") INDEX("T" TT) NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2, 1 AS C3  FROM "TEST"."T" "T" WHERE "T"."ATTR1"=1 AND ROWNUM <= 2500) SAMPLESUB


*** 2014-07-18 09:25:31.052
** Executed dynamic sampling query:
    level : 2
    sample pct. : 100.000000
    actual sample size : 809537
    filtered sample card. : 2499
    filtered sample card. (index TT): 2499
    orig. card. : 809537
    block cnt. table stat. : 16038
    block cnt. for sampling: 16038
    max. sample block cnt. : 4294967295
    sample block cnt. : 16038
    min. sel. est. : 0.01000000
    index TT selectivity est.: 0.00308695
** Using dynamic sampling card. : 809537
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.00308695
  Table: T  Alias: T
    Card: Original: 809537.142857  Rounded: 2499  Computed: 2499.00  Non Adjusted: 2499.00

非常棒，通過索引估計的基數值非常的精確，這個跟我們的預期是相同的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-1221955/，如需轉載，請註明出處，否則將追究法律責任。

相關文章