Methods for Retrieving Execution Plans

Database administrators and developers rely on several techniques to capture how the Cost-Based Optimizer (CBO) resolves SQL statements. Each method offers distinct advantages regarding runtime statistics, historical analysis, and overhead.

1. EXPLAIN PLAN FOR

This approach instructs the optimizer to generate a theoretical execution path without actually running the query. It is ideal for long-running statements that might timeout or block resources.

EXPLAIN PLAN FOR
SELECT p.product_name, p.unit_price
FROM sales.products p
WHERE p.category_id = 105 AND p.stock_qty > 0;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Limitation: Does not provide runtime metrics such as actual rows processed, logical/physical I/O, or recursive calls.

2. SET AUTOTRACE

Enabled at the session level, this commmand executes the query and prints both the execution path and performance statistics. It is highly useful for spotting unnecessary sorts or verifying index usage.

-- Grant required role if missing for non-privileged users
@?/sqlplus/admin/plustrce.sql
GRANT plustrace TO analyst_user;

SET AUTOTRACE TRACEONLY EXPLAIN;
SELECT order_id, total_amount FROM orders.customer_orders
WHERE order_date >= TRUNC(SYSDATE) - 30;
SET AUTOTRACE OFF;

Variants: ON (results + plan + stats), TRACEONLY (plan + stats only, suppresses result set), EXPLAIN (plan only), STATISTICS (stats only).

3. DBMS_XPLAN.DISPLAY_CURSOR

Retrieves plans from the library cache using a SQL_ID or child cursor number. Adding format modifiers reveals actual execution metrics.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5c4f6g8h9j0k1', 0, 'ALLSTATS LAST'));

This method requires the target cursor to remain in the shared pool. It does not generate runtime statistics unless STATISTICS_LEVEL=ALL or the /*+ GATHER_PLAN_STATISTICS */ hint was active during execution.

4. Historical SQL via V$SQL

For statements no longer in the library cache, you can locate them by text pattern and fetch the plan:

SELECT sql_id, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE '%FROM hr.employee_records WHERE salary%';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9f8e7d6c5b4a3', 0, 'ADVANCED'));

5. Event 10046

Enabling level 1, 4, 8, or 12 of this trace event captures the exact runtime plan, wait events, bind variables, and recursive SQL. It is the most comprehensive but introduces overhead and requires post-processing (e.g., TKPROF).

6. STATISTICS_LEVEL = ALL

Setting this session parameter forces the optimizer to collect detailed runtime metrics (actual vs. estimated rows, execution time) for subsequent queries. It eliminates the need for explicit hints but cannot suppress result output like AUTOTRACE TRACEONLY.

ALTER SESSION SET STATISTICS_LEVEL = ALL;
-- Execute target query here
ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

7. AWR SQL Report (awrsqrpt.sql)

Designed for historical performance analysis. It extracts plan changes, execution frequency, and resource consumption over specified snapshot intervals with out requiring real-time tracing.

---

Interpreting Execution Plan Structure

Reading Order

Execution flow follows a right-to-left, top-to-bottom hierarchy. Indentation indicates child operations. The most indented, topmost operation executes first. Parentheses or explicit join order hints can override default cost-based decisions.

Key Fields

• ID: Logical operation sequence, not execution order.
• Operation: The physical or logical step (e.g., TABLE ACCESS FULL, INDEX RANGE SCAN, HASH JOIN).
• Rows (E-Rows): Optimizer's cardinality estimate.
• Bytes: Estimated data volume for the step.
• Cost (%CPU): Normalized resource expenditure based on I/O and CPU models.
• Time: Estimated duration for the operation.

Predicate Types

• Access: Drives data retrieval. Determines which index or partition is used to locate rows. Highly selective access predicates drastically reduce I/O.
• Filter: Applied after rows are retrieved. Does not influence the access path but reduces the final result set. Excessive filtering often indicates missing indexes or poor join conditions.

Runtime Statistics

• Recursive Calls: Internal SQL statements executed by Oracle to resolve dictionary lookups, security checks, or dynamic space allocation.
• DB Block Gets: Blocks retrieved in current mode (typically for DML or reading live data dictionary metadata).
• Consistent Gets: Blocks retrieved in a read-consistent state using undo segments to reconstruct older versions. Dominates read-heavy workloads.
• Physical Reads: Blocks fetched from disk into the buffer cache. High values indicate cache misses or full table scans.
  Buffer Cache Hit Ratio ≈ 1 - (physical_reads / (consistent_gets + db_block_gets)). Values above 90% are generally desirable.
