Skip to content
Oracle DBA Scripts & Database Utilities

Query Execution Plan Formatter (qplan.sql)

What This Script Does

Section titled “What This Script Does”

This script formats and displays execution plans from PLAN_TABLE by:

  • Creating hierarchical display with proper indentation
  • Including cost and cardinality estimates
  • Showing object owners, names, and types
  • Displaying partition information when relevant
  • Supporting named statement IDs for plan organization

Script

Section titled “Script”
rem  qplan.sql
rem
accept query char prompt 'Query Name: '
rem
ttitle 'Query Plan'
rem
set lines 600
col plan format a255
rem
select
--parent_id,
level,
       lpad( ' ', 2 * ( level - 1 ), '| ' ) ||
       operation || ' ' ||
       options || ' ' ||
       object_owner || decode(object_owner,null, null,'.') ||
       object_name || ' ' ||
       object_type ||
       decode(optimizer, null, null, ' optimizer: ')|| optimizer||
       decode(cost, null, null, ' [cost=')||cost||
       decode(cardinality,
                     null, null,
                     decode(cost, null, ' [card=', ' card='))||cardinality||
       decode(cost, null, decode(cardinality, null, null, ']'), ']') ||
       decode(partition_start, null, null, ' Partition: Start='||
              partition_start||' Stop='||partition_stop) ||
       decode(other_tag, null, null, ' '||other_tag) plan
  -- , other
  from plan_table
 where statement_id = '&query'
connect by prior id = parent_id and prior statement_id = statement_id
 start with id =0
order by id;
rem
rollback;
undefine query

Usage

Section titled “Usage”
-- First, generate an explain plan
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'MY_QUERY' FOR
     SELECT * FROM employees WHERE department_id = 10;


-- Then run the script
SQL> @qplan.sql
Query Name: MY_QUERY

Required Privileges

Section titled “Required Privileges”
  • SELECT on PLAN_TABLE (must exist in your schema or be accessible)
  • EXPLAIN PLAN privilege

Sample Output

Section titled “Sample Output”
Query Plan


LEVEL PLAN
----- -------------------------------------------------------------------------------
    1 SELECT STATEMENT  [cost=4 card=5]
    2 | TABLE ACCESS BY INDEX ROWID EMPLOYEES TABLE [cost=4 card=5]
    3 | | INDEX RANGE SCAN HR.EMP_DEPARTMENT_IX INDEX [cost=1 card=5]


    1 SELECT STATEMENT  [cost=15 card=107]
    2 | HASH JOIN  [cost=15 card=107]
    3 | | TABLE ACCESS FULL HR.DEPARTMENTS TABLE [cost=3 card=27]
    4 | | TABLE ACCESS FULL HR.EMPLOYEES TABLE [cost=3 card=107]

Key Output Components

Section titled “Key Output Components”

Plan Structure

Section titled “Plan Structure”
  • Level: Hierarchy depth in the execution plan
  • Indentation: Visual hierarchy using | characters
  • Operation: Database operation type (SELECT, TABLE ACCESS, etc.)
  • Options: Operation modifiers (FULL, BY INDEX ROWID, etc.)

Object Information

Section titled “Object Information”
  • Object Owner: Schema owning the object
  • Object Name: Table, index, or view name
  • Object Type: TABLE, INDEX, VIEW, etc.

Cost Information

Section titled “Cost Information”
  • Cost: Optimizer’s estimated cost
  • Cardinality: Estimated number of rows returned
  • Optimizer: Cost-based optimizer method used

Partition Information

Section titled “Partition Information”
  • Partition Start/Stop: For partitioned objects

Understanding Execution Plans

Section titled “Understanding Execution Plans”

Common Operations

Section titled “Common Operations”
  • SELECT STATEMENT: Top-level operation
  • TABLE ACCESS: Methods of reading tables
    • FULL: Full table scan
    • BY INDEX ROWID: Index-based access
    • BY USER ROWID: Direct rowid access
  • INDEX: Index operations
    • RANGE SCAN: Reading index range
    • UNIQUE SCAN: Single index entry
    • FULL SCAN: Reading entire index
  • JOIN: Join operations
    • HASH JOIN: Hash-based join
    • NESTED LOOPS: Nested loop join
    • MERGE JOIN: Sort-merge join

Cost Analysis

Section titled “Cost Analysis”
  • Lower costs generally indicate more efficient operations
  • Compare alternative execution plans
  • Focus on highest-cost operations for tuning

Common Use Cases

Section titled “Common Use Cases”

  1. SQL Tuning

    • Analyze query execution paths
    • Compare before/after optimization
    • Identify expensive operations

  2. Index Analysis

    • Verify index usage
    • Find missing index opportunities
    • Validate index effectiveness

  3. Performance Troubleshooting

    • Understand optimizer decisions
    • Identify inefficient access paths
    • Plan query optimization strategies

Creating Multiple Plans

Section titled “Creating Multiple Plans”
-- Plan for different queries
EXPLAIN PLAN SET STATEMENT_ID = 'QUERY1' FOR
SELECT * FROM employees WHERE salary > 50000;


EXPLAIN PLAN SET STATEMENT_ID = 'QUERY2' FOR
SELECT * FROM employees WHERE department_id = 10;


-- View different plans
@qplan.sql
Query Name: QUERY1


@qplan.sql
Query Name: QUERY2

Plan Comparison

Section titled “Plan Comparison”
-- Compare plans side by side
SELECT statement_id, operation, options, object_name, cost
FROM plan_table
WHERE statement_id IN ('QUERY1', 'QUERY2')
ORDER BY statement_id, id;

Limitations

Section titled “Limitations”
  • Shows estimated costs, not actual execution statistics
  • Based on current statistics (may be outdated)
  • Doesn’t show runtime performance
  • Limited to explain plan functionality

Alternative Tools

Section titled “Alternative Tools”

For more advanced plan analysis:

  • DBMS_XPLAN: More detailed plan formatting
  • SQL Monitoring: Real-time execution statistics
  • AWR: Historical execution plans
  • SQL Trace: Detailed execution statistics
Section titled “Related Scripts”