Query Plan Display (qplano.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Script

rem  qplan.sql
rem
accept query char prompt 'Query Name: '
rem
ttitle 'Query Plan'
rem
set lines 132
col plan format a132
rem
select 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, ']'), ']') 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
set lines 80

What This Script Does

This script displays formatted execution plans from Oracle’s PLAN_TABLE, presenting query execution strategies in a hierarchical tree structure with cost and cardinality information. It’s designed to help DBAs and developers understand how Oracle plans to execute their SQL queries.

Usage

SQL> @qplano.sql
Query Name: MY_QUERY_PLAN

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT [cost=150 card=100]
  HASH JOIN [cost=150 card=100]
    TABLE ACCESS FULL SCOTT.EMP [cost=3 card=14]
    TABLE ACCESS BY INDEX ROWID SCOTT.DEPT [cost=2 card=100]
      INDEX RANGE SCAN SCOTT.DEPT_IDX [cost=1 card=100]

Prerequisites

1. PLAN_TABLE Must Exist

-- Create PLAN_TABLE if it doesn't exist
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

2. Generate Execution Plan First

-- Example: Generate a plan for your query
EXPLAIN PLAN
SET STATEMENT_ID = 'MY_QUERY_PLAN'
FOR
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Required Privileges

• SELECT privilege on PLAN_TABLE
• Ability to run EXPLAIN PLAN (typically granted through CONNECT role)

Parameters

• Query Name: The STATEMENT_ID used when running EXPLAIN PLAN (case-sensitive)

Key Features

1. Hierarchical Display

• Indentation shows parent-child relationships
• Each level indented by 2 spaces
• Visual representation of execution order

2. Operation Details

• OPERATION: The type of operation (e.g., TABLE ACCESS, INDEX SCAN)
• OPTIONS: Additional operation details (e.g., FULL, RANGE SCAN)
• OBJECT_OWNER.OBJECT_NAME: Schema and object being accessed
• OBJECT_TYPE: Type of object (TABLE, INDEX, etc.)

3. Cost Information

• Cost: Estimated resource usage (lower is better)
• Cardinality: Estimated number of rows returned
• Optimizer: Optimizer mode used (if specified)

Understanding Execution Plans

Reading the Hierarchy

SELECT STATEMENT                    -- Top level (executed last)
  NESTED LOOPS                      -- Join method
    TABLE ACCESS FULL EMP           -- First child (executed first)
    TABLE ACCESS BY INDEX ROWID DEPT -- Second child
      INDEX UNIQUE SCAN DEPT_PK     -- Grandchild (executed before parent)

Common Operations

1. Table Access Methods

   • TABLE ACCESS FULL: Full table scan
   • TABLE ACCESS BY INDEX ROWID: Row lookup using ROWID from index
   • TABLE ACCESS CLUSTER: Access clustered table

2. Index Operations

   • INDEX UNIQUE SCAN: Single row lookup
   • INDEX RANGE SCAN: Multiple rows via index
   • INDEX FULL SCAN: Read entire index
   • INDEX FAST FULL SCAN: Multiblock read of entire index

3. Join Methods

   • NESTED LOOPS: Best for small result sets
   • HASH JOIN: Best for large result sets with equi-joins
   • MERGE JOIN: Best for pre-sorted data
   • CARTESIAN: Usually indicates missing join condition

Performance Analysis Tips

1. Check for Full Table Scans

-- Look for TABLE ACCESS FULL on large tables
-- Consider adding indexes if selective predicates exist

2. Evaluate Join Order

-- Smaller tables should typically be accessed first
-- Check cardinality estimates vs. actual row counts

3. Cost vs. Actual Performance

-- Cost is an estimate - always test with actual data
-- Use SQL trace for real execution statistics

Common Use Cases

1. Query Tuning

-- Compare plans before and after adding indexes
EXPLAIN PLAN SET STATEMENT_ID = 'BEFORE_INDEX' FOR
SELECT * FROM large_table WHERE status = 'ACTIVE';

-- Create index
CREATE INDEX idx_status ON large_table(status);

EXPLAIN PLAN SET STATEMENT_ID = 'AFTER_INDEX' FOR
SELECT * FROM large_table WHERE status = 'ACTIVE';

-- Compare both plans
@qplano.sql  -- Run for BEFORE_INDEX
@qplano.sql  -- Run for AFTER_INDEX

2. Join Order Analysis

EXPLAIN PLAN SET STATEMENT_ID = 'JOIN_ANALYSIS' FOR
SELECT /*+ LEADING(small_table) */ *
FROM small_table s, large_table l
WHERE s.id = l.small_id;

@qplano.sql

3. Index Usage Verification

EXPLAIN PLAN SET STATEMENT_ID = 'INDEX_CHECK' FOR
SELECT * FROM employees
WHERE department_id = 50
AND salary > 5000;

@qplano.sql
-- Verify both conditions use indexes

Troubleshooting

Issue: “no rows selected”

Cause: Statement ID doesn’t exist in PLAN_TABLE

-- Check available statement IDs
SELECT DISTINCT statement_id
FROM plan_table
ORDER BY timestamp DESC;

Issue: Plan looks different than expected

Cause: Statistics may be stale

-- Gather fresh statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'EMP');

Issue: Missing cost/cardinality information

Cause: Using RULE-based optimizer or no statistics

-- Check optimizer mode
SHOW PARAMETER optimizer_mode

-- Ensure statistics exist
SELECT table_name, last_analyzed
FROM user_tables
WHERE table_name = 'YOUR_TABLE';

Best Practices

1. Use Meaningful Statement IDs

-- Include timestamp or description
EXPLAIN PLAN
SET STATEMENT_ID = 'CUSTOMER_REPORT_20240115'
FOR SELECT ...;

2. Clean Up PLAN_TABLE Regularly

-- Delete old execution plans
DELETE FROM plan_table
WHERE timestamp < SYSDATE - 7;
COMMIT;

3. Compare Multiple Plans

-- Use different statement IDs for variations
EXPLAIN PLAN SET STATEMENT_ID = 'WITH_HINT' FOR
SELECT /*+ INDEX(emp emp_idx) */ * FROM emp WHERE ...;

EXPLAIN PLAN SET STATEMENT_ID = 'WITHOUT_HINT' FOR
SELECT * FROM emp WHERE ...;

Advanced Usage

Viewing Specific Plan Steps

-- Query specific operations from a plan
SELECT operation, options, cost, cardinality
FROM plan_table
WHERE statement_id = 'MY_PLAN'
AND operation = 'TABLE ACCESS'
ORDER BY id;

Analyzing Parallel Execution

-- Look for parallel operations in OTHER column
SELECT id, operation, other
FROM plan_table
WHERE statement_id = 'PARALLEL_QUERY'
AND other LIKE '%PARALLEL%';

Related Scripts

• Explain Plan for SQL ID - Generate plans for cached SQL
• Query Plan - Alternative plan display format
• SQL Plan Find - Find plans in shared pool
• Extended Plan - Display plans with runtime statistics

Notes

• The script includes a ROLLBACK to ensure no transaction is left open
• Line size is temporarily set to 132 for better formatting
• The hierarchical query uses CONNECT BY to build the tree structure
• Statement IDs are case-sensitive when querying PLAN_TABLE