Oracle Index Tuning Guide - Performance Optimization Best Practices

Oracle Index Tuning Guide

Comprehensive guide to Oracle index analysis, optimization, and maintenance.

Index Usage Analysis

Find Unused Indexes

-- Enable index monitoring (per index)
ALTER INDEX hr.emp_name_idx MONITORING USAGE;

-- Check if index was used
SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING
FROM V$OBJECT_USAGE
WHERE INDEX_NAME = 'EMP_NAME_IDX';

-- Disable monitoring
ALTER INDEX hr.emp_name_idx NOMONITORING USAGE;

Index Usage from AWR (12c+)

-- Index usage statistics
SELECT
  owner,
  name AS index_name,
  total_access_count,
  total_exec_count,
  last_used
FROM DBA_INDEX_USAGE
WHERE owner = 'HR'
ORDER BY total_access_count DESC NULLS LAST;

---

Index Analysis Queries

Index Size and Statistics

-- Index sizes
SELECT
  owner,
  index_name,
  table_name,
  ROUND(bytes/1024/1024, 2) AS size_mb,
  num_rows,
  distinct_keys,
  clustering_factor
FROM DBA_INDEXES i
JOIN DBA_SEGMENTS s ON i.owner = s.owner AND i.index_name = s.segment_name
WHERE i.owner = 'HR'
ORDER BY bytes DESC;

Index Efficiency (Clustering Factor)

-- Compare clustering factor to table rows
SELECT
  i.index_name,
  i.table_name,
  i.num_rows AS index_rows,
  t.num_rows AS table_rows,
  i.clustering_factor,
  i.blevel,
  i.leaf_blocks,
  CASE
    WHEN i.clustering_factor < t.num_rows THEN 'Good'
    WHEN i.clustering_factor < t.blocks * 10 THEN 'Moderate'
    ELSE 'Poor'
  END AS efficiency
FROM DBA_INDEXES i
JOIN DBA_TABLES t ON i.table_owner = t.owner AND i.table_name = t.table_name
WHERE i.owner = 'HR'
ORDER BY i.clustering_factor DESC;

---

Identifying Missing Indexes

From SQL Tuning Advisor

-- Run SQL Tuning Advisor
DECLARE
  l_task_name VARCHAR2(30) := 'index_analysis';
BEGIN
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => '&sql_id',
    task_name => l_task_name
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
END;
/

-- View recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('index_analysis') FROM DUAL;

From Execution Plans

-- Find full table scans on large tables
SELECT
  sql_id,
  plan_hash_value,
  operation,
  object_owner,
  object_name,
  cardinality,
  cost
FROM V$SQL_PLAN
WHERE operation = 'TABLE ACCESS'
  AND options = 'FULL'
  AND cardinality > 10000
ORDER BY cost DESC;

Columns Without Indexes

-- Find frequently filtered columns without indexes
SELECT
  table_name,
  column_name,
  num_distinct,
  density
FROM DBA_TAB_COLUMNS
WHERE owner = 'HR'
  AND num_distinct > 100
  AND column_name NOT IN (
    SELECT column_name FROM DBA_IND_COLUMNS
    WHERE table_owner = 'HR' AND table_name = c.table_name
  )
ORDER BY num_distinct DESC;

---

Index Types and When to Use

B-Tree Index (Default)

-- Best for: High cardinality, range queries, ORDER BY
CREATE INDEX emp_salary_idx ON employees(salary);

-- Composite index
CREATE INDEX emp_dept_sal_idx ON employees(department_id, salary);

Bitmap Index

-- Best for: Low cardinality, data warehouse, AND/OR queries
CREATE BITMAP INDEX emp_status_idx ON employees(status);

-- Note: Avoid in OLTP due to lock contention

Function-Based Index

-- Index on expression
CREATE INDEX emp_upper_name_idx ON employees(UPPER(last_name));

-- Enable query to use index
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

Invisible Index

-- Create without affecting optimizer (for testing)
CREATE INDEX emp_test_idx ON employees(hire_date) INVISIBLE;

-- Test with hint
SELECT /*+ INDEX(e emp_test_idx) */ * FROM employees e;

-- Make visible if beneficial
ALTER INDEX emp_test_idx VISIBLE;

---

Index Maintenance

Rebuild Indexes

-- Online rebuild (recommended)
ALTER INDEX hr.emp_idx REBUILD ONLINE;

