Execution Plan Display (xplan.sql)

What This Script Does

This script displays formatted execution plans from the PLAN_TABLE using Oracle’s DBMS_XPLAN package. It’s designed to show execution plans in a readable format after running EXPLAIN PLAN commands, helping with SQL performance analysis, optimization, and troubleshooting. The script forces serial plan display for backward compatibility and provides comprehensive plan information including costs, cardinalities, and access methods.

The Script

Rem
Rem $Header: utlxpls.sql 26-feb-2002.19:49:37 bdagevil Exp $
Rem
Rem utlxpls.sql
Rem
Rem Copyright (c) 1998, 2002, Oracle Corporation.  All rights reserved.
Rem
Rem    NAME
Rem      utlxpls.sql - UTiLity eXPLain Serial plans
Rem
Rem    DESCRIPTION
Rem      script utility to display the explain plan of the last explain plan
Rem   command. Do not display information related to Parallel Query
Rem
Rem    NOTES
Rem      Assume that the PLAN_TABLE table has been created. The script
Rem   utlxplan.sql should be used to create that table
Rem
Rem      With SQL*plus, it is recomended to set linesize and pagesize before
Rem      running this script. For example:
Rem      set linesize 100
Rem      set pagesize 0
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bdagevil    02/26/02 - cast arguments
Rem    bdagevil    01/23/02 - rewrite with new dbms_xplan package
Rem    bdagevil    04/05/01 - include CPU cost
Rem    bdagevil    02/27/01 - increase Name column
Rem    jihuang     06/14/00 - change order by to order siblings by.
Rem    jihuang     05/10/00 - include plan info for recursive SQL in LE row source
Rem    bdagevil    01/05/00 - add order-by to make it deterministic
Rem    kquinn      06/28/99 - 901272: Add missing semicolon
Rem    bdagevil    05/07/98 - Explain plan script for serial plans
Rem    bdagevil    05/07/98 - Created
Rem

set markup html preformat on

Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Force serial option for backward compatibility
Rem
select plan_table_output from table(dbms_xplan.display('plan_table','&stateID','serial'));

Key Features

Formatted Plan Display: Uses DBMS_XPLAN for professional formatting
Serial Plan Focus: Forces serial execution plan display
HTML Markup Support: Includes HTML preformatting for web display
Cost Information: Shows optimizer costs and cardinalities
Access Method Analysis: Displays table access and join methods
Comprehensive Plan Details: Includes all relevant plan information
Oracle Standard Tool: Based on Oracle’s official utility script

Usage

@xplan.sql

Prerequisites:

PLAN_TABLE must exist (create with utlxplan.sql)
EXPLAIN PLAN must have been executed previously
Recommended settings: SET LINESIZE 100 and SET PAGESIZE 0

Input Parameters:

stateID: Statement ID from EXPLAIN PLAN (use NULL for last plan)

Required Privileges

