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.查看收集统计信息的Jobselect 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;