关于 DYNAMIC_SAMPLING 提示的疑惑

typ281

数据库版本 11.2.0.3，2个节点RAC
操作系统版本 Oracle Linux Server release 6.3 X64

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

--创建测试表
SQL> create table scott.test1 as select * from dba_users;

Table created.

--执行sql
SQL> select /*+ DYNAMIC_SAMPLING(t 1) */ count(*)
  2  from scott.test1 t
  3  where username='SYS';

  COUNT(*)
----------
         1


SQL_ID  6pv9dvaavmjf0, child number 0
-------------------------------------
select /*+ DYNAMIC_SAMPLING(t 1) */ count(*) from scott.test1 t where
username='SYS'

Plan hash value: 3896847026

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST1 |     1 |    17 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter("USERNAME"='SYS')

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

执行计划里面显示的是  - dynamic sampling used for this statement (level=2)，
而不是 提示里面指定的 /*+ DYNAMIC_SAMPLING(t 1) */  1，

--数据库初始化参数 optimizer_dynamic_sampling 值为2
SQL> show parameter dynamic

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

请帮忙看一下是否正常。

Liu Maclean(刘相兵

SQL> select count(*) from macdt;

  COUNT(*)
----------
    617992

       
select * from v$VerSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


alter session set events '10046 trace name context forever,level 12';

alter system flush buffer_cache;

select /*+ DYNAMIC_SAMPLING(t 1) */ count(*) from macdt t;


alter system flush buffer_cache;

select /*+ DYNAMIC_SAMPLING(t 2) */ count(*) from macdt t;


alter system flush buffer_cache;

select /*+ DYNAMIC_SAMPLING(t 3) */ count(*) from macdt t;



alter system flush buffer_cache;

select /*+ DYNAMIC_SAMPLING(t 4) */ count(*) from macdt t;


alter system flush buffer_cache;

select /*+ DYNAMIC_SAMPLING(t 10) */ count(*) from macdt t;
















DYNAMIC_SAMPLING(t 1)  时的动态采用语句

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 "SYS"."MACDT" SAMPLE BLOCK (0.340547 , 1) SEED (1) "T") SAMPLESUB


DYNAMIC_SAMPLING(t 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 /*+ NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2 FROM "SYS"."MACDT" SAMPLE BLOCK (0.692080 , 1) SEED (1) "T") SAMPLESUB

DYNAMIC_SAMPLING(t 3)  时的动态采用语句


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 "SYS"."MACDT" SAMPLE BLOCK (1.395144 , 1) SEED (1) "T") SAMPLESUB


DYNAMIC_SAMPLING(t 4)  时的动态采用语句


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 "SYS"."MACDT" SAMPLE BLOCK (2.801274 , 1) SEED (1) "T") SAMPLESUB


DYNAMIC_SAMPLING(t 10)  时的动态采用语句

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 "SYS"."MACDT" "T") SAMPLESUB

Liu Maclean(刘相兵

1 sample block 0.340547
2 sample block  0.692080
3 sample block 1.395144
4 sample block  2.801274
10 没有该信息

Level	When Dynamic Sampling will be used	Sample size (blocks)
0	Switches off dynamic sampling	N/A
1	At least one non-partitioned table in the statement has no statistics	32
2 (default)	One or more tables in the statement have no statistics	64
3	Any statement that meets level 2 criteria and any statement that has one or more expressions used in the where clause predicates e.g. Where substr(CUSTLASTNAME,1,3) or Where a + b =5	64
4	Any statement that meets level 3 criteria and any statement that has complex predicates. An OR or AND operator between multiple predicates on the same table	64
5	Any statement that meets level 4 criteria	128
6	Any statement that meets level 4 criteria	256
7	Any statement that meets level 4 criteria	512
8	Any statement that meets level 4 criteria	1024
9	Any statement that meets level 4 criteria	4086
10	All statements	All Blocks

Liu Maclean(刘相兵

如上 采用不同的 DYNAMIC_SAMPLING粒度 实际是有区别的，但是set autotrace 在欺骗你， 研究ORACLE不要过于依靠set autotrace

typ281

不好意思，提问的时候忘记粘贴显示执行计划的代码了，我是使用 select * from table(dbms_xplan.display_cursor()); 来查看sql语句的执行计划，之前有看到过使用 dbms_xplan 显示的执行计划中出现 dynamic sampling used for this statement (level=2) 表明sql语句执行过程中使用了 动态采样，一直以为 (level=2 )就是sql语句执行的时候使用的 动态采样的级别，但是今天测试发现 在sql语句中使用 /*+ DYNAMIC_SAMPLING(t 1) */  提示指定动态采样的级别，并不会改变 dbms_xplan 的dynamic sampling used for this statement (level=2)  这个level 显示的级别，不知道这个是否正常。

Liu Maclean(刘相兵

typ281 发表于 2014-4-10 02:37
不好意思，提问的时候忘记粘贴显示执行计划的代码了，我是使用 select * from table(dbms_xplan.display_cu ...

请仔细看3、4楼，我想我已经说明该问题了