SELECT on PLAN_TABLE (user's own table)
EXECUTE on DBMS_XPLAN

Sample Output

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851659489

---------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    87 |     4   (0)|
|   1 |  NESTED LOOPS                |             |     1 |    87 |     4   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    68 |     2   (0)|
|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK|    1 |       |     1   (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    19 |     2   (0)|
|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     1   (0)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."EMPLOYEE_ID"=100)
   5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

Understanding Execution Plans

Plan Structure

Plan Tree Hierarchy

-- Plan reading principles:
-- Indentation shows operation hierarchy
-- Operations execute from innermost to outermost
-- Data flows from child to parent operations
-- Costs accumulate from bottom to top

Operation Types

-- Common operations:
-- TABLE ACCESS: Full table scan or index access
-- INDEX SCAN: Various index access methods
-- NESTED LOOPS: Join algorithm
-- HASH JOIN: Another join algorithm
-- SORT: Sorting operations

Cost Analysis

Cost Components

-- Cost interpretation:
-- Cost: Optimizer's estimate of resource usage
-- Rows: Estimated number of rows returned
-- Bytes: Estimated data volume
-- %CPU: Percentage of cost attributed to CPU

Cardinality Analysis

-- Row count analysis:
-- Accurate estimates indicate good statistics
-- Wildly incorrect estimates suggest stale statistics
-- Join cardinality explosion indicates poor design
-- Filter selectivity affects performance

Access Methods

Table Access Methods

-- Table access types:
-- FULL: Complete table scan
-- BY INDEX ROWID: Access via index then table
-- SAMPLE: Sample scan for statistics
-- CLUSTER: Cluster-based access

Index Access Methods

-- Index scan types:
-- UNIQUE SCAN: Single row via unique index
-- RANGE SCAN: Multiple rows via range
-- FULL SCAN: Complete index scan
-- FAST FULL SCAN: Parallel index scan
-- SKIP SCAN: Index skip scan

Common Use Cases

SQL Performance Analysis
- Analyze execution plans for slow queries
- Identify inefficient operations
- Support query optimization efforts
- Validate optimizer choices
Index Optimization
- Verify index usage in queries
- Identify missing index opportunities
- Analyze index scan efficiency
- Support index strategy development
Join Algorithm Analysis
- Review join method selection
- Analyze join order efficiency
- Identify join performance issues
- Support query rewriting efforts
Development Support
- Validate SQL design decisions
- Support code review processes
- Analyze application query patterns
- Plan performance testing

Advanced Analysis

Plan Optimization Assessment

Efficient Plan Characteristics

-- Good plan indicators:
-- Index access instead of full scans
-- Appropriate join algorithms
-- Reasonable cost estimates
-- Accurate cardinality estimates

Problem Plan Indicators

-- Plan problems:
-- Unnecessary full table scans
-- Cartesian joins
-- Excessive sorting operations
-- Inaccurate cardinality estimates

Cost Model Analysis

Cost-Based Optimization

-- Cost analysis factors:
-- I/O costs vs. CPU costs
-- Parallel execution costs
-- Network costs (for distributed queries)
-- Memory usage costs

Statistics Impact

-- Statistics influence on plans:
-- Table statistics affect access method selection
-- Column statistics affect join order
-- Histogram statistics affect selectivity
-- Index statistics affect scan method choice

Performance Prediction

Plan Efficiency Indicators

-- Performance prediction factors:
-- Total plan cost relative to data volume
-- Join algorithm appropriateness
-- Index utilization effectiveness
-- Sort and aggregation efficiency

Scalability Assessment

-- Scalability considerations:
-- Plan behavior with data growth
-- Join algorithm scaling characteristics
-- Index scan efficiency at scale
-- Memory requirement projections

Plan Optimization Techniques

Access Method Optimization

Table Access Optimization

-- Improve table access:
-- Add indexes for frequent predicates
-- Use partitioning for large tables
-- Consider materialized views
-- Optimize WHERE clause selectivity

Index Strategy Optimization

-- Index optimization:
-- Create composite indexes for multiple predicates
-- Use function-based indexes for expressions
-- Consider bitmap indexes for low cardinality
-- Maintain index statistics currency

Join Optimization

Join Algorithm Selection

-- Join algorithm optimization:
-- Nested loops: Good for small result sets
-- Hash joins: Good for large data sets
-- Sort-merge joins: Good for pre-sorted data
-- Hints can force specific algorithms

Join Order Optimization

-- Join order improvements:
-- Start with most selective tables
-- Use leading hints for join order control
-- Consider semi-joins for EXISTS conditions
-- Optimize star query join orders

Troubleshooting Applications

Plan Analysis Issues

Inaccurate Cost Estimates

-- Cost estimation problems:
-- Stale or missing statistics
-- Data skew not reflected in statistics
-- Complex predicate selectivity estimation
-- Multi-table join cardinality estimation

Suboptimal Plan Selection

-- Plan selection issues:
-- Optimizer parameter settings
-- Missing or outdated statistics
-- Inadequate system statistics
-- SQL statement complexity

Performance Problems

Identifying Bottlenecks

-- Performance bottleneck identification:
-- Most expensive operations in plan
-- Operations with high row counts
-- Full table scans on large tables
-- Excessive sort operations

Resolution Strategies

-- Performance improvement approaches:
-- Index creation or modification
-- Query rewriting
-- Optimizer hint usage
-- Statistics refresh

Integration with Performance Tools

SQL Tuning Workflow

Analysis Workflow

-- Tuning process integration:
-- 1. Generate execution plan
-- 2. Analyze plan for issues
-- 3. Implement optimization changes
-- 4. Compare new vs. old plans
-- 5. Validate performance improvement

Tool Integration

-- Integration with other tools:
-- SQL Tuning Advisor
-- SQL Access Advisor
-- SQL Monitor reports
-- Automatic SQL Tuning

Monitoring Integration

Plan Stability Monitoring

-- Plan change detection:
-- Compare plans over time
-- Identify plan regressions
-- Monitor optimizer behavior
-- Support SQL plan management

Performance Correlation

-- Performance correlation:
-- Plan costs vs. actual execution times
-- Resource consumption validation
-- Scalability assessment
-- Capacity planning support

Best Practices

Plan Analysis

Systematic Analysis Approach

-- Analysis methodology:
-- Start with highest cost operations
-- Verify cardinality estimates accuracy
-- Check index usage appropriateness
-- Analyze join algorithm selection

Documentation

-- Plan documentation:
-- Save plans for comparison
-- Document optimization changes
-- Track performance improvements
-- Maintain tuning history

Development Integration

Code Review Process

-- Development integration:
-- Include plan analysis in code reviews
-- Establish plan quality standards
-- Automate plan generation for testing
-- Monitor plan changes in deployments

Testing Procedures

-- Testing integration:
-- Generate plans for all SQL in testing
-- Compare plans across environments
-- Validate plan stability
-- Performance test plan efficiency

explain-sqlid.sql - Explain plan for SQL ID
qplan.sql - Query plan analysis
vsqlplanfind.sql - Find SQL plans
gvplanid.sql - Plan ID analysis

Setup Requirements

PLAN_TABLE Creation

Table Setup

-- Create PLAN_TABLE if not exists:
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

-- Or use CREATE TABLE statement:
CREATE TABLE plan_table AS
SELECT * FROM sys.plan_table$ WHERE 1=0;

Permissions

-- Ensure proper permissions:
-- User must own PLAN_TABLE or have access
-- DBMS_XPLAN package must be accessible
-- Appropriate system privileges required

Environment Setup

SQL*Plus Settings

-- Recommended settings:
SET LINESIZE 150
SET PAGESIZE 0
SET LONG 10000
SET MARKUP HTML PREFORMAT ON

Summary

This script is essential for:

SQL Performance Analysis - Comprehensive analysis of execution plans for performance optimization
Query Optimization - Understanding and improving SQL statement execution strategies
Index Strategy Development - Analyzing index usage and identifying optimization opportunities
Development Support - Supporting SQL development and code review processes
Performance Troubleshooting - Diagnosing and resolving SQL performance issues through plan analysis