监控索引常用的sql脚本

1、当前用户下,哪些表的索引个数字超过5个的 SELECT TABLE_NAME, COUNT(*) CNT  FROM USER_INDEXES GROUP BY TABLE_NAMEHAVING COUNT(*) >= 5 ORDER BY CNT DESC;2、从未收集过统计信息或者是最近30天内未收集过统计信息的表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 - 30);   3、哪些组合索引组合列超过4个的SELECT TABLE_NAME, INDEX_NAME, COUNT(*) CNT  FROM USER_IND_COLUMNS GROUP BY TABLE_NAME, INDEX_NAMEHAVING COUNT(*) >= 4 ORDER BY COUNT(*) DESC;4、大于10GB的表未建任何索引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 >= 10 ORDER BY GB DESC; 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、查找在最近30天内未被使用过的索引SELECT *  FROM V$OBJECT_USAGE WHERE USED = 'NO'   AND START_MONITORING <= SYSDATE - 30   AND END_MONITORING IS NOT NULL;   7、外键的约束失效了SELECT TABLE_NAME,       CONSTRAINT_NAME,       STATUS,       CONSTRAINT_TYPE,       R_CONSTRAINT_NAME  FROM USER_CONSTRAINTS WHERE STATUS='DISABLED';