Oracle之索引监控脚本大全

1.当前用户下,哪些表的索引个数字超过5个的 

select table_name, count(*) cnt
  from user_indexes
 group by table_name
having count(*) >= 5

order by cnt desc ;

2.当前用户下，哪些组合索引组合列超过4个的
select table_name, index_name, count(*)
  from user_ind_columns
 group by table_name, index_name
having count(*) >= 4
 order by count(*) desc;

3.--当前用户下，哪些索引的聚合因子特别大。数据越大说明聚合因子数量多
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 b.num_rows!=0--有待考究！（自己加的）
     and a.clustering_factor is not null
     and a.clustering_factor / b.num_rows>0.9
 order by cluster_rate desc  ;

4.当前用户下，哪些表的组合索引与单列索引存在交叉的情况。(个人感觉统计有问题有待考究）

重复率
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;

---------例子


drop table t purge;


drop table t1 purge;
create table t1 as select * from dba_objects where object_id is not null;
create index idx_t1_objid_owner on t1(object_id ,owner);
create index idx_t1_object_id   on t1(object_id );




drop table t2 purge;
create table t2 as select * from dba_objects where object_id is not null;
create index idx_t2_objid_owner on t2(object_id,owner);
create index idx_t2_object_id   on t2(object_id);
create index idx_t2_owner   on t2(owner);




drop table t3 purge;
create table t3 as select * from dba_objects where object_id is not null;
create index idx_t3_objid_owner on t3(object_id,owner);
create index idx_t3_owner_objid on t3(owner,object_id);
create index idx_t3_object_id   on t3(object_id);
create index idx_t3_owner       on t3(owner);




---执行如下语句，发现当前用户下，T3,T2,T1表存在索引单列组合有交叉，最严重的是T3
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 ;


TABLE_NAME                     CROSS_IDX_RATE
------------------------------ --------------
T3                                        .33
T2                                         .5
T1                                        .66

5.失效-普通索引 
select t.index_name,
       t.table_name,
       blevel,
       t.num_rows,
       t.leaf_blocks,
       t.distinct_keys
  from user_indexes t
where status = 'UNUSABLE' ;

6.失效-分区索引
select t1.blevel,
       t1.leaf_blocks,
       t1.INDEX_NAME,
       t2.table_name,
       t1.PARTITION_NAME,
       t1.STATUS
  from user_ind_partitions t1, user_indexes t2
where t1.index_name = t2.index_name
   and t1.STATUS = 'UNUSABLE';

7.哪些数据大表没有建索引

--针对普通表（大于2GB的表未建任何索引）


select segment_name, bytes/1024/1024/1024 "GB", blocks, tablespace_name
  from user_segments
 where segment_type = 'TABLE'
   and segment_name not in (select table_name from user_indexes)
   and bytes / 1024 / 1024 / 1024 >= 2
 order by GB desc;
   
   
--针对分区表（大于2GB的分区表未建任何索引）
--无论是建了局部索引还是全局索引，在user_indexes都可以查到，只是status不一样。
select segment_name, sum(bytes)/1024/1024/1024 "GB", sum(blocks)
  from user_segments 
 where segment_type = 'TABLE PARTITION'
   and segment_name not in (select table_name from user_indexes)
   group by segment_name
   having sum(bytes)/1024/1024/1024>=2
 order by GB desc;
   
--注：无论是建了局部索引还是全局索引，在user_indexes都可以查到，只是status不一样。

8.将外键未建索引的情况列出 
select table_name,
       constraint_name,
       cname1 || nvl2(cname2, ',' || cname2, null) ||
       nvl2(cname3, ',' || cname3, null) ||
       nvl2(cname4, ',' || cname4, null) ||
       nvl2(cname5, ',' || cname5, null) ||
       nvl2(cname6, ',' || cname6, null) ||
       nvl2(cname7, ',' || cname7, null) ||
       nvl2(cname8, ',' || cname8, null) columns
  from (select b.table_name,
               b.constraint_name,
               max(decode(position, 1, column_name, null)) cname1,
               max(decode(position, 2, column_name, null)) cname2,
               max(decode(position, 3, column_name, null)) cname3,
               max(decode(position, 4, column_name, null)) cname4,
               max(decode(position, 5, column_name, null)) cname5,
               max(decode(position, 6, column_name, null)) cname6,
               max(decode(position, 7, column_name, null)) cname7,
               max(decode(position, 8, column_name, null)) cname8,
               count(*) col_cnt
          from (select substr(table_name, 1, 30) table_name,
                       substr(constraint_name, 1, 30) constraint_name,
                       substr(column_name, 1, 30) column_name,
                       position
                  from user_cons_columns) a,
               user_constraints b
         where a.constraint_name = b.constraint_name
           and b.constraint_type = 'R'
         group by b.table_name, b.constraint_name) cons
 where col_cnt > ALL
 (select count(*)
          from user_ind_columns i
         where i.table_name = cons.table_name
           and i.column_name in (cname1, cname2, cname3, cname4, cname5,
                cname6, cname7, cname8)
           and i.column_position <= cons.col_cnt
         group by i.index_name);

9.当前用户下，哪些索引的高度比较高，大于5层（LEVEL=4)
select table_name,
       index_name,
       blevel,
       leaf_blocks,
       num_rows,
       last_analyzed,
       degree,
       status
  from user_indexes
  where  blevel>=4;

10.当前用户下，哪些外键的约束失效了。


SELECT TABLE_NAME,
       CONSTRAINT_NAME,
       STATUS,
       CONSTRAINT_TYPE,
       R_CONSTRAINT_NAME
  FROM USER_CONSTRAINTS
 WHERE STATUS='DISABLED';

11.当前用户下，哪些索引被设置了并行。


select table_name,
       index_name,
       blevel,
       leaf_blocks,
       num_rows,
       last_analyzed,
       degree,
       status
  from user_indexes
  where  degree>1;

12.--普通索引（从未收集过统计信息或者是最近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);




--分区索引（从未收集过统计信息或者是最近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);