A Practical Guide for Oracle DBAs
Indexes play a critical role in Oracle database performance. While well-designed indexes can dramatically improve query response time, unused or missing indexes can just as easily hurt performance. This blog explains how Oracle DBAs can identify unused indexes and missing indexes, along with best practices and real-world considerations.
Why Index Review Matters
Over time, databases evolve:
- New queries are introduced
- Old reports are retired
- Tables grow in size
- Data access patterns change
Without periodic review, databases accumulate unused indexes while still suffering from slow queries due to missing indexes.
A balanced index strategy is essential.
Index Usage Checks (Finding Unused Indexes)
What Is Index Monitoring?
Oracle provides a built-in feature to track whether an index is used by the Cost-Based Optimizer.
ALTER INDEX index_name MONITORING USAGE;
Oracle records index usage in the DBA_OBJECT_USAGE view.
Checking Index Usage
SELECT
table_name,
index_name,
used
FROM dba_object_usage
WHERE index_name IS NOT NULL;
If USED = NO, the index was not chosen by the optimizer during the monitoring window.
Benefits of Index Usage Checks
- Identifies unused indexes
- Reduces storage consumption
- Improves DML performance
- Simplifies index landscape
Limitations
- Monitoring tracks only optimizer-chosen usage
- Rare batch jobs may not run during monitoring
- Indexes used via hints or outlines may not be captured
Tip: Always monitor for at least one full business cycle.
Missing Index Checks (Finding Performance Gaps)
Identifying Full Table Scans
Frequent full table scans on large tables often indicate missing indexes.
SELECT DISTINCT
object_owner,
object_name
FROM v$sql_plan
WHERE operation = ‘TABLE ACCESS’
AND options = ‘FULL’;
High I/O Tables
SELECT
owner,
segment_name,
SUM(physical_reads) reads
FROM v$segment_statistics
WHERE statistic_name = ‘physical reads’
GROUP BY owner, segment_name
ORDER BY reads DESC;
Tables with high physical reads are strong candidates for indexing review.
Foreign Keys Without Indexes
Unindexed foreign keys can cause severe performance issues during deletes or updates.
SELECT owner, table_name, constraint_name
FROM dba_constraints
WHERE constraint_type = ‘R’
AND NOT EXISTS (
SELECT 1
FROM dba_ind_columns i
WHERE i.table_owner = owner
AND i.table_name = table_name
);
Benefits of Missing Index Checks
- Faster query response times
- Reduced I/O and CPU usage
- Improved concurrency
- Better scalability for OLTP systems
Risks of Adding Indexes
- Increased DML overhead
- Additional storage usage
- More objects to maintain
Index creation should always be driven by real SQL workloads, not assumptions.
