Oracle SQL Script for Index Usage and Performance:

advertisements

_____________________________________________________________________________________________________________________

1. Identify Underused or Unused Indexes 

 This script identifies underused or unused indexes in a specific schema. It queries the all_indexes data dictionary view based on the schema name, status, and index statistics (num_rows and leaf_blocks). The output displays the index name, table name, status, number of rows, and number of leaf blocks.

-- Query to identify underused or unused indexes
SELECT index_name, table_name, status, num_rows, leaf_blocks
FROM all_indexes
WHERE table_owner = 'YOUR_SCHEMA_NAME'
  AND status = 'VALID'
  AND num_rows = 0
  AND leaf_blocks = 0;

-- Sample Output:
-- INDEX_NAME         TABLE_NAME     STATUS  NUM_ROWS  LEAF_BLOCKS
-- -----------------  -------------  ------  --------  -----------
-- IDX_CUSTOMER_ID    CUSTOMERS      VALID   0         0
-- IDX_ORDER_ID       ORDERS         VALID   0         0

advertisements

 

2. Find Index Usage Statistics for a Specific Index 

 This script provides index usage statistics for a specific index. It joins the all_indexes and all_tab_statistics data dictionary views based on the owner, table name, and index name. The output includes the table name, statistic name, index status, number of rows, number of leaf blocks, and corresponding value (such as the number of index scans or fetches).

-- Query to find index usage statistics for a specific index
SELECT t.TABLE_NAME, t.STATISTIC_NAME, i.STATUS, i.NUM_ROWS, i.LEAF_BLOCKS, t.VALUE
FROM all_indexes i
JOIN all_tab_statistics t ON i.owner = t.owner AND i.table_name = t.table_name AND i.index_name = t.index_name
WHERE i.index_name = 'YOUR_INDEX_NAME';

-- Sample Output:
-- TABLE_NAME     STATISTIC_NAME      STATUS  NUM_ROWS  LEAF_BLOCKS  VALUE
-- -------------  -----------------  ------  --------  -----------  -----
-- CUSTOMERS      IDX_CUSTOMER_ID    VALID   1000      50           100
-- CUSTOMERS      IDX_CUSTOMER_ID    VALID   1000      50           200

3. Analyze Impact of an Index on Query Performance 

 This script allows you to analyze the impact of an index on query performance. Replace with the actual query you want to analyze. The script uses the EXPLAIN PLAN FOR statement to generate the execution plan for the query, and the DBMS_XPLAN.DISPLAY function to display the plan. The output provides details about the execution plan, including the operations performed and the corresponding index usage. Please customize 'YOUR_SCHEMA_NAME' and 'YOUR_INDEX_NAME' with the appropriate schema and index names in the scripts. Adjust the queries as needed for schema-specific information or additional details.

-- Query to analyze the impact of an index on query performance
EXPLAIN PLAN FOR
<YOUR_QUERY>;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Sample Output:
-- PLAN_TABLE_OUTPUT
-- ---------------------------------------------------------------------
-- Plan hash value: 1234567890
-- 
-- ---------------------------------------------------------------------------------
-- | Id  | Operation                   | Name             | Rows  | Bytes | Cost  |
-- ---------------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT            |                  |       |       |       |
-- |   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS        |  1000 | 10000 |     3 |
-- |   2 |   INDEX FULL SCAN           | IDX_CUSTOMER_ID  |  1000 |       |     2 |
-- ---------------------------------------------------------------------------------

_____________________________________________________________________________________________________________________

Website Stats