Oracle性能查询统计信息的SQL语句

1、查询表的统计信息
SELECT A.TABLE_NAME,
       A.NUM_ROWS,
       A.BLOCKS,
       A.EMPTY_BLOCKS,
       A.AVG_SPACE,
       A.CHAIN_CNT,
       A.AVG_ROW_LEN,
       A.GLOBAL_STATS,
       A.USER_STATS,
       A.SAMPLE_SIZE,
       TO_CHAR(A.LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS')
  FROM DBA_TABLES A
 WHERE OWNER = UPPER(NVL('&OWNER', USER))
   AND TABLE_NAME = UPPER('&TABLE_NAME');

2、查询列的统计信息
SELECT T.COLUMN_NAME,
       T.DATA_TYPE,
       T.NULLABLE,
       T.NUM_DISTINCT,
       T.density,
       T.NUM_BUCKETS,
       T.NUM_NULLS,
       T.GLOBAL_STATS,
       T.USER_STATS,
       T.sample_size,
       TO_CHAR(T.LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS')
  FROM DBA_TAB_COLS T
 WHERE TABLE_NAME = UPPER('&TABLE_NAME')
   AND OWNER = UPPER(NVL('&OWNER', USER));

3、查询索引的统计信息
SELECT T.INDEX_NAME,
       T.UNIQUENESS,
       T.BLEVEL,
       T.LEAF_BLOCKS,
       T.DISTINCT_KEYS,
       T.NUM_ROWS,
       T.AVG_LEAF_BLOCKS_PER_KEY,
       T.AVG_DATA_BLOCKS_PER_KEY,
       T.CLUSTERING_FACTOR,
       T.GLOBAL_STATS,
       T.USER_STATS,
       T.SAMPLE_SIZE,
       TO_CHAR(T.LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS')
  FROM DBA_INDEXES T
 WHERE TABLE_NAME = UPPER('&TABLE_NAME')
   AND OWNER = UPPER(NVL('&OWNER', USER));

 

 

4.查看收集统计信息的Job

    select log_date,job_name,status 
    from dba_scheduler_job_run_details 
    where job_name='GATHER_STATS_JOB' order by log_id;

    select * from dba_scheduler_programs 
    where program_name=upper('GATHER_STATS_PROG');

   Select dbms_stats.get_stats_history_availability from dual;