-- Check 1: High Degree of Parallelism-- Title: High Degree of Parallelism on Indexes-- Summary: Identifies indexes with a DEGREE setting higher than 1, which may affect resource usage and query performance.SELECT 'High Degree of Parallelism' AS "Check Type", INDEX_NAME, TABLE_NAME, DEGREEFROM USER_INDEXESWHERE DEGREE > 1ORDER BY DEGREE DESC;
-- Check 2: Potentially Unused Indexes-- Title: Potentially Unused Indexes-- Summary: Finds indexes that have not been accessed recently. These may be candidates for review or removal to reduce overhead.SELECT 'Potentially Unused Indexes' AS "Check Type", INDEX_NAME, TABLE_NAME, LAST_USEDFROM V$OBJECT_USAGEWHERE LAST_USED IS NULLAND OBJECT_TYPE = 'INDEX';
-- Check 3: Large Indexes Relative to Table Size-- Title: Large Indexes Relative to Table Size-- Summary: Flags indexes where size exceeds 50% of the associated table size. Large indexes can negatively impact performance and storage.SELECT a.SEGMENT_NAME AS INDEX_NAME, b.SEGMENT_NAME AS TABLE_NAME, ROUND((a.BYTES / b.BYTES) * 100, 2) AS INDEX_TO_TABLE_RATIO_PERCENTFROM USER_SEGMENTS aJOIN USER_SEGMENTS b ON a.SEGMENT_NAME = b.SEGMENT_NAMEWHERE a.SEGMENT_TYPE = 'INDEX' AND b.SEGMENT_TYPE = 'TABLE' AND ROUND((a.BYTES / b.BYTES) * 100, 2) > 50;
-- Check 4: Indexes with Stale Statistics-- Title: Indexes with Stale Statistics-- Summary: Lists indexes with statistics older than 30 days. Fresh statistics are essential for optimal query execution.SELECT 'Stale Statistics' AS "Check Type", INDEX_NAME, TABLE_NAME, NUM_ROWS, LAST_ANALYZEDFROM USER_INDEXESWHERE (LAST_ANALYZED IS NULL OR LAST_ANALYZED < SYSDATE - 30)ORDER BY LAST_ANALYZED;
-- Check 5: Indexes Not Aligned with Best Practices-- Title: Indexes Not Aligned with Best Practices-- Summary: Identifies indexes that are either unused, have a high degree of parallelism, or are invalid.SELECT 'Indexes Not Aligned with Best Practices' AS "Check Type", i.INDEX_NAME, i.TABLE_NAME, i.UNIQUENESS, i.STATUS, i.DEGREE, u.LAST_USEDFROM USER_INDEXES iLEFT JOIN V$OBJECT_USAGE u ON i.INDEX_NAME = u.OBJECT_NAMEWHERE (i.DEGREE > 1 OR u.LAST_USED IS NULL OR i.STATUS <> 'VALID')ORDER BY i.TABLE_NAME;
SELECT i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, i.TABLE_NAME, i.UNIQUENESS, i.STATUS, i.COMPRESSION, i.DEGREE, i.INI_TRANS, i.PCT_FREE, LISTAGG(c.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY c.COLUMN_POSITION) AS INDEXED_COLUMNSFROM ALL_INDEXES iJOIN ALL_IND_COLUMNS c ON i.INDEX_NAME = c.INDEX_NAME AND i.OWNER = c.INDEX_OWNERWHERE 1=1 AND i.OWNER <> 'CTXSYS' AND i.OWNER NOT IN ('DBSNMP') AND i.INDEX_NAME NOT LIKE 'SYS%'GROUP BY i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, i.TABLE_NAME, i.UNIQUENESS, i.STATUS, i.COMPRESSION, i.DEGREE, i.INI_TRANS, i.PCT_FREEORDER BY i.OWNER, i.INDEX_NAME;
--REBUILDING PARITIONED INDEXES
BEGIN FOR rec IN ( SELECT PARTITION_NAME FROM ALL_IND_PARTITIONS WHERE INDEX_NAME = 'IX_LX_FIELD_WORKFLOWS_PERFORMANCE_01' AND INDEX_OWNER = 'DGSTREAM' ) LOOP EXECUTE IMMEDIATE 'ALTER INDEX DGSTREAM.IX_LX_FIELD_WORKFLOWS_PERFORMANCE_01 REBUILD PARTITION ' || rec.PARTITION_NAME; END LOOP;END;