Session Execution Plan Viewer (vplan.sql)

What This Script Does

This script displays execution plans for SQL statements currently being executed by a specific session by:

Joining V$SQL_PLAN with V$SESSION to get current session’s SQL
Showing hierarchical execution plan with proper indentation
Including I/O cost estimates and partition information
Displaying access predicates for detailed analysis

Script

rem  vplan.sql
rem
rem
ttitle 'Query Plan'
rem
set lines 132
ttitle off

clear columns
column child_number format 999999 head "Child#"
-- column op format a68 wrap head "Operation"
column op format a100 wrap head "Operation"
clear breaks
break on child_number nodup

select child_number,
       io_cost,
       lpad(' ',2 * (depth),'| ') || operation
       || decode(options,
                        null, null, ' (' || initcap(options) || ')')
       || ' ' || object_owner
       ||decode(object_owner, null, null, '.')
       || object_name
       || decode(partition_start, null, null, ' Partition: Start='
         ||partition_start||' Stop='||partition_stop)
       || decode(access_predicates, null, null, '('||access_predicates||')') op
  from v$sql_plan
 where ( address, hash_value ) in (
       select sql_address, sql_hash_value
         from v$session
        where sid = &sid )
 order by child_number, id;
ttitle off

Usage

SQL> @vplan.sql
Enter value for sid: 156

Parameters

sid: Session ID to analyze (from V$SESSION)

Required Privileges

SELECT on V$SQL_PLAN
SELECT on V$SESSION

Sample Output

Query Plan

 Child#   IO_COST Operation
------- --------- --------------------------------------------------------------------------------
      0        45 SELECT STATEMENT
      0        45 | SORT ORDER BY
      0        44 | | NESTED LOOPS
      0         3 | | | TABLE ACCESS (Full) HR.DEPARTMENTS
      0        41 | | | TABLE ACCESS (By Index Rowid) HR.EMPLOYEES Partition: Start=1 Stop=4
      0         1 | | | | INDEX (Range Scan) HR.EMP_DEPARTMENT_IX(DEPARTMENT_ID=:1)

      1        52 SELECT STATEMENT
      1        52 | HASH JOIN
      1         3 | | TABLE ACCESS (Full) HR.DEPARTMENTS
      1        49 | | TABLE ACCESS (Full) HR.EMPLOYEES

Key Output Columns

Child#: Child cursor number (different execution plans)
IO_COST: Estimated I/O cost for the operation
Operation: Hierarchical execution plan showing:
- Operation type with indentation
- Access method in parentheses
- Object owner and name
- Partition information if applicable
- Access predicates for filter conditions

Understanding Execution Plans

Operation Hierarchy

Indentation shows parent-child relationships
Operations execute from inside-out (deepest first)
Results flow up through the hierarchy

Common Operations

TABLE ACCESS: Reading table data
- (Full): Full table scan
- (By Index Rowid): Index-based access
INDEX: Index operations
- (Range Scan): Reading index range
- (Unique Scan): Single index entry
JOIN: Join operations
- NESTED LOOPS: Nested loop join
- HASH JOIN: Hash-based join
- MERGE JOIN: Sort-merge join

Cost Analysis

Higher costs indicate more expensive operations
Compare costs between child cursors
Focus tuning efforts on highest-cost operations

Common Use Cases

Real-time Performance Analysis
- Analyze currently executing SQL
- Identify expensive operations in progress
- Monitor long-running queries
Session Troubleshooting
- Understand what a session is doing
- Diagnose hanging or slow sessions
- Validate expected execution paths
Query Optimization
- Compare execution plans
- Verify index usage
- Identify optimization opportunities

Finding Active Sessions

To identify sessions to analyze:

-- Find active sessions
SELECT sid, username, status, sql_id,
       seconds_in_wait, event
FROM v$session
WHERE status = 'ACTIVE'
AND username IS NOT NULL
ORDER BY seconds_in_wait DESC;

-- Find sessions with high CPU/I/O
SELECT s.sid, s.username, st.value cpu_used
FROM v$session s, v$sesstat st, v$statname sn
WHERE s.sid = st.sid
AND st.statistic# = sn.statistic#
AND sn.name = 'CPU used by this session'
AND st.value > 1000
ORDER BY st.value DESC;

Multiple Child Cursors

When multiple child cursors exist:

Different execution plans for same SQL
May indicate bind variable peeking issues
Compare costs and operations between children

Limitations

Only shows plans for currently active SQL
Session must be currently executing SQL
Historical plans require other methods (AWR, ASH)

Alternative Plan Sources

For more comprehensive analysis:

DBA_HIST_SQL_PLAN: Historical plans from AWR
V$SQL_PLAN_MONITOR: Real-time SQL monitoring
DBMS_XPLAN: More detailed plan formatting