How to Identify and fix Index is Causing Performance Issue in oracle database ?

 

🔎 PART 1 — How to Identify Index is Causing Performance Issue

---

✅ Step 1: Find Slow SQL (Entry Point)

Start from Top SQL, not from the index.

SELECT sql_id, executions, elapsed_time, buffer_gets

FROM v$sql

ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

👉 Pick the problematic SQL

---

✅ Step 2: Check Execution Plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', NULL, 'ALLSTATS LAST'));

🔍 Look for:

Symptom	Meaning
FULL TABLE SCAN	Index missing or ignored ❌
INDEX RANGE SCAN with high cost	Index inefficient ❌
INDEX FULL SCAN	Poor selectivity ❌

---

✅ Step 3: Check Index Access Cost

In plan, observe:

• Cost
• Rows
• Bytes
• Buffer Gets

👉 If index scan:

• Reads too many blocks → Index is not selective

---

✅ Step 4: Check Clustering Factor (Critical)

SELECT index_name, clustering_factor, num_rows

FROM dba_indexes

WHERE index_name = 'INDEX_NAME';

Interpretation:

• CF ≈ number of rows → ❌ Random reads (BAD index)
• CF ≈ number of blocks → ✅ Efficient index

👉 High CF → more I/O → slower query

---

✅ Step 5: Check Selectivity (Very Important)

SELECT num_distinct, num_rows

FROM dba_tab_col_statistics

WHERE table_name = 'TABLE_NAME'

AND column_name = 'COLUMN_NAME';

👉 Formula:

Selectivity = num_distinct / num_rows

Result:

Value	Meaning
High (close to 1)	✅ Good index
Low (<0.1)	❌ Bad index

👉 Example:

• status = 'ACTIVE' → bad index
• customer_id → good index

---

✅ Step 6: Check If Index is Used Properly

ALTER INDEX schema.index_name MONITORING USAGE;

Then:

SELECT * FROM v$object_usage WHERE index_name = 'INDEX_NAME';

👉 Result:

• USED = NO → Index useless
• USED = YES but slow → Index is problem

---

✅ Step 7: Check Logical Reads (Performance Impact)

SELECT *

FROM v$segment_statistics

WHERE object_name = 'INDEX_NAME'

AND statistic_name = 'logical reads';

👉 High logical reads = heavy I/O → possible issue

---

✅ Step 8: Check Index BLEVEL (Depth)

SELECT index_name, blevel

FROM dba_indexes

WHERE index_name = 'INDEX_NAME';

BLEVEL	Meaning
0–2	✅ Good
3–4	⚠️ Ok
>4	❌ Too deep

---

✅ Step 9: Validate Stats

EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA','INDEX_NAME');

👉 Bad stats = wrong execution plan

---

✅ Step 10: Compare With Full Table Scan

Force test:

SELECT /*+ FULL(table_name) */ ..

👉 If FULL scan is faster → Index is hurting performance

---

🚨 Conclusion (Index Causing Issue If):

• High clustering factor
• Low selectivity
• High logical reads
• Not used or incorrectly used
• Execution plan shows inefficiency

---

✅ PART 2 — How to Check Index is GOOD or BAD

---

🎯 Step-by-Step Evaluation Framework

---

✅ Step 1: Usage Check

SELECT * FROM v$object_usage

WHERE index_name = 'INDEX_NAME';

Result	Decision
USED frequently	✅ Good
NOT USED	❌ Drop candidate

---

✅ Step 2: Query Alignment

👉 Check if index matches WHERE clause:

WHERE col1 = :1 AND col2 = :2

👉 Good index:

(col1, col2)

👉 Bad index:

(col3, col4)

---

✅ Step 3: Selectivity Check

• High distinct values → ✅ Good
• Low distinct values → ❌ Bad

---

✅ Step 4: Clustering Factor

• Low CF → ✅ Good
• High CF → ❌ Bad

---

✅ Step 5: Size vs Benefit

SELECT bytes/1024/1024 MB

FROM dba_segments

