Oracle Database: Performance Tuning

Indexes

-- B-tree index (default)
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_emp_name ON employees(last_name, first_name);  -- composite

-- Unique index
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- Function-based index (for queries on expressions)
CREATE INDEX idx_upper_last ON employees(UPPER(last_name));
-- Now this uses the index:
SELECT * FROM employees WHERE UPPER(last_name) = 'KING';

-- Bitmap index (low-cardinality columns, data warehouse)
CREATE BITMAP INDEX idx_emp_job ON employees(job_id);

-- Partial index (only index certain rows)
CREATE INDEX idx_active_orders ON orders(order_date)
WHERE status = 'ACTIVE';

-- Index on partitioned table
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;  -- local = per partition

-- Check index usage
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- Drop index
DROP INDEX idx_emp_dept;

-- Rebuild fragmented index
ALTER INDEX idx_emp_dept REBUILD ONLINE;

Execution Plans & EXPLAIN PLAN

-- View execution plan
EXPLAIN PLAN FOR
SELECT e.last_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Or use AUTOTRACE in SQL*Plus
SET AUTOTRACE ON;
SELECT * FROM employees WHERE department_id = 60;

-- From v$sql_plan (for already-executed queries)
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'abc123xyz'));

-- Key operations to look for:
-- TABLE ACCESS FULL   — no index used (bad for large tables)
-- INDEX RANGE SCAN    — good, uses index
-- INDEX UNIQUE SCAN   — best, exact match on unique index
-- HASH JOIN           — large table joins (good)
-- NESTED LOOPS        — small table joins (good when driving table is small)
-- SORT MERGE JOIN     — sorted inputs

-- Check statistics age (stale stats = bad plans)
SELECT table_name, last_analyzed, num_rows
FROM user_tables
ORDER BY last_analyzed;

-- Gather fresh stats
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => TRUE);

Hints & Query Optimization

-- Hints: override optimizer decisions (use sparingly, as a last resort)

-- Force index use
SELECT /*+ INDEX(e idx_emp_dept) */ * FROM employees e WHERE department_id = 60;

-- Force full table scan (when FTS is actually faster for large result sets)
SELECT /*+ FULL(e) */ * FROM employees e WHERE salary > 1000;

-- Join order hints
SELECT /*+ LEADING(d e) USE_NL(e) */ *
FROM departments d JOIN employees e ON d.department_id = e.department_id;

-- Parallel query
SELECT /*+ PARALLEL(employees, 4) */ * FROM employees;

-- Result cache (cache query result in SGA)
SELECT /*+ RESULT_CACHE */ * FROM expensive_view;

-- Common performance tips
-- 1. Avoid functions on indexed columns in WHERE clause:
--    Bad:  WHERE TO_CHAR(hire_date, 'YYYY') = '2020'
--    Good: WHERE hire_date >= DATE '2020-01-01' AND hire_date < DATE '2021-01-01'

-- 2. Use bind variables (not literals) to share cursor cache
--    Bad:  WHERE id = 100
--    Good: WHERE id = :emp_id

-- 3. Use EXISTS instead of IN for correlated subqueries
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);