The Optimizer and Execution Plans

48.1 Cost-Based Optimizer (CBO)

Query transformation → cost estimation (using cardinality, selectivity, and cost from statistics) → plan generation.

Goals: OPTIMIZER_MODE = ALL_ROWS (default, throughput) vs FIRST_ROWS_n (response time).

Query transformations: view merging, predicate pushing, subquery unnesting, OR expansion, join elimination, star transformation, query rewrite with MVs, group-by placement, join factorization.

48.2 Getting Execution Plans

SQL Monitor (real-time, for long-running or parallel queries; needs the Tuning Pack):

SQL trace and TKPROF: The 10053 trace shows optimizer decisions.

EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);                         -- estimated

-- Actual plan with runtime statistics
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM orders WHERE customer_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PEEKED_BINDS +ADAPTIVE'));

-- From the cursor cache / AWR
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7h35uxf5uhmm1', 0, 'TYPICAL'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('7h35uxf5uhmm1'));

SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS

SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(sql_id => '7h35uxf5uhmm1', type => 'HTML') FROM dual;

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 123, serial_num => 456, waits => TRUE, binds => TRUE);
ALTER SESSION SET tracefile_identifier = 'mytest';
ALTER SESSION SET events '10046 trace name context forever, level 12';
-- tkprof orcl_ora_1234_mytest.trc out.txt sort=exeela,fchela sys=no

48.3 Reading a Plan

Read the indented tree: the most-indented operation executes first. Each parent consumes its children's rows.

Key columns: Id, Operation, Name, Rows (E-Rows), A-Rows, Starts, Cost, Buffers, Reads, A-Time, and the Predicate Information section (access vs filter).

The #1 tuning signal is a big gap between E-Rows and A-Rows (a cardinality misestimate).

48.4 Access Paths and Join Methods

TABLE ACCESS FULL Multiblock reads up to the HWM. Fine for large fractions of the data.

TABLE ACCESS BY ROWID Fastest single row INDEX UNIQUE / RANGE / FULL / FAST FULL / SKIP SCAN TABLE ACCESS INMEMORY FULL In-Memory column store Nested Loops

Small driving row source with an indexed inner table Large unsorted sets, equality joins Sort Merge Join Sorted inputs, non-equality joins Cartesian (Merge Join Cartesian)

Usually a bug: missing join condition or a bad estimate Semi / anti joins EXISTS / NOT EXISTS / IN / NOT IN

48.5 Adaptive Query Optimization (12c+)

Adaptive plans: join method switching (NL ↔ hash), adaptive parallel distribution.

Adaptive statistics: dynamic sampling, statistics feedback, SQL plan directives.

Parameters: OPTIMIZER_ADAPTIVE_PLANS (TRUE), OPTIMIZER_ADAPTIVE_STATISTICS (FALSE by default since 12.2).

48.6 Cursor Sharing and Bind Variables

Hard parse vs soft parse vs session cursor cache (softer soft parse).

Use bind variables to avoid excessive hard parsing and shared pool fragmentation (latch and mutex contention).

CURSOR_SHARING = EXACT (default) vs FORCE (replaces literals with system binds as a workaround).

Bind peeking, plus Adaptive Cursor Sharing (bind-sensitive / bind-aware cursors, V$SQL.IS_BIND_SENSITIVE) for skewed data.

Child cursors and V$SQL_SHARED_CURSOR (why cursors aren't shared). High version counts.