How Oracle Executes a SQL Statement: From Parsing to Optimizer

Introduction

The author revisits the Oracle SQL execution flow after a group discussion, referencing official Oracle documentation and personal examples to clarify each processing stage.

Execution Phases Overview

Oracle processes a SQL statement in five distinct phases: SQL statement submission, parsing, optimizer, row‑source generation, and execution.

Note: The theoretical points are derived from Oracle's public documentation combined with the author’s interpretation.

Phase 1 – SQL Statement Phase

The client or application sends the SQL text to the database.

Phase 2 – Parse Phase

The parser breaks the statement into an internal data structure and performs three checks:

Syntax check – validates SQL grammar (e.g., SELECT * FORM table; raises ORA‑00923).

Semantic check – verifies object and column existence (e.g., querying a non‑existent table raises ORA‑00942).

Shared‑pool check – computes a hash value (SQL ID) and looks for a matching entry in the library cache.

If a matching entry exists, a soft parse reuses the cached execution plan; otherwise, a hard parse builds a new plan and stores it in the shared pool.

Hard vs. Soft Parse

Hard parse involves library‑cache miss, dictionary lookups, and locking, which increase latency. Soft parse (library‑cache hit) skips optimizer and row‑source generation, improving performance.

SELECT sql_id, executions, parse_calls, sql_text
FROM v$sql
WHERE sql_text LIKE 'select * from LIU_ORACLEOLTP_YWCS_EMPLOYEES%';

Phase 3 – Optimizer Phase

The optimizer selects the lowest‑cost execution plan using statistics. Oracle offers two optimizer types:

Rule‑Based Optimizer (RBO) – applies predefined rules; deprecated after 10g.

Cost‑Based Optimizer (CBO) – evaluates cost using statistics (I/O, CPU, memory) and chooses the cheapest plan.

Optimizer components:

Query Transformer – rewrites queries for lower cost (e.g., converting OR to UNION ALL).

Estimator – calculates selectivity, cardinality, and cost.

Plan Generator – explores access paths, join methods, and orders, then picks the minimal‑cost plan.

Optimizer Modes

SHOW PARAMETER optimizer_mode;
-- first_rows_n, first_rows, all_rows (CBO)
-- rule, choose (RBO, deprecated)
ALTER SESSION SET optimizer_mode = all_rows;

Phase 4 – Row‑Source Generation Phase

The optimizer’s chosen plan is transformed into a row‑source tree that defines table order, access methods, join methods, and operations such as filtering or sorting.

Phase 5 – Execution Phase

The database executes the row‑source tree, retrieving rows from tables or indexes and applying joins, filters, and aggregations until the final result set is returned to the client.

SELECT e.last_name, j.job_title, d.department_name
FROM hr.employees e, hr.departments d, hr.jobs j
WHERE e.department_id = d.department_id
  AND e.job_id = j.job_id
  AND e.last_name LIKE 'A%';

The execution plan is displayed as a tree where black boxes represent physical data retrieval (table scans, index scans) and white boxes represent operations (hash joins, filters).

Conclusion

The article consolidates scattered Oracle documentation into a coherent walkthrough of SQL statement processing, emphasizing the importance of understanding each phase for effective query tuning.