V$SQL_PLAN - View Cached Execution Plans & Detect Plan Regression

V$SQL_PLAN

Overview

V$SQL_PLAN stores the execution plan rows for every SQL cursor currently held in the shared pool. Each row represents one step (operation) in the plan tree, identified by the combination of SQL_ID, CHILD_NUMBER, and PLAN_HASH_VALUE. DBAs use this view to inspect the plan Oracle is actually using for a live cursor — without needing to run EXPLAIN PLAN, which shows what Oracle would do, not what it is doing. It is the in-memory source for DBMS_XPLAN.DISPLAY_CURSOR and is frequently queried to detect full table scans, missing indexes, or suboptimal join methods on production workloads.

View Type: Dynamic Performance View Available Since: Oracle 9i Required Privileges: SELECT on V_$SQL_PLAN or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
SQL_ID	VARCHAR2(13)	SQL identifier; links to V$SQL.SQL_ID
CHILD_NUMBER	NUMBER	Child cursor number; links to V$SQL.CHILD_NUMBER
PLAN_HASH_VALUE	NUMBER	Numeric hash of the entire plan; changes when any step in the plan changes
ID	NUMBER	Step number within the plan; root is always 0
PARENT_ID	NUMBER	ID of the parent step; used to reconstruct the tree hierarchy
DEPTH	NUMBER	Nesting depth in the plan tree; used for indentation in DBMS_XPLAN output
POSITION	NUMBER	Execution order among siblings under the same parent
OPERATION	VARCHAR2(30)	Row source operation type (e.g., TABLE ACCESS, INDEX, HASH JOIN, SORT)
OPTIONS	VARCHAR2(255)	Qualifier for the operation (e.g., FULL, BY INDEX ROWID, RANGE SCAN, UNIQUE SCAN)
OBJECT_OWNER	VARCHAR2(128)	Schema owning the object accessed by this step
OBJECT_NAME	VARCHAR2(128)	Name of the table, index, or other object accessed
OBJECT_TYPE	VARCHAR2(20)	Object type (TABLE, INDEX, etc.)
OBJECT#	NUMBER	Dictionary object number; joins to DBA_OBJECTS
COST	NUMBER	Optimizer-estimated cost for this step
CARDINALITY	NUMBER	Optimizer-estimated row count output from this step
BYTES	NUMBER	Optimizer-estimated bytes output from this step
CPU_COST	NUMBER	Optimizer-estimated CPU cost
IO_COST	NUMBER	Optimizer-estimated I/O cost
TEMP_SPACE	NUMBER	Optimizer-estimated temporary space (bytes) for sort or hash operations
ACCESS_PREDICATES	VARCHAR2(4000)	Predicates used to access the object (index key conditions)
FILTER_PREDICATES	VARCHAR2(4000)	Predicates applied to filter rows after access
PROJECTION	VARCHAR2(4000)	Columns returned by this step
TIME	NUMBER	Optimizer-estimated wall clock time in seconds
QBLOCK_NAME	VARCHAR2(128)	Query block name (useful for hints and outline equivalence)
REMARKS	VARCHAR2(4000)	Additional optimizer notes (e.g., dynamic sampling used, statistics missing)
OTHER_TAG	VARCHAR2(255)	Parallel query coordination tag (e.g., PARALLEL_COMBINED_WITH_PARENT)
PARTITION_START	VARCHAR2(255)	First partition accessed for partition pruning
PARTITION_STOP	VARCHAR2(255)	Last partition accessed for partition pruning

Essential Queries

Basic Usage

Retrieve the formatted execution plan for a known SQL_ID using DBMS_XPLAN — the fastest way to read a live plan:

-- Most common approach: uses V$SQL_PLAN internally
SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id      => '&sql_id',
        cursor_child_no => NULL,    -- NULL = all child cursors
        format      => 'ALLSTATS LAST'
    )
);

For direct access to the raw plan rows (useful in scripts or tooling that needs to parse plan data programmatically):

