You type this:

SELECT c.name, SUM(o.amount) AS total_amount FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.country = 'IN' GROUP BY c.name;

A few milliseconds later, rows come back. It feels like one step, but it's a pipeline, and most of the interesting decisions happen before a single row is read.

I work on optimizer internals, so this is my favorite part of the database. Here's the big picture.

Parse -> Optimize -> Choose a plan -> Execute

Query Life Cycle

  1. Parse: validate the SQL and look for an existing cursor to reuse.
  2. Optimize: the cost-based optimizer (CBO) explores many ways to answer the query.
  3. Choose a plan: the lowest-cost candidate becomes the execution plan.
  4. Execute: the plan runs and rows are fetched back to you.

1. Parse: "Is this even a valid question?"

Oracle first checks that your SQL makes sense:

  • Syntax check: a typo like SELEC dies here.
  • Semantic check: do the tables and columns exist, and do you have privileges on them?
  • Shared pool check: Oracle hashes the statement text into a SQL_ID and looks in the library cache.
    • Soft parse: a matching cursor exists and is reused. Cheap.
    • Hard parse: no match, so Oracle runs full optimization. Expensive.

You can watch this for your own session:

SELECT n.name, m.value FROM v$mystat m JOIN v$statname n ON n.statistic# = m.statistic# WHERE n.name IN ('parse count (total)', 'parse count (hard)');

Run your query, then run this again. If "parse count (hard)" went up, Oracle had to build a new plan from scratch.

2. Optimize: "What are all the ways I could answer this?"

SQL is declarative: you say what you want, and the CBO decides how. The same query can be executed many ways, and the gap between the best and worst plan can be milliseconds versus hours.

The optimizer considers:

  • Query transformations: view merging, subquery unnesting, predicate pushing, join elimination.
  • Access paths: full table scan, index range scan, index fast full scan.
  • Join order: customers -> orders or orders -> customers. With many tables, the number of orderings explodes.
  • Join methods: nested loops, hash join, sort-merge.

To compare candidates, it estimates cardinality (how many rows each step produces) from optimizer statistics, then computes a cost for each plan.

The optimizer never runs the candidates. It predicts which one will be cheapest.

That's why stale statistics cause so many "it was fast yesterday" incidents. Bad estimates lead to bad plans. Refresh stats with:

BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => USER, tabname => 'CUSTOMERS', cascade => TRUE -- include indexes ); END; /

3. Choose a plan: "Pick the winner"

The lowest-cost candidate becomes the execution plan, a tree of row sources. To see the predicted plan:

EXPLAIN PLAN FOR SELECT c.name, SUM(o.amount) AS total_amount FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.country = 'IN' GROUP BY c.name; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Illustrative output (trimmed):

| Id | Operation | Name | Rows | |----|---------------------------------------|-----------------------|------| | 0 | SELECT STATEMENT | | 500 | | 1 | HASH GROUP BY | | 500 | |* 2 | HASH JOIN | | 25K | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 500 | |* 4 | INDEX RANGE SCAN | IDX_CUSTOMERS_COUNTRY | 500 | | 5 | TABLE ACCESS FULL | ORDERS | 500K | Predicate Information (identified by operation id): 2 - access("O"."CUSTOMER_ID"="C"."ID") 4 - access("C"."COUNTRY"='IN')

Read it by indentation, innermost first. Oracle uses the index to find Indian customers, builds a hash table from that small set, scans orders once to probe it, then aggregates.

If the statistics said 90% of customers were in India, the CBO would likely skip the index entirely and choose a different plan.

4. Execute: "Now actually do it"

The cursor lifecycle is Parse -> Bind -> Execute -> Fetch. While executing, Oracle:

  • Reads blocks from the buffer cache, or from disk on a miss.
  • Uses undo to give your query a read-consistent view of the data.
  • Spills to temp if a hash join or sort doesn't fit in PGA memory.
  • Streams rows back to the client in fetch batches.

EXPLAIN PLAN only shows the prediction. To see what actually happened:

SELECT /*+ GATHER_PLAN_STATISTICS */ c.name, SUM(o.amount) AS total_amount FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.country = 'IN' GROUP BY c.name; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

Compare E-Rows (estimated) with A-Rows (actual). A big gap between them usually points straight at your problem.

Why this matters as a developer

Same SQL, different speed usually means the optimizer chose a different plan because data or statistics changed.

Use bind variables so Oracle can soft parse instead of hard parsing on every call:

SELECT c.name, SUM(o.amount) AS total_amount FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.country = :country GROUP BY c.name;

Indexes are options, not commands. Creating one doesn't force the CBO to use it. It only does so if the estimated cost is lower.