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

Liu Maclean(刘相兵

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

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