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;