Execution Plan Display by Hash Value (vplanhash.sql)

What This Script Does

This script displays formatted execution plans for SQL statements by querying V$SQL_PLAN using a specific hash value. It presents the execution plan in a hierarchical tree format with proper indentation, showing the sequence of operations, access methods, join techniques, and associated database objects. The script includes I/O cost information and handles multiple child cursors, making it essential for SQL performance analysis and optimization.

The Script

rem  vplanhash.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 ||'.'|| object_name op
  from v$sql_plan
 where hash_value = &hash_value
 order by child_number, id;
ttitle off

Key Features

Hash Value Based Lookup: Retrieves plans using SQL hash values
Hierarchical Display: Shows plan operations with proper tree indentation
Multiple Child Support: Handles multiple child cursors for the same SQL
Cost Information: Displays I/O cost estimates for operations
Object Details: Shows owner and object names for accessed objects
Operation Details: Includes operation types and options
Formatted Output: Clean, readable plan presentation

Usage

@vplanhash.sql

Input Parameters:

hash_value: The hash value of the SQL statement to display

Required Privileges

SELECT on V$SQL_PLAN

Sample Output

                                                Query Plan

Child#   IO_COST Operation
------- -------- ----------------------------------------------------------------------------------------------------
      0       45 SELECT STATEMENT
                 | HASH JOIN
                 | | TABLE ACCESS (Full) HR.EMPLOYEES
                 | | HASH JOIN
                 | | | INDEX (Range Scan) HR.DEPT_IDX
                 | | | TABLE ACCESS (By Index Rowid) HR.DEPARTMENTS
                 | | | TABLE ACCESS (Full) HR.LOCATIONS
                 | SORT (Order By)

      1       52 SELECT STATEMENT
                 | NESTED LOOPS
                 | | TABLE ACCESS (Full) HR.EMPLOYEES
                 | | TABLE ACCESS (By Index Rowid) HR.DEPARTMENTS
                 | | | INDEX (Unique Scan) HR.DEPT_PK
                 | SORT (Order By)

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 plan operations including:
- Operation type (SELECT STATEMENT, TABLE ACCESS, etc.)
- Access method options (Full, By Index Rowid, etc.)
- Object owner and name
- Proper indentation showing operation hierarchy

Understanding Execution Plans

Plan Reading Fundamentals

Reading Direction

-- Execution plan reading:
-- Read from most indented to least indented
-- Operations execute from bottom to top
-- Inner operations complete before outer operations
-- Data flows from inner to outer operations

Operation Types

-- Common operations:
-- SELECT STATEMENT: Top-level operation
-- TABLE ACCESS: Data retrieval methods
-- INDEX: Index access operations
-- HASH JOIN: Hash-based join operations
-- NESTED LOOPS: Nested loop joins
-- SORT: Sorting operations

Access Methods

Table Access Methods

-- TABLE ACCESS (Full): Full table scan
-- TABLE ACCESS (By Index Rowid): Index-guided access
-- TABLE ACCESS (By User Rowid): Direct rowid access
-- TABLE ACCESS (Sample): Sampling access

Index Access Methods

-- INDEX (Unique Scan): Single row via unique index
-- INDEX (Range Scan): Multiple rows via range
-- INDEX (Full Scan): All index entries
-- INDEX (Fast Full Scan): Parallel index scan
-- INDEX (Skip Scan): Composite index skip scan

Join Operations

Join Types

-- NESTED LOOPS: Row-by-row processing
-- HASH JOIN: Hash table-based joining
-- SORT MERGE JOIN: Sort-merge algorithm
-- CARTESIAN JOIN: Cross product

Join Considerations

-- Join efficiency factors:
-- Data volume and selectivity
-- Available indexes
-- Memory allocation
-- Optimizer statistics quality

Common Use Cases

SQL Performance Analysis
- Analyze execution plan efficiency
- Identify performance bottlenecks
- Compare different execution approaches
- Validate optimizer decisions
Query Optimization
- Identify inefficient operations
- Plan index strategies
- Optimize join orders
- Improve access paths
Plan Stability Investigation
- Compare multiple child cursors
- Investigate plan changes
- Analyze adaptive cursor sharing
- Troubleshoot plan regressions
Performance Troubleshooting
- Diagnose slow query performance
- Identify resource-intensive operations
- Analyze cost estimation accuracy
- Support tuning initiatives

Advanced Analysis

Child Cursor Analysis

Multiple Plans

-- Multiple child cursors indicate:
-- Different bind variable values
-- Adaptive cursor sharing
-- Plan instability
-- Environment differences

Plan Comparison

-- Compare child cursors:
-- Operation differences
-- Cost variations
-- Access method changes
-- Join order variations

Cost Analysis

I/O Cost Interpretation

-- I/O cost represents:
-- Estimated logical reads
-- Relative operation expense
-- Optimizer's cost calculation
-- Basis for plan selection

Cost Distribution

-- Analyze cost patterns:
-- High-cost operations identification
-- Cost distribution across operations
-- Bottleneck operation detection
-- Optimization priority setting

