Oracle SQL Query Optimization: Execution Plans and Hints

Reading an execution plan

Always start with the execution plan before adding hints or indexes:

EXPLAIN PLAN FOR
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'OPEN' AND c.region = 'APAC';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Or for a query already in the shared pool:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'your_sql_id', format => 'ALLSTATS LAST'));

What to look for in the plan

Full Table Scans (TABLE ACCESS FULL): expected on small tables, concerning on large ones. Check if a suitable index exists and if the predicate is selective enough.

Cardinality estimates: the E-Rows column shows Oracle’s estimate; A-Rows (with ALLSTATS) shows actual. Large discrepancies indicate stale statistics — run DBMS_STATS.GATHER_TABLE_STATS.

Nested Loop vs Hash Join: nested loops are efficient when the inner result is small; hash joins are better for large set joins. Oracle usually chooses correctly — investigate when it doesn’t.

Gathering fresh statistics

Stale statistics cause poor plan choices more than almost anything else:

DBMS_STATS.GATHER_TABLE_STATS(
  ownname     => 'SCHEMA',
  tabname     => 'ORDERS',
  cascade     => TRUE,  -- includes indexes
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);

Common optimizer hints

Use hints as a last resort, after verifying statistics are fresh and appropriate indexes exist:

/*+ INDEX(o orders_status_idx) */    -- force specific index
/*+ FULL(o) */                        -- force full scan
/*+ USE_NL(o c) */                    -- force nested loop join
/*+ USE_HASH(o c) */                  -- force hash join
/*+ PARALLEL(o 4) */                  -- use 4 parallel processes
/*+ FIRST_ROWS(10) */                 -- optimise for first N rows

The PARALLEL hint for batch operations

For analytical queries or bulk operations on large tables, parallelism can dramatically reduce runtime:

SELECT /*+ PARALLEL(4) */ SUM(amount) FROM sales WHERE year = 2025;

Index strategies

Composite indexes: include all WHERE clause columns and SELECT columns for index-only scans. Column order matters — most selective first, or leading column must appear in WHERE clause.

Function-based indexes: for queries using functions on indexed columns:

CREATE INDEX idx_upper_email ON employees (UPPER(email));
-- Now this query uses the index:
WHERE UPPER(email) = 'USER@COMPANY.COM'