1.当前用户下,哪些索引的聚合因子特别大
select a.table_name,
a.index_name, a.blevel, a.leaf_blocks,
b.num_rows, b.blocks, a.clustering_factor,
trunc(a.clustering_factor / b.num_rows,2) cluster_rate
from user_indexes a, user_tables b
where a.table_name = b.table_name
and a.clustering_factor is not null
and a.clustering_factor / b.num_rows>0.9
order by cluster_rate desc ;
注意:有时候由于统计信息问题,b.num_rows可能为0,那么请自行改变查询条件
2.当前用户下,哪些表的组合索引与单列索引存在交叉的情况
select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate
from user_ind_columns group by table_name
having count(distinct(column_name)) / count(*) < 1 order by cross_idx_rate desc;
3.当前用户下,哪些索引的高度比较高,大于5层(LEVEL=4) 从0开始
select table_name, index_name, blevel,leaf_blocks,
num_rows,last_analyzed,degree,status
from user_indexes where blevel>=4;
4.当前用户下,哪些索引最近30天内从未被使用过
select * from v$object_usage
where USED = 'NO' and START_MONITORING <= sysdate - 30
and END_MONITORING is not null;
开启相关监视语法
alter index idx_t_id monitoring usage; --开始监控
alter index idx_t_id nomonitoring usage; -- 取消监控
5.当前用户下,哪些索引被设置了并行
select table_name,index_name, blevel,
leaf_blocks,num_rows,last_analyzed,
degree, status
from user_indexes where degree>1;
6.普通索引(从未收集过统计信息或者是最近10天内未收集过统计信息的表)
select index_name, table_name, last_analyzed, num_rows, temporary, status
from user_indexes where status <> 'N/A'
and (last_analyzed is null or last_analyzed < sysdate - 10);
7.分区索引(从未收集过统计信息或者是最近10天内未收集过统计信息的分区)
select t2.table_name, t1.INDEX_NAME, t1.PARTITION_NAME,
t1.last_analyzed,t1.blevel,t1.leaf_blocks,t1.STATUS
from user_ind_partitions t1, user_indexes t2
where t1.index_name = t2.index_name
and (t1.last_analyzed is null or t1.last_analyzed < sysdate - 10);