Oracle执行计划之动态采样

/*

1. 11g默认启动了统计信息收集的任务，默认运行时间是周一到周五晚上10点和周6，周天的早上6点
2. 你也可以关闭自动统计新收集任务，选择手工收集的方式，但是一般不建议这样操作。

  结论：
  1. 统计信息默认情况下是每天晚上10点半后收集，如果新建对象还没来得级收集统计信息，就采用动态采样的方式。
  2. 具体在set autotrace 跟踪的执行计划中，可以看到类似：- dynamic sampling used for this statement (level=2)
  3. 除非你用类似/*+dynamic_sampling(t 0) */的HINT关闭这个动态采样。
  4. 在收集过统计信息后，Oracle就不会采用动态采样。
  注：建索引过程中，默认会收集索引相关的统计信息。
*/

set autotrace off
set linesize 1000
drop table t_sample purge;
create table t_sample as select * from dba_objects;
create index idx_t_sample_objid on t_sample(object_id);
 select num_rows, blocks, last_analyzed
  from user_tables
 where table_name = 'T_SAMPLE';
 
 NUM_ROWS   BLOCKS   LAST_ANALYZED
----------------------------------


--建索引后，自动收集统计信息。
select index_name, 
       num_rows, 
       leaf_blocks, 
       distinct_keys, 
       last_analyzed
  from user_indexes
 where table_name = 'T_SAMPLE';
 
INDEX_NAME                       NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
------------------------------ ---------- ----------- ------------- --------------
IDX_T_SAMPLE_OBJID                  73159         162         73159 11-1月 -14 
 
set autotrace traceonly
set linesize 1000


select  * from t_sample where object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 1453182238
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_SAMPLE           |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_SAMPLE_OBJID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=20)
Note
-----
   - dynamic sampling used for this statement (level=2) --这是在动态采样，因为没有收集统计信息
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1393  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
--以下方法是要取消动态采样。          
select /*+dynamic_sampling(t 0) */ * from t_sample t where object_id=20;


执行计划
----------------------------------------------------------
Plan hash value: 1453182238
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |   872 |   176K|     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_SAMPLE           |   872 |   176K|     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_SAMPLE_OBJID |   349 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=20)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1393  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T_SAMPLE',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;  


set autotrace off 
 select num_rows, blocks, last_analyzed
    from user_tables
  where table_name = 'T_SAMPLE';


  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- --------------
     73630       1068 12-1月 -14


set autotrace traceonly     
select  * from t_sample where object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 1453182238


--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    97 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_SAMPLE           |     1 |    97 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_SAMPLE_OBJID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=20)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1393  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


--表统计信息收集后，是这样的。          
set autotrace off
 select num_rows, blocks, last_analyzed
  from user_tables
 where table_name = 'T';
 
  NUM_ROWS     BLOCKS LAST_ANALYZED
--------- ---------- --------------
    73118       1068 11-1月 -14