1. query table Statistics
select. table_name,
. num_rows,
. blocks,
. empty_blocks,
. avg_space,
. chain_cnt,
. avg_row_len,
. global_stats,
. user_stats,
. sample_size,
to_char (. 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. query column Statistics
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. query the index statistics
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. view the job that collects statistics 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;
|