SELECT
    p.sql_id,
    p.child_number,
    p.plan_hash_value,
    p.id,
    p.parent_id,
    LPAD(' ', 2 * p.depth) || p.operation       AS operation,
    p.options,
    p.object_owner,
    p.object_name,
    p.cost,
    p.cardinality                                AS est_rows,
    p.bytes                                      AS est_bytes,
    p.access_predicates,
    p.filter_predicates
FROM
    v$sql_plan p
WHERE
    p.sql_id        = '&sql_id'
    AND p.child_number = 0
ORDER BY
    p.id;

Monitoring Query

Find all SQL in the shared pool using full table scans on tables larger than 1000 blocks (a practical threshold to avoid noise from small lookup tables):

SELECT DISTINCT
    p.sql_id,
    p.child_number,
    p.plan_hash_value,
    p.object_owner,
    p.object_name,
    s.num_rows                                   AS table_rows,
    t.blocks                                     AS table_blocks,
    sq.executions,
    ROUND(sq.elapsed_time / NULLIF(sq.executions, 0) / 1e6, 4)
                                                 AS avg_elapsed_secs,
    sq.buffer_gets,
    ROUND(sq.buffer_gets / NULLIF(sq.executions, 0), 0)
                                                 AS gets_per_exec,
    SUBSTR(sq.sql_text, 1, 120)                  AS sql_text
FROM
    v$sql_plan  p
    JOIN v$sql      sq ON sq.sql_id        = p.sql_id
                       AND sq.child_number  = p.child_number
    JOIN dba_tables t  ON t.owner          = p.object_owner
                       AND t.table_name    = p.object_name
    JOIN dba_tables s  ON s.owner          = t.owner
                       AND s.table_name    = t.table_name
WHERE
    p.operation = 'TABLE ACCESS'
    AND p.options  = 'FULL'
    AND t.blocks   > 1000
ORDER BY
    sq.buffer_gets DESC;

Combined with Other Views

Compare the plan hash values active in V$SESSION right now against the historically best-performing plan hash from AWR (DBA_HIST_SQLSTAT), surfacing potential plan regressions:

SELECT
    curr.sql_id,
    curr.plan_hash_value                         AS current_plan_hash,
    best.plan_hash_value                         AS best_hist_plan_hash,
    CASE
        WHEN curr.plan_hash_value != best.plan_hash_value THEN 'PLAN REGRESSION'
        ELSE 'OK'
    END                                          AS plan_status,
    curr.executions                              AS current_execs,
    ROUND(curr.elapsed_time / NULLIF(curr.executions, 0) / 1e6, 4)
                                                 AS current_avg_elapsed_secs,
    ROUND(best.min_avg_elapsed_us / 1e6, 4)      AS best_avg_elapsed_secs
FROM
    v$sql curr
    JOIN (
        SELECT
            sql_id,
            plan_hash_value,
            MIN(elapsed_time_total / NULLIF(executions_total, 0)) AS min_avg_elapsed_us
        FROM
            dba_hist_sqlstat
        WHERE
            executions_total > 10
        GROUP BY
            sql_id, plan_hash_value
    ) best ON best.sql_id = curr.sql_id
WHERE
    curr.executions > 0
    AND curr.plan_hash_value != best.plan_hash_value
ORDER BY
    curr.elapsed_time DESC;

Advanced Analysis: Index Usage Across Cached Plans

Report all indexes referenced by plans currently in the shared pool — useful before dropping an index to confirm it is not actively used:

SELECT
    p.object_owner,
    p.object_name                                AS index_name,
    p.object_type,
    p.operation,
    p.options                                    AS scan_type,
    COUNT(DISTINCT p.sql_id)                     AS distinct_sql_count,
    SUM(sq.executions)                           AS total_executions,
    ROUND(SUM(sq.elapsed_time) / 1e6, 2)         AS total_elapsed_secs,
    MIN(p.sql_id)                                AS sample_sql_id
FROM
    v$sql_plan  p
    JOIN v$sql      sq ON sq.sql_id       = p.sql_id
                       AND sq.child_number = p.child_number
