Script to Analyzes the Efficiency of Indexes in an Oracle Database:

advertisements

_____________________________________________________________________________________________________________________

This script performs the following steps: 

 1. Creates a temporary table (index_analysis_results) to store the index analysis results. 

2. Loops through all non-system indexes (NORMAL index type) in the database (excluding system schemas and recycle bin objects). 

3. Calculates the index inefficiency by retrieving the unused space information using DBMS_SPACE.UNUSED_SPACE procedure. 

4. Inserts the index analysis results into the temporary table. 

5. Displays the index analysis results, ordered by inefficiency in descending order. 

6. Drops the temporary table.

advertisements

 

-- Script to analyze index efficiency in Oracle Database

-- Enable SQL*Plus settings
SET PAGESIZE 100
SET LINESIZE 200
SET VERIFY OFF

-- Create a temporary table to store index analysis results
CREATE GLOBAL TEMPORARY TABLE index_analysis_results (
  owner        VARCHAR2(30),
  table_name   VARCHAR2(30),
  index_name   VARCHAR2(30),
  inefficiency NUMBER
) ON COMMIT PRESERVE ROWS;

-- Analyze index efficiency
BEGIN
  FOR index_stats IN (
    SELECT owner, table_name, index_name
    FROM all_indexes
    WHERE table_owner NOT IN ('SYS', 'SYSTEM')
      AND table_name NOT LIKE 'BIN$%'
      AND index_type LIKE 'NORMAL'
  ) LOOP
    -- Calculate index inefficiency
    EXECUTE IMMEDIATE '
      DECLARE
        l_total_blocks NUMBER;
        l_used_blocks  NUMBER;
        l_inefficiency NUMBER;
      BEGIN
        DBMS_SPACE.UNUSED_SPACE (
          segment_owner   => :owner,
          segment_name    => :index_name,
          segment_type    => ''INDEX'',
          total_blocks    => l_total_blocks,
          unused_blocks   => l_used_blocks
        );
        l_inefficiency := (l_used_blocks / l_total_blocks) * 100;
        INSERT INTO index_analysis_results (
          owner, table_name, index_name, inefficiency
        )
        VALUES (
          :owner, :table_name, :index_name, l_inefficiency
        );
      END;'
    USING index_stats.owner, index_stats.table_name, index_stats.index_name;
  END LOOP;
END;
/

-- Display index analysis results
SELECT *
FROM index_analysis_results
ORDER BY inefficiency DESC;

-- Drop the temporary table
DROP TABLE index_analysis_results;

The index analysis results will show the inefficiency percentage for each index, indicating how effectively the index is being used. Higher inefficiency values may indicate that the index is not providing significant performance benefits and may require further analysis or tuning. 

Note: This script assumes that you have appropriate privileges to access the necessary system tables and execute the required procedures. Also, ensure that you review and customize the script as per your specific requirements and security guidelines.

_____________________________________________________________________________________________________________________

Website Stats