index_maintenance.sql
-- ------------------------------------------------------------------------------------- File Name : index_maintenance.sql-- Author : Pierre Montbleau-- Description : Comprehensive Oracle database index analysis and maintenance utility-- Purpose : Analyze index usage, identify optimization opportunities, and perform maintenance-- Call Syntax : @F:\DBA\Scripts\index_maintenance.sql (action) (schema_name) (index_type)-- Parameters : action - Maintenance action (ANALYZE, REBUILD, COALESCE, UNUSED, STATS)-- schema_name - Specific schema or '%' for all schemas-- index_type - Index type filter (ALL, NORMAL, BITMAP, UNIQUE, FUNCTION_BASED)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1DEFINE v_index_type = '&3';
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_index_maintenance_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Index Maintenance Script...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Schema: &2PROMPT Index Type: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_schema_name VARCHAR2(30) := UPPER('&2'); v_index_type VARCHAR2(20) := UPPER('&3'); v_schema_exists NUMBER;BEGIN IF v_action NOT IN ('ANALYZE', 'REBUILD', 'COALESCE', 'UNUSED', 'STATS') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be ANALYZE, REBUILD, COALESCE, UNUSED, or STATS'); END IF; IF v_schema_name != '%' THEN SELECT COUNT(*) INTO v_schema_exists FROM dba_users WHERE username = v_schema_name; IF v_schema_exists = 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Schema ' || v_schema_name || ' does not exist'); END IF; END IF; IF v_index_type NOT IN ('ALL', 'NORMAL', 'BITMAP', 'UNIQUE', 'FUNCTION_BASED') THEN RAISE_APPLICATION_ERROR(-20003, 'Index type must be ALL, NORMAL, BITMAP, UNIQUE, or FUNCTION_BASED'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- INDEX USAGE STATISTICS: Index usage and efficiency analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. INDEX USAGE AND EFFICIENCY ANALYSISPROMPT =======================================
COLUMN owner FORMAT A15COLUMN index_name FORMAT A40COLUMN table_name FORMAT A30COLUMN uniqueness FORMAT A10COLUMN distinctiveness FORMAT 999.99COLUMN blevel FORMAT 999COLUMN leaf_blocks FORMAT 999,999COLUMN clustering_factor FORMAT 999,999,999
SELECT i.owner, i.index_name, i.table_name, i.uniqueness, ROUND((i.distinct_keys / NULLIF(t.num_rows, 0)) * 100, 2) as distinctiveness, i.blevel, i.leaf_blocks, i.clustering_factorFROM dba_indexes iJOIN dba_tables t ON i.owner = t.owner AND i.table_name = t.table_nameWHERE i.owner LIKE 'ATTSTATS' AND ('&v_index_type' = 'ALL' OR ('&v_index_type' = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR ('&v_index_type' = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR ('&v_index_type' = 'BITMAP' AND i.index_type = 'BITMAP') OR ('&v_index_type' = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')) AND i.num_rows > 0ORDER BY i.leaf_blocks DESC NULLS LAST;
-- ------------------------------------------------------------------------------------- INDEX SIZE ANALYSIS: Largest indexes and storage consumption-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. LARGEST INDEXES BY SIZEPROMPT ===========================
COLUMN owner FORMAT A15COLUMN index_name FORMAT A40COLUMN table_name FORMAT A30COLUMN tablespace_name FORMAT A20COLUMN size_mb FORMAT 999,999,999COLUMN pct_of_total FORMAT 999.99
SELECT s.owner, s.segment_name as index_name, i.table_name, s.tablespace_name, ROUND(s.bytes / 1024 / 1024, 2) as size_mb, ROUND((s.bytes / SUM(s.bytes) OVER ()) * 100, 2) as pct_of_totalFROM dba_segments sJOIN dba_indexes i ON s.owner = i.owner AND s.segment_name = i.index_nameWHERE s.segment_type = 'INDEX' AND s.owner LIKE 'ATTSTATS' AND ('&v_index_type' = 'ALL' OR ('&v_index_type' = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR ('&v_index_type' = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR ('&v_index_type' = 'BITMAP' AND i.index_type = 'BITMAP') OR ('&v_index_type' = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%'))ORDER BY s.bytes DESC;
-- ------------------------------------------------------------------------------------- UNUSED INDEXES: Identify potentially unused indexes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. POTENTIALLY UNUSED INDEXESPROMPT ==============================
COLUMN owner FORMAT A15COLUMN index_name FORMAT A40COLUMN table_name FORMAT A30COLUMN uniqueness FORMAT A10COLUMN size_mb FORMAT 999,999COLUMN last_analyzed FORMAT A12
SELECT i.owner, i.index_name, i.table_name, i.uniqueness, ROUND(s.bytes / 1024 / 1024, 2) as size_mb, TO_CHAR(i.last_analyzed, 'YYYY-MM-DD') as last_analyzedFROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner LIKE '&2' AND NOT EXISTS ( SELECT 1 FROM v$object_usage ou WHERE ou.index_name = i.index_name AND ou.table_name = i.table_name AND ou.used = 'YES' ) AND i.index_type != 'LOB' AND ('&v_index_type' = 'ALL' OR ('&v_index_type' = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR ('&v_index_type' = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR ('&v_index_type' = 'BITMAP' AND i.index_type = 'BITMAP') OR ('&v_index_type' = 'FUNCTION_BASED' AND i.index_type = 'FUNCTION-BASED'))ORDER BY s.bytes DESC;-- FETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- INDEX FRAGMENTATION: Analyze index fragmentation levels-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. INDEX FRAGMENTATION ANALYSISPROMPT ================================
COLUMN owner FORMAT A15COLUMN index_name FORMAT A40COLUMN table_name FORMAT A30COLUMN blevel FORMAT 999COLUMN leaf_blocks FORMAT 999,999COLUMN del_lf_rows FORMAT 999,999,999COLUMN pct_deleted FORMAT 999.99COLUMN fragmentation_level FORMAT A20
SELECT owner, index_name, table_name, blevel, leaf_blocks, num_rows, ROUND((distinct_keys / NULLIF(num_rows, 0)) * 100, 2) as selectivity_pct, clustering_factor, CASE WHEN blevel > 3 THEN 'HIGH' WHEN blevel > 2 THEN 'MEDIUM' WHEN blevel > 1 THEN 'LOW' ELSE 'GOOD' END as depth_levelFROM dba_indexesWHERE owner LIKE 'ATTSTATS' AND num_rows > 1000 AND ('ALL' = 'ALL' OR ('ALL' = 'NORMAL' AND uniqueness = 'NONUNIQUE') OR ('ALL' = 'UNIQUE' AND uniqueness = 'UNIQUE') OR ('ALL' = 'BITMAP' AND index_type = 'BITMAP') OR ('ALL' = 'FUNCTION_BASED' AND index_type LIKE 'FUNCTION-BASED%'))ORDER BY blevel DESC NULLS LAST;
-- ------------------------------------------------------------------------------------- DUPLICATE INDEXES: Identify potentially duplicate indexes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. POTENTIALLY DUPLICATE INDEXESPROMPT =================================
COLUMN owner FORMAT A15COLUMN table_name FORMAT A30COLUMN index_count FORMAT 999COLUMN column_list FORMAT A50COLUMN total_size_mb FORMAT 999,999
SELECT i.owner, i.table_name, COUNT(*) as index_count, LISTAGG(i.index_name, ', ') WITHIN GROUP (ORDER BY i.index_name) as index_names, ROUND(SUM(s.bytes) / 1024 / 1024, 2) as total_size_mbFROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner LIKE '&2' AND i.index_type NOT IN ('LOB', 'DOMAIN')GROUP BY i.owner, i.table_nameHAVING COUNT(*) > 3ORDER BY 5 DESC;-- FETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MAINTENANCE OPERATIONS: Perform requested index maintenance-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. INDEX MAINTENANCE OPERATIONSPROMPT ================================
DECLARE v_action VARCHAR2(20) := UPPER('STATS'); v_schema_name VARCHAR2(30) := UPPER('ATTSTATS'); v_index_type VARCHAR2(20) := UPPER('ALL'); v_processed_count NUMBER := 0;
-- Define record type for cursor TYPE idx_rec_type IS RECORD ( owner VARCHAR2(30), index_name VARCHAR2(30), table_name VARCHAR2(30), bytes NUMBER ); -- Cursor for REBUILD and COALESCE actions CURSOR c_indexes IS SELECT i.owner, i.index_name, i.table_name, s.bytes FROM dba_indexes i JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name WHERE i.owner = v_schema_name AND s.segment_type = 'INDEX' AND (v_index_type = 'ALL' OR (v_index_type = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR (v_index_type = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR (v_index_type = 'BITMAP' AND i.index_type = 'BITMAP') OR (v_index_type = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')) AND i.index_type NOT IN ('LOB', 'DOMAIN') AND i.num_rows > 0; -- Variables to hold cursor data idx_rec c_indexes%ROWTYPE; BEGIN -- Enable DBMS_OUTPUT DBMS_OUTPUT.ENABLE(NULL); IF v_action = 'REBUILD' THEN DBMS_OUTPUT.PUT_LINE('Starting index rebuild operations...'); OPEN c_indexes; LOOP FETCH c_indexes INTO idx_rec; EXIT WHEN c_indexes%NOTFOUND; BEGIN EXECUTE IMMEDIATE 'ALTER INDEX "' || idx_rec.owner || '"."' || idx_rec.index_name || '" REBUILD'; v_processed_count := v_processed_count + 1; DBMS_OUTPUT.PUT_LINE('Rebuilt: ' || idx_rec.owner || '.' || idx_rec.index_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error rebuilding ' || idx_rec.owner || '.' || idx_rec.index_name || ': ' || SQLERRM); END; END LOOP; CLOSE c_indexes; DBMS_OUTPUT.PUT_LINE('Index rebuild completed. Processed: ' || v_processed_count || ' indexes');
ELSIF v_action = 'COALESCE' THEN DBMS_OUTPUT.PUT_LINE('Starting index coalesce operations...'); OPEN c_indexes; LOOP FETCH c_indexes INTO idx_rec; EXIT WHEN c_indexes%NOTFOUND; BEGIN EXECUTE IMMEDIATE 'ALTER INDEX "' || idx_rec.owner || '"."' || idx_rec.index_name || '" COALESCE'; v_processed_count := v_processed_count + 1; DBMS_OUTPUT.PUT_LINE('Coalesced: ' || idx_rec.owner || '.' || idx_rec.index_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error coalescing ' || idx_rec.owner || '.' || idx_rec.index_name || ': ' || SQLERRM); END; END LOOP; CLOSE c_indexes; DBMS_OUTPUT.PUT_LINE('Index coalesce completed. Processed: ' || v_processed_count || ' indexes');
ELSIF v_action = 'UNUSED' THEN DBMS_OUTPUT.PUT_LINE('Identifying unused indexes for potential removal...'); FOR unused_rec IN ( SELECT i.owner, i.index_name, i.table_name, s.bytes FROM dba_indexes i JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name WHERE i.owner = v_schema_name AND s.segment_type = 'INDEX' AND NOT EXISTS ( SELECT 1 FROM v$object_usage ou WHERE ou.index_name = i.index_name AND ou.table_name = i.table_name AND ou.used = 'YES' ) AND s.bytes > 1024 * 1024 -- Larger than 1MB AND (v_index_type = 'ALL' OR (v_index_type = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR (v_index_type = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR (v_index_type = 'BITMAP' AND i.index_type = 'BITMAP') OR (v_index_type = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')) ) LOOP DBMS_OUTPUT.PUT_LINE('Unused candidate: ' || unused_rec.owner || '.' || unused_rec.index_name || ' on ' || unused_rec.table_name || ' (' || ROUND(unused_rec.bytes / 1024 / 1024, 2) || ' MB)'); v_processed_count := v_processed_count + 1; END LOOP; DBMS_OUTPUT.PUT_LINE('Unused index analysis completed. Found: ' || v_processed_count || ' candidates');
ELSIF v_action = 'STATS' THEN DBMS_OUTPUT.PUT_LINE('Generating index statistics report...'); DBMS_OUTPUT.PUT_LINE('============================================'); -- Total index count SELECT COUNT(*) INTO v_processed_count FROM dba_indexes i WHERE i.owner = v_schema_name AND (v_index_type = 'ALL' OR (v_index_type = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR (v_index_type = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR (v_index_type = 'BITMAP' AND i.index_type = 'BITMAP') OR (v_index_type = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')); DBMS_OUTPUT.PUT_LINE('Total indexes: ' || v_processed_count); DBMS_OUTPUT.PUT_LINE(''); -- Index types breakdown FOR type_rec IN ( SELECT i.index_type, i.uniqueness, COUNT(*) as cnt FROM dba_indexes i WHERE i.owner = v_schema_name GROUP BY i.index_type, i.uniqueness ORDER BY i.index_type, i.uniqueness ) LOOP DBMS_OUTPUT.PUT_LINE(type_rec.index_type || ' (' || type_rec.uniqueness || '): ' || type_rec.cnt); END LOOP; DBMS_OUTPUT.PUT_LINE(''); -- Size summary FOR size_rec IN ( SELECT ROUND(SUM(s.bytes) / 1024 / 1024, 2) as total_mb, COUNT(*) as index_count, ROUND(AVG(s.bytes) / 1024 / 1024, 2) as avg_mb, ROUND(MAX(s.bytes) / 1024 / 1024, 2) as max_mb FROM dba_indexes i JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name WHERE i.owner = v_schema_name AND s.segment_type = 'INDEX' AND (v_index_type = 'ALL' OR (v_index_type = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR (v_index_type = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR (v_index_type = 'BITMAP' AND i.index_type = 'BITMAP') OR (v_index_type = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')) ) LOOP DBMS_OUTPUT.PUT_LINE('Size Summary:'); DBMS_OUTPUT.PUT_LINE(' Total size: ' || size_rec.total_mb || ' MB'); DBMS_OUTPUT.PUT_LINE(' Average index size: ' || size_rec.avg_mb || ' MB'); DBMS_OUTPUT.PUT_LINE(' Largest index: ' || size_rec.max_mb || ' MB'); DBMS_OUTPUT.PUT_LINE(' Index count: ' || size_rec.index_count); END LOOP;
ELSE DBMS_OUTPUT.PUT_LINE('No maintenance operations performed (action: ' || v_action || ')'); DBMS_OUTPUT.PUT_LINE('Valid actions: REBUILD, COALESCE, UNUSED, STATS'); END IF;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);END;/
-- ------------------------------------------------------------------------------------- INDEX STATISTICS: Collection and analysis recommendations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. INDEX STATISTICS STATUSPROMPT ===========================
COLUMN owner FORMAT A25COLUMN index_name FORMAT A40COLUMN table_name FORMAT A40COLUMN last_analyzed FORMAT A20COLUMN stale_stats FORMAT A5COLUMN sample_size FORMAT 999,999,999
BEGIN IF UPPER('&1') IN ('ANALYZE', 'STATS') THEN DBMS_OUTPUT.PUT_LINE('Checking index statistics status...'); END IF;END;/
SELECT owner, index_name, table_name, TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as last_analyzed, stale_stats, sample_sizeFROM dba_ind_statisticsWHERE owner LIKE '&2' AND last_analyzed IS NULL OR last_analyzed < SYSDATE - 30 OR stale_stats = 'YES'ORDER BY 4 NULLS FIRST;-- FETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- OPTIMIZATION RECOMMENDATIONS: Actionable index improvement suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. INDEX OPTIMIZATION RECOMMENDATIONSPROMPT ======================================
COLUMN recommendation_type FORMAT A25COLUMN object_name FORMAT A50COLUMN description FORMAT A60COLUMN potential_savings_mb FORMAT 999,999COLUMN priority FORMAT A8
SELECT 'Rebuild Fragmented Index' as recommendation_type, i.owner || '.' || i.index_name as object_name, 'High BLEVEL: ' || i.blevel || ' (consider rebuild)' as description, ROUND(s.bytes / 1024 / 1024, 2) as potential_savings_mb, CASE WHEN i.blevel >= 4 THEN 'HIGH' WHEN i.blevel = 3 THEN 'MEDIUM' ELSE 'LOW' END as priorityFROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner = 'ATTSTATS' AND s.segment_type = 'INDEX' AND i.blevel >= 3 -- High BLEVEL indicates fragmentation
UNION ALL
SELECT 'Investigate Unused Index', i.owner || '.' || i.index_name, 'Large unused index consuming space', ROUND(s.bytes / 1024 / 1024, 2), 'MEDIUM'FROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner = 'ATTSTATS' AND s.segment_type = 'INDEX' AND NOT EXISTS ( SELECT 1 FROM v$object_usage ou WHERE ou.index_name = i.index_name AND ou.table_name = i.table_name AND ou.used = 'YES' ) AND s.bytes > 50 * 1024 * 1024 -- Larger than 50MB
UNION ALL
SELECT 'Update Statistics', owner || '.' || index_name, CASE WHEN last_analyzed IS NULL THEN 'Statistics never analyzed' ELSE 'Statistics are stale' END, 0, 'MEDIUM'FROM dba_ind_statisticsWHERE owner = 'ATTSTATS' AND (last_analyzed IS NULL OR stale_stats = 'YES')
UNION ALL
SELECT 'Poor Clustering Index', i.owner || '.' || i.index_name, 'Poor clustering factor: ' || i.clustering_factor || ' (rows: ' || i.num_rows || ', ratio: ' || ROUND(i.clustering_factor / NULLIF(i.num_rows, 0) * 100, 1) || '%)', 0, 'LOW'FROM dba_indexes iWHERE i.owner = 'ATTSTATS' AND i.num_rows > 1000 AND i.clustering_factor / NULLIF(i.num_rows, 0) > 10 -- Poor clustering
UNION ALL
SELECT 'Low Selectivity Index', i.owner || '.' || i.index_name, 'Low selectivity: ' || ROUND((i.distinct_keys / NULLIF(i.num_rows, 0)) * 100, 2) || '%', ROUND(s.bytes / 1024 / 1024, 2), 'MEDIUM'FROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner = 'ATTSTATS' AND s.segment_type = 'INDEX' AND i.num_rows > 10000 AND ROUND((i.distinct_keys / NULLIF(i.num_rows, 0)) * 100, 2) < 5 -- Less than 5% distinct
ORDER BY 4 DESC;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT INDEX MAINTENANCE COMPLETEDPROMPT =====================================PROMPT Action: &1PROMPT Schema: &2PROMPT Index Type: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendations abovePROMPT 2. Schedule regular index maintenancePROMPT 3. Monitor index usage patternsPROMPT 4. Consider implementing index monitoringPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1DEFINE v_index_type = '&3';
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_index_maintenance_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Index Maintenance Script...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Schema: &2PROMPT Index Type: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_schema_name VARCHAR2(30) := UPPER('&2'); v_index_type VARCHAR2(20) := UPPER('&3'); v_schema_exists NUMBER;BEGIN IF v_action NOT IN ('ANALYZE', 'REBUILD', 'COALESCE', 'UNUSED', 'STATS') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be ANALYZE, REBUILD, COALESCE, UNUSED, or STATS'); END IF; IF v_schema_name != '%' THEN SELECT COUNT(*) INTO v_schema_exists FROM dba_users WHERE username = v_schema_name; IF v_schema_exists = 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Schema ' || v_schema_name || ' does not exist'); END IF; END IF; IF v_index_type NOT IN ('ALL', 'NORMAL', 'BITMAP', 'UNIQUE', 'FUNCTION_BASED') THEN RAISE_APPLICATION_ERROR(-20003, 'Index type must be ALL, NORMAL, BITMAP, UNIQUE, or FUNCTION_BASED'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- INDEX USAGE STATISTICS: Index usage and efficiency analysis-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. INDEX USAGE AND EFFICIENCY ANALYSISPROMPT =======================================
COLUMN owner FORMAT A15COLUMN index_name FORMAT A40COLUMN table_name FORMAT A30COLUMN uniqueness FORMAT A10COLUMN distinctiveness FORMAT 999.99COLUMN blevel FORMAT 999COLUMN leaf_blocks FORMAT 999,999COLUMN clustering_factor FORMAT 999,999,999
SELECT i.owner, i.index_name, i.table_name, i.uniqueness, ROUND((i.distinct_keys / NULLIF(t.num_rows, 0)) * 100, 2) as distinctiveness, i.blevel, i.leaf_blocks, i.clustering_factorFROM dba_indexes iJOIN dba_tables t ON i.owner = t.owner AND i.table_name = t.table_nameWHERE i.owner LIKE 'ATTSTATS' AND ('&v_index_type' = 'ALL' OR ('&v_index_type' = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR ('&v_index_type' = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR ('&v_index_type' = 'BITMAP' AND i.index_type = 'BITMAP') OR ('&v_index_type' = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')) AND i.num_rows > 0ORDER BY i.leaf_blocks DESC NULLS LAST;
-- ------------------------------------------------------------------------------------- INDEX SIZE ANALYSIS: Largest indexes and storage consumption-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. LARGEST INDEXES BY SIZEPROMPT ===========================
COLUMN owner FORMAT A15COLUMN index_name FORMAT A40COLUMN table_name FORMAT A30COLUMN tablespace_name FORMAT A20COLUMN size_mb FORMAT 999,999,999COLUMN pct_of_total FORMAT 999.99
SELECT s.owner, s.segment_name as index_name, i.table_name, s.tablespace_name, ROUND(s.bytes / 1024 / 1024, 2) as size_mb, ROUND((s.bytes / SUM(s.bytes) OVER ()) * 100, 2) as pct_of_totalFROM dba_segments sJOIN dba_indexes i ON s.owner = i.owner AND s.segment_name = i.index_nameWHERE s.segment_type = 'INDEX' AND s.owner LIKE 'ATTSTATS' AND ('&v_index_type' = 'ALL' OR ('&v_index_type' = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR ('&v_index_type' = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR ('&v_index_type' = 'BITMAP' AND i.index_type = 'BITMAP') OR ('&v_index_type' = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%'))ORDER BY s.bytes DESC;
-- ------------------------------------------------------------------------------------- UNUSED INDEXES: Identify potentially unused indexes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. POTENTIALLY UNUSED INDEXESPROMPT ==============================
COLUMN owner FORMAT A15COLUMN index_name FORMAT A40COLUMN table_name FORMAT A30COLUMN uniqueness FORMAT A10COLUMN size_mb FORMAT 999,999COLUMN last_analyzed FORMAT A12
SELECT i.owner, i.index_name, i.table_name, i.uniqueness, ROUND(s.bytes / 1024 / 1024, 2) as size_mb, TO_CHAR(i.last_analyzed, 'YYYY-MM-DD') as last_analyzedFROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner LIKE '&2' AND NOT EXISTS ( SELECT 1 FROM v$object_usage ou WHERE ou.index_name = i.index_name AND ou.table_name = i.table_name AND ou.used = 'YES' ) AND i.index_type != 'LOB' AND ('&v_index_type' = 'ALL' OR ('&v_index_type' = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR ('&v_index_type' = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR ('&v_index_type' = 'BITMAP' AND i.index_type = 'BITMAP') OR ('&v_index_type' = 'FUNCTION_BASED' AND i.index_type = 'FUNCTION-BASED'))ORDER BY s.bytes DESC;-- FETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- INDEX FRAGMENTATION: Analyze index fragmentation levels-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. INDEX FRAGMENTATION ANALYSISPROMPT ================================
COLUMN owner FORMAT A15COLUMN index_name FORMAT A40COLUMN table_name FORMAT A30COLUMN blevel FORMAT 999COLUMN leaf_blocks FORMAT 999,999COLUMN del_lf_rows FORMAT 999,999,999COLUMN pct_deleted FORMAT 999.99COLUMN fragmentation_level FORMAT A20
SELECT owner, index_name, table_name, blevel, leaf_blocks, num_rows, ROUND((distinct_keys / NULLIF(num_rows, 0)) * 100, 2) as selectivity_pct, clustering_factor, CASE WHEN blevel > 3 THEN 'HIGH' WHEN blevel > 2 THEN 'MEDIUM' WHEN blevel > 1 THEN 'LOW' ELSE 'GOOD' END as depth_levelFROM dba_indexesWHERE owner LIKE 'ATTSTATS' AND num_rows > 1000 AND ('ALL' = 'ALL' OR ('ALL' = 'NORMAL' AND uniqueness = 'NONUNIQUE') OR ('ALL' = 'UNIQUE' AND uniqueness = 'UNIQUE') OR ('ALL' = 'BITMAP' AND index_type = 'BITMAP') OR ('ALL' = 'FUNCTION_BASED' AND index_type LIKE 'FUNCTION-BASED%'))ORDER BY blevel DESC NULLS LAST;
-- ------------------------------------------------------------------------------------- DUPLICATE INDEXES: Identify potentially duplicate indexes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. POTENTIALLY DUPLICATE INDEXESPROMPT =================================
COLUMN owner FORMAT A15COLUMN table_name FORMAT A30COLUMN index_count FORMAT 999COLUMN column_list FORMAT A50COLUMN total_size_mb FORMAT 999,999
SELECT i.owner, i.table_name, COUNT(*) as index_count, LISTAGG(i.index_name, ', ') WITHIN GROUP (ORDER BY i.index_name) as index_names, ROUND(SUM(s.bytes) / 1024 / 1024, 2) as total_size_mbFROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner LIKE '&2' AND i.index_type NOT IN ('LOB', 'DOMAIN')GROUP BY i.owner, i.table_nameHAVING COUNT(*) > 3ORDER BY 5 DESC;-- FETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MAINTENANCE OPERATIONS: Perform requested index maintenance-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. INDEX MAINTENANCE OPERATIONSPROMPT ================================
DECLARE v_action VARCHAR2(20) := UPPER('STATS'); v_schema_name VARCHAR2(30) := UPPER('ATTSTATS'); v_index_type VARCHAR2(20) := UPPER('ALL'); v_processed_count NUMBER := 0;
-- Define record type for cursor TYPE idx_rec_type IS RECORD ( owner VARCHAR2(30), index_name VARCHAR2(30), table_name VARCHAR2(30), bytes NUMBER ); -- Cursor for REBUILD and COALESCE actions CURSOR c_indexes IS SELECT i.owner, i.index_name, i.table_name, s.bytes FROM dba_indexes i JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name WHERE i.owner = v_schema_name AND s.segment_type = 'INDEX' AND (v_index_type = 'ALL' OR (v_index_type = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR (v_index_type = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR (v_index_type = 'BITMAP' AND i.index_type = 'BITMAP') OR (v_index_type = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')) AND i.index_type NOT IN ('LOB', 'DOMAIN') AND i.num_rows > 0; -- Variables to hold cursor data idx_rec c_indexes%ROWTYPE; BEGIN -- Enable DBMS_OUTPUT DBMS_OUTPUT.ENABLE(NULL); IF v_action = 'REBUILD' THEN DBMS_OUTPUT.PUT_LINE('Starting index rebuild operations...'); OPEN c_indexes; LOOP FETCH c_indexes INTO idx_rec; EXIT WHEN c_indexes%NOTFOUND; BEGIN EXECUTE IMMEDIATE 'ALTER INDEX "' || idx_rec.owner || '"."' || idx_rec.index_name || '" REBUILD'; v_processed_count := v_processed_count + 1; DBMS_OUTPUT.PUT_LINE('Rebuilt: ' || idx_rec.owner || '.' || idx_rec.index_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error rebuilding ' || idx_rec.owner || '.' || idx_rec.index_name || ': ' || SQLERRM); END; END LOOP; CLOSE c_indexes; DBMS_OUTPUT.PUT_LINE('Index rebuild completed. Processed: ' || v_processed_count || ' indexes');
ELSIF v_action = 'COALESCE' THEN DBMS_OUTPUT.PUT_LINE('Starting index coalesce operations...'); OPEN c_indexes; LOOP FETCH c_indexes INTO idx_rec; EXIT WHEN c_indexes%NOTFOUND; BEGIN EXECUTE IMMEDIATE 'ALTER INDEX "' || idx_rec.owner || '"."' || idx_rec.index_name || '" COALESCE'; v_processed_count := v_processed_count + 1; DBMS_OUTPUT.PUT_LINE('Coalesced: ' || idx_rec.owner || '.' || idx_rec.index_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error coalescing ' || idx_rec.owner || '.' || idx_rec.index_name || ': ' || SQLERRM); END; END LOOP; CLOSE c_indexes; DBMS_OUTPUT.PUT_LINE('Index coalesce completed. Processed: ' || v_processed_count || ' indexes');
ELSIF v_action = 'UNUSED' THEN DBMS_OUTPUT.PUT_LINE('Identifying unused indexes for potential removal...'); FOR unused_rec IN ( SELECT i.owner, i.index_name, i.table_name, s.bytes FROM dba_indexes i JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name WHERE i.owner = v_schema_name AND s.segment_type = 'INDEX' AND NOT EXISTS ( SELECT 1 FROM v$object_usage ou WHERE ou.index_name = i.index_name AND ou.table_name = i.table_name AND ou.used = 'YES' ) AND s.bytes > 1024 * 1024 -- Larger than 1MB AND (v_index_type = 'ALL' OR (v_index_type = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR (v_index_type = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR (v_index_type = 'BITMAP' AND i.index_type = 'BITMAP') OR (v_index_type = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')) ) LOOP DBMS_OUTPUT.PUT_LINE('Unused candidate: ' || unused_rec.owner || '.' || unused_rec.index_name || ' on ' || unused_rec.table_name || ' (' || ROUND(unused_rec.bytes / 1024 / 1024, 2) || ' MB)'); v_processed_count := v_processed_count + 1; END LOOP; DBMS_OUTPUT.PUT_LINE('Unused index analysis completed. Found: ' || v_processed_count || ' candidates');
ELSIF v_action = 'STATS' THEN DBMS_OUTPUT.PUT_LINE('Generating index statistics report...'); DBMS_OUTPUT.PUT_LINE('============================================'); -- Total index count SELECT COUNT(*) INTO v_processed_count FROM dba_indexes i WHERE i.owner = v_schema_name AND (v_index_type = 'ALL' OR (v_index_type = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR (v_index_type = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR (v_index_type = 'BITMAP' AND i.index_type = 'BITMAP') OR (v_index_type = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')); DBMS_OUTPUT.PUT_LINE('Total indexes: ' || v_processed_count); DBMS_OUTPUT.PUT_LINE(''); -- Index types breakdown FOR type_rec IN ( SELECT i.index_type, i.uniqueness, COUNT(*) as cnt FROM dba_indexes i WHERE i.owner = v_schema_name GROUP BY i.index_type, i.uniqueness ORDER BY i.index_type, i.uniqueness ) LOOP DBMS_OUTPUT.PUT_LINE(type_rec.index_type || ' (' || type_rec.uniqueness || '): ' || type_rec.cnt); END LOOP; DBMS_OUTPUT.PUT_LINE(''); -- Size summary FOR size_rec IN ( SELECT ROUND(SUM(s.bytes) / 1024 / 1024, 2) as total_mb, COUNT(*) as index_count, ROUND(AVG(s.bytes) / 1024 / 1024, 2) as avg_mb, ROUND(MAX(s.bytes) / 1024 / 1024, 2) as max_mb FROM dba_indexes i JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name WHERE i.owner = v_schema_name AND s.segment_type = 'INDEX' AND (v_index_type = 'ALL' OR (v_index_type = 'NORMAL' AND i.uniqueness = 'NONUNIQUE') OR (v_index_type = 'UNIQUE' AND i.uniqueness = 'UNIQUE') OR (v_index_type = 'BITMAP' AND i.index_type = 'BITMAP') OR (v_index_type = 'FUNCTION_BASED' AND i.index_type LIKE 'FUNCTION-BASED%')) ) LOOP DBMS_OUTPUT.PUT_LINE('Size Summary:'); DBMS_OUTPUT.PUT_LINE(' Total size: ' || size_rec.total_mb || ' MB'); DBMS_OUTPUT.PUT_LINE(' Average index size: ' || size_rec.avg_mb || ' MB'); DBMS_OUTPUT.PUT_LINE(' Largest index: ' || size_rec.max_mb || ' MB'); DBMS_OUTPUT.PUT_LINE(' Index count: ' || size_rec.index_count); END LOOP;
ELSE DBMS_OUTPUT.PUT_LINE('No maintenance operations performed (action: ' || v_action || ')'); DBMS_OUTPUT.PUT_LINE('Valid actions: REBUILD, COALESCE, UNUSED, STATS'); END IF;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);END;/
-- ------------------------------------------------------------------------------------- INDEX STATISTICS: Collection and analysis recommendations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. INDEX STATISTICS STATUSPROMPT ===========================
COLUMN owner FORMAT A25COLUMN index_name FORMAT A40COLUMN table_name FORMAT A40COLUMN last_analyzed FORMAT A20COLUMN stale_stats FORMAT A5COLUMN sample_size FORMAT 999,999,999
BEGIN IF UPPER('&1') IN ('ANALYZE', 'STATS') THEN DBMS_OUTPUT.PUT_LINE('Checking index statistics status...'); END IF;END;/
SELECT owner, index_name, table_name, TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as last_analyzed, stale_stats, sample_sizeFROM dba_ind_statisticsWHERE owner LIKE '&2' AND last_analyzed IS NULL OR last_analyzed < SYSDATE - 30 OR stale_stats = 'YES'ORDER BY 4 NULLS FIRST;-- FETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- OPTIMIZATION RECOMMENDATIONS: Actionable index improvement suggestions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. INDEX OPTIMIZATION RECOMMENDATIONSPROMPT ======================================
COLUMN recommendation_type FORMAT A25COLUMN object_name FORMAT A50COLUMN description FORMAT A60COLUMN potential_savings_mb FORMAT 999,999COLUMN priority FORMAT A8
SELECT 'Rebuild Fragmented Index' as recommendation_type, i.owner || '.' || i.index_name as object_name, 'High BLEVEL: ' || i.blevel || ' (consider rebuild)' as description, ROUND(s.bytes / 1024 / 1024, 2) as potential_savings_mb, CASE WHEN i.blevel >= 4 THEN 'HIGH' WHEN i.blevel = 3 THEN 'MEDIUM' ELSE 'LOW' END as priorityFROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner = 'ATTSTATS' AND s.segment_type = 'INDEX' AND i.blevel >= 3 -- High BLEVEL indicates fragmentation
UNION ALL
SELECT 'Investigate Unused Index', i.owner || '.' || i.index_name, 'Large unused index consuming space', ROUND(s.bytes / 1024 / 1024, 2), 'MEDIUM'FROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner = 'ATTSTATS' AND s.segment_type = 'INDEX' AND NOT EXISTS ( SELECT 1 FROM v$object_usage ou WHERE ou.index_name = i.index_name AND ou.table_name = i.table_name AND ou.used = 'YES' ) AND s.bytes > 50 * 1024 * 1024 -- Larger than 50MB
UNION ALL
SELECT 'Update Statistics', owner || '.' || index_name, CASE WHEN last_analyzed IS NULL THEN 'Statistics never analyzed' ELSE 'Statistics are stale' END, 0, 'MEDIUM'FROM dba_ind_statisticsWHERE owner = 'ATTSTATS' AND (last_analyzed IS NULL OR stale_stats = 'YES')
UNION ALL
SELECT 'Poor Clustering Index', i.owner || '.' || i.index_name, 'Poor clustering factor: ' || i.clustering_factor || ' (rows: ' || i.num_rows || ', ratio: ' || ROUND(i.clustering_factor / NULLIF(i.num_rows, 0) * 100, 1) || '%)', 0, 'LOW'FROM dba_indexes iWHERE i.owner = 'ATTSTATS' AND i.num_rows > 1000 AND i.clustering_factor / NULLIF(i.num_rows, 0) > 10 -- Poor clustering
UNION ALL
SELECT 'Low Selectivity Index', i.owner || '.' || i.index_name, 'Low selectivity: ' || ROUND((i.distinct_keys / NULLIF(i.num_rows, 0)) * 100, 2) || '%', ROUND(s.bytes / 1024 / 1024, 2), 'MEDIUM'FROM dba_indexes iJOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_nameWHERE i.owner = 'ATTSTATS' AND s.segment_type = 'INDEX' AND i.num_rows > 10000 AND ROUND((i.distinct_keys / NULLIF(i.num_rows, 0)) * 100, 2) < 5 -- Less than 5% distinct
ORDER BY 4 DESC;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT INDEX MAINTENANCE COMPLETEDPROMPT =====================================PROMPT Action: &1PROMPT Schema: &2PROMPT Index Type: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Next Steps:PROMPT 1. Review HIGH priority recommendations abovePROMPT 2. Schedule regular index maintenancePROMPT 3. Monitor index usage patternsPROMPT 4. Consider implementing index monitoringPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------