-- Rebuild with new tablespace
ALTER INDEX hr.emp_idx REBUILD TABLESPACE idx_ts ONLINE;

-- Rebuild with compression
ALTER INDEX hr.emp_idx REBUILD COMPRESS ONLINE;

-- Coalesce instead of rebuild (less resource intensive)
ALTER INDEX hr.emp_idx COALESCE;

When to Rebuild

-- Check index fragmentation
SELECT
  index_name,
  blevel,
  leaf_blocks,
  del_lf_rows_len,
  ROUND(del_lf_rows_len / (leaf_blocks * 8192) * 100, 2) AS pct_deleted
FROM INDEX_STATS;

-- Note: Run ANALYZE INDEX ... VALIDATE STRUCTURE first
ANALYZE INDEX hr.emp_idx VALIDATE STRUCTURE;

Index Statistics

-- Gather index statistics
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_IDX');

-- Gather all indexes for a table
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => TRUE);

---

Composite Index Strategy

Column Order Matters

-- For query: WHERE dept_id = 10 AND status = 'ACTIVE' AND salary > 50000

-- Best order: equality columns first, range column last
CREATE INDEX emp_comp_idx ON employees(department_id, status, salary);

-- This index can be used for:
-- WHERE department_id = 10
-- WHERE department_id = 10 AND status = 'ACTIVE'
-- WHERE department_id = 10 AND status = 'ACTIVE' AND salary > 50000

Index Skip Scan

-- Optimizer may skip leading column if cardinality is low
-- For index on (gender, last_name)
SELECT * FROM employees WHERE last_name = 'SMITH';
-- May use index skip scan if gender has few distinct values

---

Index Compression

-- Compress duplicate values in composite indexes
CREATE INDEX emp_dept_job_idx ON employees(department_id, job_id) COMPRESS 1;

-- Advanced compression (12c+)
CREATE INDEX emp_adv_idx ON employees(department_id) COMPRESS ADVANCED LOW;

-- Check compression ratio
SELECT index_name, compression, prefix_length
FROM DBA_INDEXES
WHERE owner = 'HR';

---

Parallel Index Operations

-- Create index in parallel
CREATE INDEX emp_idx ON employees(last_name) PARALLEL 4;

-- Rebuild in parallel
ALTER INDEX emp_idx REBUILD PARALLEL 4;

-- Reset to non-parallel for DML
ALTER INDEX emp_idx NOPARALLEL;

---

Index Hints

-- Force specific index
SELECT /*+ INDEX(e emp_name_idx) */ * FROM employees e WHERE last_name = 'SMITH';

-- Force full table scan (compare performance)
SELECT /*+ FULL(e) */ * FROM employees e WHERE last_name = 'SMITH';

-- Force index range scan
SELECT /*+ INDEX_RS(e emp_idx) */ * FROM employees e;

-- Force index fast full scan
SELECT /*+ INDEX_FFS(e emp_idx) */ * FROM employees e;

---

Troubleshooting Index Issues

Index Not Being Used

-- Check execution plan
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE last_name = 'SMITH';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Common reasons:
-- 1. Statistics stale
-- 2. Data type mismatch (implicit conversion)
-- 3. Function on indexed column
-- 4. Leading wildcard (LIKE '%SMITH')
-- 5. NULL comparison (index doesn't include NULLs by default)
-- 6. Optimizer chose full scan as more efficient

Check for Implicit Conversion

-- This prevents index use
SELECT * FROM employees WHERE employee_id = '100';  -- String to number

-- This uses index
SELECT * FROM employees WHERE employee_id = 100;

Index on NULL Values

-- Standard B-tree doesn't index NULL
-- Include constant to index NULLs
CREATE INDEX emp_mgr_idx ON employees(manager_id, 0);

-- Now this can use the index
SELECT * FROM employees WHERE manager_id IS NULL;

---

Performance Monitoring

-- Index wait events
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO/1000000 AS secs
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE '%index%'
ORDER BY TIME_WAITED_MICRO DESC;

-- Index I/O statistics
SELECT
  owner,
  object_name,
  statistic_name,
  value
FROM V$SEGMENT_STATISTICS
WHERE object_type = 'INDEX'
  AND owner = 'HR'
  AND value > 0
ORDER BY value DESC;

---

Related Resources

• Schema Analysis Scripts
• Performance Analysis Scripts
• ORA-01652: Unable to Extend Temp
• ORA-01654: Unable to Extend Index