oracle之查询统计信息

以下是炼金数据的课程文件总结：

1、prompt <p>检查统计信息是否被收集
--10g
select t.job_name,t.program_name,,t.state,t.enabled
  from dba_scheduler_jobs t
where job_name = 'GATHER_STATS_JOB';

--11g
select client_name,status from dba_autotask_client;

select window_next_time,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;

2、prompt <p>检查哪些未被收集或者很久没收集(表、分区、子分区）

//查询表
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 ;

3、查询那些列信息未被收集

prompt <p>检查哪些列很久没被收集统计信息

select t.table_name,
       t.column_name,
       t.num_distinct,
       t.low_value,
       t.high_value,
       last_analyzed
  from user_tab_col_statistics t
 where t.last_analyzed < sysdate - 100
   and table_name not like 'BIN$%'
 order by table_name,last_analyzed;
 

select t.table_name,
       t.column_name,
       t.num_distinct,
       t.low_value,
       t.high_value,
       last_analyzed
  from user_part_col_statistics t
 where  t.last_analyzed < sysdate - 100
   and table_name not like 'BIN$%'
 order by table_name,last_analyzed;
   
select t.table_name,
       t.column_name,
       t.num_distinct,
       t.low_value,
       t.high_value,
       last_analyzed
  from user_subpart_col_statistics t
 where t.last_analyzed < sysdate - 100
   and table_name not like 'BIN$%'
 order by table_name,last_analyzed;

3、prompt <p>检查哪些索引未被收集或者很久没收集

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;

4、prompt <p>被收集统计信息的临时表
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;