WHERE
    p.object_type LIKE '%INDEX%'
    AND p.object_owner NOT IN ('SYS', 'SYSTEM')
GROUP BY
    p.object_owner,
    p.object_name,
    p.object_type,
    p.operation,
    p.options
ORDER BY
    total_executions DESC;

Partition Pruning Verification

Confirm that partition pruning is occurring for a specific partitioned table — critical for partition strategy validation:

SELECT
    p.sql_id,
    p.child_number,
    p.id,
    LPAD(' ', 2 * p.depth) || p.operation       AS operation,
    p.options,
    p.object_owner,
    p.object_name,
    p.partition_start,
    p.partition_stop,
    p.cardinality                                AS est_rows,
    p.access_predicates,
    p.filter_predicates
FROM
    v$sql_plan p
WHERE
    p.object_name = UPPER('&table_name')
    AND p.object_owner = UPPER('&schema_name')
    AND p.operation IN ('TABLE ACCESS', 'INDEX')
ORDER BY
    p.sql_id,
    p.child_number,
    p.id;

Common Use Cases

• Live plan inspection — View the plan Oracle is actually executing for a running statement, not the theoretical EXPLAIN PLAN which can differ from the cached cursor
• Full table scan audit — Regularly query for large-table full scans that have appeared in the shared pool since the last check, focusing new index creation efforts
• Pre-drop index validation — Before dropping an index, confirm no live cursors are using it to avoid unexpected plan changes and regressions in production
• Partition pruning confirmation — Verify that queries on partitioned tables are pruning to the expected partition range rather than scanning all partitions
• Plan regression detection — Compare the PLAN_HASH_VALUE of the current cursor against historical AWR data to detect optimizer regressions after statistics gathers, upgrades, or parameter changes
• Join method analysis — Find all SQL in the shared pool using NESTED LOOPS on large tables or CARTESIAN joins, which often indicate missing join conditions or incorrect statistics

Related Views

• V$SQL — Parent view containing cursor statistics; join on SQL_ID + CHILD_NUMBER to correlate plans with runtime metrics
• V$SESSION — Join on SQL_ID to see which sessions are currently executing a given plan
• V$SYSSTAT — System-level statistics (physical reads, sorts) that reflect the aggregate cost of all plans running on the system
• V$SQL_PLAN_STATISTICS — Per-step actual row counts and elapsed times for cursors with STATISTICS_LEVEL = ALL or the GATHER_PLAN_STATISTICS hint applied
• V$SQL_PLAN_STATISTICS_ALL — Combines estimated (from V$SQL_PLAN) and actual (from V$SQL_PLAN_STATISTICS) statistics side by side
• DBA_HIST_SQL_PLAN — AWR snapshot of execution plans; survives shared pool aging and instance restarts; used for historical plan comparison

Version Notes

• Oracle 9i: V$SQL_PLAN introduced; previously execution plans could only be retrieved via EXPLAIN PLAN (showing future plans, not cached ones)
• Oracle 10g: DBMS_XPLAN.DISPLAY_CURSOR added as the standard tool for formatting V$SQL_PLAN output; projection and qblock_name columns added
• Oracle 11g: Adaptive cursor sharing can create multiple child cursors with different PLAN_HASH_VALUEs for the same SQL_ID; SQL Plan Management (SPM) baselines can control which plan is used
• Oracle 12c: Adaptive plans introduced — a single execution may switch between alternatives (e.g., nested loop vs. hash join) at runtime; the final plan stabilises after the first execution and is reflected in V$SQL_PLAN
• Oracle 19c: Automatic SQL Plan Management can automatically accept better plans from SQL Tuning Advisor runs; REMARKS column may show “automatic capture” notes
• Oracle 21c / 23ai: SQL Analysis Report enhancements; Real-Time SQL Monitoring captures V$SQL_PLAN detail automatically for long-running statements; automatic indexing (23ai) can change which indexes appear in cached plans