set autotrace offset linesize 1000drop 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 traceonlyset linesize 1000select  * 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月 -14set 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 twhere t.temporary = ‘Y‘   and last_analyzed is not null;