SQL scripts for index usage by SQL statements in Oracle

advertisements

_____________________________________________________________________________________________________________________

1. Identify SQL Statements Not Using an Existing Index 

This script identifies SQL statements that are not using an existing index. It queries the v$sql view to find statements that do not contain hints for index usage (/*+ INDEX(...) */ or /*+ FULL(...) */). This helps identify potential opportunities for index optimization.

-- Query to identify SQL statements that are not using an existing index
SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text NOT LIKE '%/*+ INDEX(%' AND sql_text NOT LIKE '%/*+ FULL(%';

advertisements

 

2. Find the Index Used by a Specific SQL Statement 

This script finds the index used by a specific SQL statement. Replace with the actual SQL statement 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 index used. Please customize with the actual SQL statement you want to analyze. Always be cautious when analyzing and optimizing SQL statements, as changing indexes can impact database performance. Always test changes in a controlled environment before applying them to production systems.

-- Query to find the index used by a specific SQL statement
EXPLAIN PLAN FOR
<YOUR_SQL_STATEMENT>;

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