查询统计信息
select * from user_tab_statistics t
where t.TABLE_NAME=upper('tablename');
查询表基本信息
select * from user_tab_statistics
select table_name,num_rows,blocks,avg_row_len,sample_size,owner
from all_tables/* where owner='NEO'*/
where num_rows > 5000000
执行统计函数
exec dbms_stats.gather_table_stats(ownname=>'aneo_0905_3',tabname=>'chms_trade_cexecutions',cascade => true);
查询索引状态
select * from dba_indexes b
1） create table ind (id int,name varchar2(100));
2）
SQL> begin
2 for i in 1..10000 loop
3 insert into ind values(i,to_char(i)||'aaa');
4 end loop;
5 commit;
6 end;
7 /
3）create index ind_id_idx on ind(id);
4）analyze index ind_id_idx validate structure;
5）SELECT * from index_stats;
6）
SELECT name,del_lf_rows, lf_rows - del_lf_rows lf_rows_used,to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
FROM index_stats where name = upper('&&index_name');
analyze index ind_id_idx validate structure;
select (del_lf_rows/lf_rows)*100 from index_stats ;
alter index ind_id_idx coalesce;