Execution Plan Viewer (vexp.sql)

What This Script Does

This script provides a hierarchical view of SQL execution plans by:

Displaying plans directly from V$SQL_PLAN
Using indentation to show operation hierarchy
Including I/O cost estimates for each operation
Supporting multiple child cursors
Formatting operations with options and object names

Script

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_name op
  from v$sql_plan
 where hash_value = &hash_value
 order by child_number, id;

Usage

SQL> @vexp.sql
Enter value for hash_value: 1234567890

Parameters

hash_value: The hash value of the SQL statement (from V$SQL or V$SQLAREA)

Required Privileges

SELECT on V$SQL_PLAN

Sample Output

 Child#   IO_COST Operation
------- --------- --------------------------------------------------------------------------------
      0        45 SELECT STATEMENT
      0        45 | SORT ORDER BY
      0        44 | | NESTED LOOPS
      0         3 | | | TABLE ACCESS (Full) DEPARTMENTS
      0        41 | | | TABLE ACCESS (By Index Rowid) EMPLOYEES
      0         1 | | | | INDEX (Range Scan) EMP_DEPARTMENT_IX

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

Key Output Columns

Child#: Child cursor number (different execution plans for same SQL)
IO_COST: Estimated I/O cost for the operation
Operation: Hierarchical display of execution steps with:
- Indentation showing operation hierarchy
- Operation type (SELECT, TABLE ACCESS, INDEX, etc.)
- Access method in parentheses (Full, Range Scan, etc.)
- Object name being accessed

Understanding Execution Plans

Common Operations

SELECT STATEMENT: Top-level operation
TABLE ACCESS: Reading from tables
- (Full): Full table scan
- (By Index Rowid): Index-based access
- (Sample): Sampling operation
INDEX: Index operations
- (Range Scan): Reading index range
- (Unique Scan): Single index entry
- (Full Scan): Reading entire index
SORT: Sorting operations
- (Order By): ORDER BY clause
- (Group By): GROUP BY clause
- (Unique): DISTINCT operation
HASH JOIN: Hash-based joins
NESTED LOOPS: Nested loop joins
MERGE JOIN: Sort-merge joins

Cost Analysis

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

Common Use Cases

Performance Tuning
- Analyze execution plan efficiency
- Identify expensive operations
- Compare different execution paths
SQL Optimization
- Verify index usage
- Check join methods
- Validate optimizer choices
Troubleshooting
- Understand SQL behavior
- Identify performance bottlenecks
- Validate query changes

Finding Hash Values

To find hash values for analysis:

-- From currently executing SQL
SELECT sql_id, hash_value, sql_text
FROM v$sql
WHERE sql_text LIKE '%your_table%'
AND sql_text NOT LIKE '%v$sql%';

-- From SQL area
SELECT hash_value, executions, buffer_gets, sql_text
FROM v$sqlarea
WHERE sql_text LIKE '%your_query%'
ORDER BY buffer_gets DESC;

Comparing with Other Plan Sources

This script shows plans from V$SQL_PLAN (currently cached). Compare with:

EXPLAIN PLAN: What optimizer estimates
DBMS_XPLAN: More detailed formatting
AWR: Historical execution plans

Advanced Analysis

For more detailed plan analysis:

-- Include additional plan details
SELECT child_number, id, operation, options,
       object_name, cardinality, bytes, cost,
       cpu_cost, io_cost
FROM v$sql_plan
WHERE hash_value = &hash_value
ORDER BY child_number, id;