• Sorts (Memory/Disk): Memory sorts are optimal. Disk sorts occur when the PGA SORT_AREA_SIZE is exhausted, triggering temporary tablespace usage and severe performance degradation.
• SQL*Net Statistics: Measure network roundtrips and byte transfer volumes between client and server.

Cache Flushing (Testing Only):

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

Never execute in production. Use to simulate cold-start behavior.### Dynamic Sampling

When optimizer statistics are missing or stale, the CBO may invoke dynamic sampling. It performs a low-overhead random read of blocks to estimate cardinality and selectivity. Results are not persisted to the data dictionary but improve plan accuracy for ad-hoc queries.

---

Core Optimization Concepts

ROWID

A pseudocolumn storing the physical address of a row (file number, block number, row slot). Accessing data via ROWID is the fastest single-row retrieval method, utilizing single-block I/O.

Recursive SQL

Background statements triggered by DDL/DML to maintain data dictionary consistency, manage extents, or enforce constraints. Users do not interact with them directly, but they contribute to recursive call counts.

Selectivity & Composite Indexes

Selectivity = Unique Values / Total Rows. Approaching 1.0 indicates high selectivity, making the column an ideal index candidate. Composite indexes rely on leading columns. Queries must reference the leading column(s) to trigger index usage efficiently.

Driving vs. Probed Tables

The CBO determines the driving (outer) table based on cost estimation, not FROM clause order. The driving table should return the fewest rows after filter predicates are applied. The probed (inner) table benefits from an indexed join column to avoid repeated full scans during nested operations.

---

Data Access Paths

Full Table Scan (FTS)

Reads every block in a table using multi-block I/O (DB_FILE_MULTIBLOCK_READ_COUNT). Efficient when retrieving >5–10% of rows, performing parallel queries, or when small tables lack indexes.

Index Unique Scan

Triggered by PRIORARY KEY or UNIQUE constraints with equality predicates. Returns exactly one ROWID. Combined with a TABLE ACCESS BY ROWID, it achieves single-row retrieval in minimal I/O.

Index Range Scan

Used for non-unique indexes or equality predicates on non-unique columns, as well as range operators (BETWEEN, >, <). Retrieves multiple ROWIDs. Efficiency drops if >5–10% of rows are accessed due to random table I/O.

Index Full Scan vs. Fast Full Scan

Full Scan: Reads the index in sorted order. Used when all needed columns exist in the index and an ORDER BY matches the index key. Eliminates explicit sort operations.

Fast Full Scan: Reads index blocks in unsorted order using multi-block I/O. Can leverage parallel execution. Ideal for covering queries where sort order is irrelevant.

-- Fast Full Index Scan example
EXPLAIN PLAN FOR
SELECT invoice_number, invoice_date
FROM billing.invoices
WHERE status = 'ACTIVE';

---

Table Join Algorithms

Sort Merge Join (SMJ)

Both input datasets are sorted on the join key, then merged sequentially. Suitable for non-equality conditions (>, <=, BETWEEN) or large, unindexed tables. No driving/probed distinction exists. Performance improves significantly if data is pre-sorted via indexes.

Nested Loops (NL)

Iterates through the driving row source and probes the inner table for matches. Highly efficient when the driving set is small (<1000 rows) and the inner table has a selective index on the join column. Returns rows incrementally, enabling fast response times for interactive queries.

Hash Join (HJ)

Exclusive to equality joins (=). The optimizer selects a build table (typically smaller), constructs a hash table in the PGA, and probes it using the probe table's join column. Avoids sorting overhead and scales well for large datasets.

Hash Join Memory Modes

• Optimal: The entire hash table fits within the allocated PGA workarea (PGA_AGGREGATE_TARGET). Executes entirely in memory with zero disk spills. Maximum throughput.
• OnePass: The hash table exceeds memory capacity. Oracle partitions the hash table, spilling overflow buckets to disk. Each partition is read from disk exactly once during the probe phase. Acceptable performance degradation.
• MultiPass: Occurs when PGA is severely constrained relative to table size. Requires multiple disk read/write cycles per partition. Causes massive I/O bottlenecks. Should be eliminated by increasing PGA_AGGREGATE_TARGET or optimizing join cardinality.