Optimization Strategies

High-Cost Operations

Full Table Scans

-- Optimize full table scans:
-- Add appropriate indexes
-- Improve WHERE clause selectivity
-- Consider partitioning
-- Validate statistics currency

Inefficient Joins

-- Optimize join operations:
-- Ensure join condition indexes
-- Adjust join order through hints
-- Consider materialized views
-- Optimize driving table selection

Index Optimization

Missing Indexes

-- Identify missing indexes:
-- Frequent full table scans
-- High-cost table access operations
-- Filter predicate analysis
-- Join column indexing needs

Index Usage

-- Validate index effectiveness:
-- Range scan vs. full scan
-- Index selectivity
-- Composite index utilization
-- Function-based index needs

Plan Stability Analysis

Plan Changes

Change Detection

-- Detect plan changes:
-- Compare historical plans
-- Identify operation differences
-- Analyze cost variations
-- Track performance impact

Stability Factors

-- Plan stability influences:
-- Statistics freshness
-- Data volume changes
-- System parameter modifications
-- Oracle version differences

Bind Variable Impact

Bind Peeking

-- Bind variable considerations:
-- Initial bind value influence
-- Skewed data distribution
-- Adaptive cursor sharing
-- Plan baseline usage

Integration with Other Tools

SQL Tuning Advisor

-- Use with SQL Tuning Advisor:
-- Automated recommendation generation
-- Index suggestion validation
-- SQL profile consideration
-- Alternative plan evaluation

AWR and ASH

-- Correlate with performance data:
-- Execution statistics
-- Wait event analysis
-- Resource consumption
-- Historical trend analysis

SQL Plan Baselines

-- Plan baseline management:
-- Capture effective plans
-- Prevent plan regressions
-- Control plan evolution
-- Ensure plan stability

Finding Hash Values

From V$SQL

-- Find hash values:
SELECT sql_id, hash_value, sql_text
FROM v$sql
WHERE sql_text LIKE '%your_table_name%'
  AND sql_text NOT LIKE '%v$sql%';

From AWR

-- Historical hash values:
SELECT sql_id, plan_hash_value, sql_text
FROM dba_hist_sqltext
WHERE sql_text LIKE '%your_query%';

From Current Session

-- Current session SQL:
SELECT prev_hash_value
FROM v$session
WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

Plan Documentation

Performance Baselines

-- Document baseline plans:
-- Capture optimal execution plans
-- Record performance metrics
-- Establish comparison standards
-- Track plan evolution

Optimization History

-- Maintain optimization records:
-- Before/after plan comparisons
-- Performance improvement metrics
-- Optimization techniques applied
-- Lessons learned documentation

Best Practices

Plan Analysis

-- Effective plan analysis:
-- Focus on high-cost operations
-- Validate access method efficiency
-- Check join order appropriateness
-- Verify index utilization

Performance Validation

-- Validate plan effectiveness:
-- Compare estimated vs. actual costs
-- Monitor execution statistics
-- Track response time improvements
-- Measure resource utilization

Plan Management

-- Manage execution plans:
-- Use SQL plan baselines for stability
-- Monitor plan changes
-- Document optimization decisions
-- Maintain plan libraries

gvplanid.sql - Plan analysis by SQL_ID
vsqlplanfind.sql - Find execution plans
explain-sqlid.sql - Explain plan by SQL_ID
qplan.sql - Quick plan display

Troubleshooting

Common Issues

-- Script troubleshooting:
-- Verify hash value exists in V$SQL_PLAN
-- Check for aged-out plans
-- Validate user privileges
-- Confirm plan cache retention

Plan Not Found

-- When no plan is found:
-- Check V$SQL for hash value existence
-- Verify SQL is currently cached
-- Look in AWR for historical plans
-- Consider using SQL_ID instead

Multiple Results

-- Handle multiple child cursors:
-- Compare different plans
-- Identify optimal plan
-- Investigate plan variations
-- Consider SQL plan baseline

Advanced Modifications

Enhanced Cost Display

-- Add more cost details:
SELECT
  child_number,
  id,
  cost,
  cardinality,
  bytes,
  lpad(' ',2 * depth,'| ') || operation ||
  decode(options, null, null, ' (' || options || ')') ||
  ' ' || object_owner ||'.'|| object_name as operation
FROM v$sql_plan
WHERE hash_value = &hash_value
ORDER BY child_number, id;

Predicate Information

-- Include predicates:
SELECT
  child_number,
  lpad(' ',2 * depth,'| ') || operation operation,
  access_predicates,
  filter_predicates
FROM v$sql_plan
WHERE hash_value = &hash_value
ORDER BY child_number, id;

Summary

This script is essential for:

Plan Analysis - Understanding SQL execution strategies and performance characteristics
Performance Optimization - Identifying bottlenecks and optimization opportunities
Plan Comparison - Analyzing different execution approaches for the same SQL
Troubleshooting - Diagnosing query performance issues and plan problems
Optimization Validation - Verifying the effectiveness of tuning efforts