Master Oracle SQL Performance: 40 Proven Optimization Techniques

Oracle processes a SQL statement through several stages: syntax analysis, semantic analysis, view and expression transformation, optimizer selection, join method and order determination, data access path selection, and finally execution of the plan.

Choosing the Right Optimizer

Oracle offers three optimizer modes: RULE (rule‑based), COST (cost‑based), and CHOOSE (automatic selection). Selecting the appropriate mode influences how execution paths are evaluated.

Table Access Methods

Full Table Scan : Reads all rows sequentially, optimized by loading multiple data blocks at once.

ROWID Access : Uses the physical ROWID stored in indexes for fast row retrieval.

Shared SQL and the SGA Pool

Executed SQL statements are cached in the SGA shared pool. Adjusting the pool size can reduce parsing overhead and improve performance.

Driving Table Selection

Oracle parses the FROM clause from right to left; the rightmost table (driving table) is accessed first. In rule‑based optimization, this order determines join processing.

WHERE Clause Ordering

Oracle evaluates WHERE conditions from bottom‑up (right‑to‑left). Place the most selective predicates last to filter rows early.

Avoid Using "*" in SELECT

Oracle expands "*" by querying the data dictionary, adding overhead. List required columns explicitly.

Reduce Database Round‑Trips

Each SQL execution triggers parsing, index usage estimation, bind variable handling, and block reads. Consolidating multiple simple queries into a single statement reduces I/O and CPU usage.

Counting Rows Efficiently

SELECT COUNT(*) FROM table_name;</code>
<code>SELECT COUNT(1) FROM table_name;</code>
<code>SELECT COUNT(column) FROM table_name;

Without a primary‑key index, COUNT(1) is often fastest; with an indexed column, COUNT(column) is optimal.

Replace HAVING with WHERE

HAVING filters after aggregation, requiring sorting and aggregation of all rows. Moving filter conditions to WHERE reduces work.

Use EXISTS Instead of IN

EXISTS stops scanning as soon as a matching row is found, while IN may materialise the entire sub‑query.

Replace NOT IN with NOT EXISTS or OUTER JOIN

NOT IN forces a full scan of the sub‑query; NOT EXISTS or an outer join can use indexes.

Prefer JOIN over EXISTS for Simple Joins

Direct table joins are usually more efficient than wrapping them in EXISTS.

Replace DISTINCT with EXISTS

When deduplication can be expressed via EXISTS, the optimizer can avoid costly sorting.

Identify Inefficient SQL

SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,
       ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_ratio,
       ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
       SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0 AND BUFFER_GETS>0
  AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

Use V$SQLAREA or SQL Trace to locate high‑cost statements.

Explain Plan

EXPLAIN PLAN shows the optimizer’s chosen access paths (index scan, full scan, join method) without executing the query.

SQL*Plus TRACE

SQL> SET AUTOTRACE TRACEONLY
SQL> /

Displays execution plan and statistics.

Index Usage

Index Unique Scan vs. Index Range Scan.

Driving table selection differs between CBO (cost‑based) and RBO (rule‑based).

When multiple indexes are eligible, the optimizer prefers the one with the highest selectivity; the leftmost column of a composite index must appear in the WHERE clause.

Avoid functions, arithmetic, or NOT on indexed columns—they disable index usage.

Prefer equality predicates over range predicates for better selectivity.

Use the leading column of a composite index; otherwise a full table scan occurs.

Force index usage with hints (INDEX, FULL, ROWID, CACHE, ALL_ROWS, FIRST_ROWS, USE_NL, USE_MERGE, USE_HASH, etc.) only for critical queries.

Other Optimizations

Replace OR with UNION or IN for indexed columns.

Use UNION ALL instead of UNION when duplicate elimination is not required.

Avoid resource‑intensive operations like DISTINCT, UNION, MINUS, INTERSECT, and ORDER BY when possible.

Filter rows before GROUP BY to reduce aggregation work.

Be cautious with date arithmetic; adding >0.99999 days rolls over to the next day.

Prefer explicit cursors over implicit ones to avoid the "TOO MANY ROWS" check.

Store tables and indexes in separate tablespaces and on different disks for I/O parallelism.

Applying these 40 techniques will address the majority of common Oracle SQL performance issues.