🔎 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:
CostRowsBytesBuffer 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 indexcustomer_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 uselessUSED = 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)
- Identify top SQL (AWR/V$SQL)
- Analyze execution plan
- Validate index usage
- Check clustering factor + selectivity
- 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
