关于动态抽样（DynamicSampling） - 数据库编程

ling(t 3) */ * from t where flag1 = 'N' and flag2 = 'N'; Execution Plan ----------------------------- Plan hash value: 470836197 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 6 | 630 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 630 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IDX | 6 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------- 2 - access("FLAG1"='N' AND "FLAG2"='N')

code8: 我们打开SQL_TRACE会看到以下语句：

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),:"SYS_B_00"),
NVL(SUM(C2),:"SYS_B_01"), NVL(SUM(C3),:"SYS_B_02")
FROM
(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T")
NO_PARALLEL_INDEX("T") */ :"SYS_B_03" AS C1, CASE WHEN "T"."FLAG1"=
:"SYS_B_04" AND "T"."FLAG2"=:"SYS_B_05" THEN :"SYS_B_06" ELSE :"SYS_B_07"
END AS C2, CASE WHEN "T"."FLAG2"=:"SYS_B_08" AND "T"."FLAG1"=:"SYS_B_09"
THEN :"SYS_B_10" ELSE :"SYS_B_11" END AS C3 FROM "T" SAMPLE BLOCK
(:"SYS_B_12" , :"SYS_B_13") SEED (:"SYS_B_14") "T") SAMPLESUB

可以看出来优化器在验证其猜想。。。

4、动态采样级别：
现在列出11个级别，详细请参考：http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i43032
1）Level 0: Do not use dynamic sampling.
0级：不使用动态采样。

2）Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
1级：满足以下条件则采样所有没被分析的表：
（1）查询中至少有一个未分析表；
（2）这个未分析表被关联另外一个表或者出现在子查询或非merge视图中；
（3）这个未分析表有索引；
（4）这个未分析表有多余动态采样默认的数据块数（默认是32块）。

3）Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
2级：对所有未分析表进行动态采样。采样数据块数量是默认数量的2倍。

4）Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
3级：在2级基础上加上那些使用了猜想选择消除表，采样数据块数量等于默认数量。对于未分析表，采样数量2倍于默认数量。

5）Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
4级：在3级基础上加上那些有单表谓词关联2个或多个列，采样数据块数量等于默认数量。对于未分析表，采样数量2倍于默认数量。

6）Levels 5

关于动态抽样（DynamicSampling）(四)