关于动态抽样（DynamicSampling）

create table t
as
select owner, object_type
from all_objects
/




select count(*) from t;


COUNT(*)
------------------------
68076

set autotrace traceonly explain
SQL> select /*+ dynamic_sampling(t 0) */ * from t;


Execution Plan
------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16010 | 437K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 16010 | 437K| 55 (0)| 00:00:01 |
--------------------------------------------------------------------------

select * from t;


Execution Plan
------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77871 | 2129K| 56 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 77871 | 2129K| 56 (2)| 00:00:01 |
--------------------------------------------------------------------------


Note
------------------------------------------
- dynamic sampling used for this statement




code3: 被高估的基数
SQL> delete from t;
68076 rows deleted.


SQL> commit;
Commit complete.


SQL> set autotrace traceonly explain
SQL> select /*+ dynamic_sampling(t 0) */ * from t;


Execution Plan
------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16010 | 437K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 16010 | 437K| 55 (0)| 00:00:01 |
--------------------------------------------------------------------------


SQL> select * from t;


Execution Plan
-----------------------------
Plan hash value: 1601196873


------------------------------------------------------------------------【本文来自鸿网互联 (http://www.68idc.cn)】--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 1 | 28 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------


Note
---------------------------------------
- dynamic sampling used for this statement

SQL > create table t
as select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1,
decode( mod(rownum,2), 0, 'Y', 'N' ) flag2, a.*
from all_objects a
/
Table created.


SQL > create index t_idx on t(flag1,flag2);
Index created.


SQL > begin
dbms_stats.gather_table_stats
( user, 'T',
method_opt=>'for all indexed columns size 254' );
end;
/
PL/SQL procedure successfully completed.


SQL> select num_rows, num_rows/2,
num_rows/2/2 from user_tables
where table_name = 'T';


NUM_ROWS NUM_ROWS/2 NUM_ROWS/2/2
-------- ---------- ------------
68076 34038 17019


code5：验证一下上面的说法：
SQL> set autotrace traceonly explain
SQL> select * from t where flag1='N';


Execution Plan
------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33479 | 3432K| 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 33479 | 3432K| 292 (1)| 00:00:04 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG1"='N')


SQL> select * from t where flag2='N';


Execution Plan
----------------------------
Plan hash value: 1601196873


---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34597 | 3547K| 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 34597 | 3547K| 292 (1)| 00:00:04 |
---------------------------------------------------------------------------


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


1 - filter("FLAG2"='N')


--至此一切正常！so far, so good！


code5: here comes the problem
SQL> select * from t where flag1 = 'N' and flag2 = 'N';


Execution Plan
----------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17014 | 1744K| 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 17014 | 1744K| 292 (1)| 00:00:04 |
--------------------------------------------------------------------------


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


1 - filter("FLAG1" = 'N' AND "FLAG2" = 'N')


--验证了我们前面说的优化器此时异想天开了


code7: 动态采样听令，开始介入
SQL> select /*+ dynamic_sampling(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')

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