優化器革命之-Dynamic Sampling（五）
我們看看在有索引的情況下，索引的存在是否會對動態取樣有幫助。
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
非常棒，通過索引估計的基數值非常的精確，這個跟我們的預期是相同的。
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.
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 優化器革命之-Dynamic Sampling（二）優化
- 優化器革命之-Dynamic Sampling（四）優化
- 優化器革命之-Dynamic Sampling（三）優化
- 優化器革命之- adaptive cursor sharing （三）優化APT
- 優化器革命之- adaptive cursor sharing （二）優化APT
- 9i,10g 優化器動態取樣級別引數 -- optimizer_dynamic_sampling (統計優化)優化
- oracle10g的dynamic samplingOracle
- Pipelined table function statistics and dynamic samplingFunction
- optimizer_dynamic_sampling引數的理解
- oracle動態取樣_optimizer_dynamic_samplingOracle
- 最佳化器革命之- adaptive cursor sharing （三）APT
- 最佳化器革命之- adaptive cursor sharing （四）APT
- oracle動態取樣_optimizer_dynamic_sampling(二)Oracle
- ptimizer_dynamic_sampling設定為4的作用。
- sql profile禁用oracle動態取樣dynamic samplingSQLOracle
- Android 效能優化（五）之細說 BitmapAndroid優化
- 前端效能優化之桌面瀏覽器優化策略前端優化瀏覽器
- CUDA優化之執行配置和暫存器優化優化
- dbms_stats.lock_table_stats與動態取樣（Dynamic Sampling）
- KUDU(五)kudu優化優化
- 前端效能優化之移動端瀏覽器優化策略前端優化瀏覽器
- oracle動態取樣dynamic sampling hint為何不生效一點思考Oracle
- Mysql優化系列之——優化器對子查詢的處理MySql優化
- MSSQL優化之索引優化SQL優化索引
- CUDA優化之指令優化優化
- 運籌優化（五）--線性規劃之內點法優化
- MySQL查詢優化之優化器工作流程以及優化的執行計劃生成MySql優化
- 優化器優化
- 前端效能優化 之 瀏覽器快取前端優化瀏覽器快取
- 前端效能JQuery篇之選擇器優化前端jQuery優化
- sql優化之邏輯優化SQL優化
- MySQL 效能優化之索引優化MySql優化索引
- MySQL 效能優化之SQL優化MySql優化
- Oracle優化的五個方面Oracle優化
- 「視訊直播技術詳解」系列之五：延遲優化優化
- Oracle 11g中dynamic sampling自動調節（auto-adjusted）機制Oracle
- Flutter 的革命之處Flutter
- Android優化之佈局優化Android優化