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
检查哪些未被收集或者很久没收集(表、分区、子分区）
select table_name, blocks, num_rows, last_analyzed from user_tab_statistics t where (t.last_analyzed is null or t.last_analyzed < sysdate - 100) and table_name not like 'BIN$%' order by last_analyzed ; select table_name, blocks, num_rows, last_analyzed from user_tab_partitions t where (t.last_analyzed is null or t.last_analyzed < sysdate - 100) and table_name not like 'BIN$%' order by last_analyzed ; select table_name, blocks, num_rows, last_analyzed from user_tab_subpartitions t where (t.last_analyzed is null or t.last_analyzed < sysdate - 100) and table_name not like 'BIN$%' order by last_analyzed ;
检查哪些索引未被收集或者很久没收集
select t.table_name, t.index_name, t.blevel, t.leaf_blocks, t.num_rows, t.last_analyzed from user_ind_statistics t where (t.last_analyzed is null or t.last_analyzed < sysdate - 100) and table_name not like 'BIN$%' order by table_name,index_name;
临时表不要收集统计信息，会导致执行计划出错。
排查被收集统计信息的临时表
select table_name, t.last_analyzed, t.num_rows, t.blocks from user_tables t where t.temporary = 'Y' and last_analyzed is not null;
