我们看看在有索引的情况下,索引的存在是否会对动态采样有帮助。
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-1221955/