WHERE segment_name='INDEX_NAME';

👉 Large + rarely used → ❌ Bad

---

✅ Step 6: DML Overhead Check

👉 Too many indexes on high DML table:

• Slows INSERT/UPDATE/DELETE

SELECT index_name

FROM dba_indexes

WHERE table_name = 'TABLE_NAME';

👉 More indexes ≠ better

---

✅ Step 7: Redundancy Check

👉 Duplicate indexes = waste

---

✅ Step 8: BLEVEL + Leaf Blocks

👉 Small, compact index = good
👉 Huge fragmented index = bad

---

✅ FINAL DECISION MATRIX

Condition	Good Index ✅	Bad Index ❌
Usage	Frequent	Not used
Execution Plan	Index scan	Full scan
Selectivity	High	Low
Clustering Factor	Low	High
BLEVEL	≤2	>4
Size	Optimized	Huge unused
DML Impact	Low	High

---

🔥 REAL-WORLD QUICK CHECK SCRIPT

SELECT

i.index_name,

i.blevel,

i.clustering_factor,

i.num_rows,

i.distinct_keys,

s.bytes/1024/1024 MB

FROM dba_indexes i

JOIN dba_segments s

ON i.index_name = s.segment_name

WHERE i.owner = 'SCHEMA_NAME'

ORDER BY s.bytes DESC;

---

💡 Architect-Level Insight (Important)

👉 Don’t judge index in isolation

Always evaluate:

SQL → Execution Plan → Index → Data Pattern

---

✅ Recommended Approach (Best Practice)

1. Identify top SQL (AWR/V$SQL)
2. Analyze execution plan
3. Validate index usage
4. Check clustering factor + selectivity
5. Decide: KEEP / REBUILD / DROP
   
   

INDEX HEALTH CLASSIFICATION SCRIPT

set lines 300 pages 500

col  OWNER  for a10

col  INDEX_NAME  for a50

col TABLE_NAME for a50

WITH idx_stats AS (

    SELECT 

        i.owner,

        i.index_name,

        i.table_name,

        i.blevel,

        i.clustering_factor,

        i.num_rows,

        i.distinct_keys,

        s.bytes/1024/1024 AS size_mb,

        CASE 

            WHEN i.num_rows > 0 

            THEN ROUND(i.distinct_keys / i.num_rows, 4)

            ELSE 0

        END AS selectivity

    FROM dba_indexes i

    JOIN dba_segments s

        ON i.owner = s.owner

        AND i.index_name = s.segment_name

    WHERE i.owner  IN ('RITRS')

AND CLUSTERING_FACTOR >700

),

usage_stats AS (

    SELECT 

        index_name,

        CASE 

            WHEN used = 'YES' THEN 'USED'

            ELSE 'NOT_USED'

        END AS usage_status

    FROM v$object_usage

)

SELECT 

    i.owner,

    i.index_name,

    i.table_name,

    i.size_mb,

    i.blevel,

    i.clustering_factor,

    i.num_rows,

    i.distinct_keys,

    i.selectivity,

    NVL(u.usage_status, 'UNKNOWN') AS usage_status,

    CASE 

        WHEN NVL(u.usage_status, 'NOT_USED') = 'NOT_USED'

            THEN 'DROP_CANDIDATE'

        WHEN i.selectivity < 0.05

            THEN 'BAD_INDEX_LOW_SELECTIVITY'

        WHEN i.clustering_factor > i.num_rows * 0.9

            THEN 'BAD_INDEX_HIGH_CF'

        WHEN i.blevel > 4

            THEN 'REBUILD_REQUIRED'

        WHEN i.size_mb > 500 

             AND NVL(u.usage_status, 'NOT_USED') <> 'USED'

            THEN 'REVIEW_BIG_UNUSED_INDEX'

        ELSE 'GOOD_INDEX'

    END AS index_health_status

FROM idx_stats i

LEFT JOIN usage_stats u

    ON i.index_name = u.index_name

ORDER BY i.size_mb DESC