关于 DYNAMIC_SAMPLING 提示的疑惑
数据库版本 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
请帮忙看一下是否正常。
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
1 sample block 0.340547
2 sample block 0.692080
3 sample block 1.395144
4 sample block 2.801274
10 没有该信息
LevelWhen Dynamic Sampling will be usedSample size (blocks)
0Switches off dynamic samplingN/A
1At least one non-partitioned table in the statement has no statistics32
2 (default)One or more tables in the statement have no statistics64
3Any 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 =564
4Any statement that meets level 3 criteria and any statement that has complex predicates. An OR or AND operator between multiple predicates on the same table64
5Any statement that meets level 4 criteria128
6Any statement that meets level 4 criteria256
7Any statement that meets level 4 criteria512
8Any statement that meets level 4 criteria1024
9Any statement that meets level 4 criteria4086
10All statementsAll Blocks
如上 采用不同的 DYNAMIC_SAMPLING粒度 实际是有区别的，但是set autotrace 在欺骗你， 研究ORACLE不要过于依靠set autotrace 不好意思，提问的时候忘记粘贴显示执行计划的代码了，我是使用 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 显示的级别，不知道这个是否正常。
typ281 发表于 2014-4-10 02:37 static/image/common/back.gif
不好意思，提问的时候忘记粘贴显示执行计划的代码了，我是使用 select * from table(dbms_xplan.display_cu ...
请仔细看3、4楼，我想我已经说明该问题了
页:
[1]