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

最新推荐文章于 2021-05-22 22:21:35 发布

cotchte0421

最新推荐文章于 2021-05-22 22:21:35 发布

阅读量375

点赞数

我们来看下表级的动态采样DYNAMIC_SAMPLING(alias, )，这个HINT要求输入2个参数，在CURSOR级别的DYNAMIC_SAMPLING只需要输入动态采样的级别就好，但是表级还需要输入表的alias作为第一个参数，如：

DYNAMIC_SAMPLING(T, 4)

不过有趣的是，这种表级别的动态采样跟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';

我们查询一个不存在的值：

select
count(*) as cnt
from
t
where
attr1 = 19
and id>1;

CNT
----------
0

Execution Plan
----------------------------------------------------------
Plan hash value: 801713572

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | INDEX RANGE SCAN| AAAA | 1 | 16 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

2 - access("ATTR1"=19 AND "ID">1 AND "ID" IS NOT NULL)

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

我们看到输出结果也没有使用动态采样。

*** 2014-07-17 16:13:31.640
** Performing dynamic sampling initial checks. **
Column (#1): ID( NO STATISTICS (using defaults)
AvgLen: 13 NDV: 31250 Nulls: 0 Density: 0.000032
Column (#2):
NewDensity:0.050000, OldDensity:0.000000 BktCnt:1000000, PopBktCnt:1000000, PopValCnt:10, NDV:10
Column (#2): ATTR1(
AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.050000 Min: 1 Max: 10
Histogram: Freq #Bkts: 10 UncompBkts: 1000000 EndPtVals: 10
Using prorated density: 0.000000 of col #2 as selectvity of out-of-range/non-existent value pred
** Dynamic sampling initial checks returning TRUE (level = 4).
Using prorated density: 0.000000 of col #2 as selectvity of out-of-range/non-existent value pred

*** 2014-07-17 16:13:31.640
** 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), NVL(SUM(C3),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, CASE WHEN "T"."ID">1 AND "T"."ATTR1"=19 THEN 1 ELSE 0 END AS C3 FROM "TEST"."T" SAMPLE BLOCK (0.193291 , 1) SEED (1) "T") SAMPLESUB

*** 2014-07-17 16:13:31.646
** Executed dynamic sampling query:
level : 4
sample pct. : 0.193291
actual sample size : 2458
filtered sample card. : 0
filtered sample card. (index AAAA): 0
orig. card. : 1000000
block cnt. table stat. : 16038
block cnt. for sampling: 16038
max. sample block cnt. : 32
sample block cnt. : 31
min. sel. est. : 0.00000002
** Not using dynamic sampling for single table sel. or cardinality.
DS Failed for : ----- Current SQL Statement for this session (sql_id=1bx1yccvbnspn) -----

从trace文件可以看到动态采样实际发生了，但是并没有采纳动态采样结果。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-1221265/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-1221265/

cotchte0421

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
优化器革命之-Dynamic Sampling（四）

我们来看下表级的动态采样DYNAMIC_SAMPLING(alias, )，这个HINT要求输入2个参数，在CURSOR级别的DYNAMIC_SAMPLING只需要输入动态采样的级别就好，但是表级还需要输入表的alias作...
复制链接

扫一扫