Skip to content

Explain Plan Display (ep.sql)

What This Script Does

Displays the execution plan from the PLAN_TABLE using Oracle’s DBMS_XPLAN.DISPLAY() function with formatted output.

The Script

rem ep.sql
rem
set pagesize 140
set linesize 280
rem
ttitle 'Explain Plan'
rem

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Usage

-- Basic usage
@ep.sql

Parameters

The script prompts for:

None - this script requires no parameters## Required Privileges

SELECT ANY DICTIONARY
-- OR --
```## Sample Output

                                              Explain Plan

Plan hash value: 1445457117

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 10 | 690 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMPLOYEES| 10 | 690 | 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(“DEPARTMENT_ID”=10)

## Key Output Sections

- **Plan Hash Value**: Unique identifier for this execution plan
- **Operations**: The steps Oracle will take to execute the query
- **Rows**: Estimated number of rows processed at each step
- **Bytes**: Estimated bytes processed
- **Cost**: Optimizer cost estimate
- **Time**: Estimated execution time
- **Predicate Information**: Filter and access conditions

## Common Use Cases

**Query Tuning**
```sql
-- Analyze execution plans to optimize SQL performance
@ep.sql
-- Enter: [appropriate values]

Index Analysis

-- Verify that indexes are being used correctly
@ep.sql

sqltext - Display SQL text by SQL_ID
dindex - Analyze index statistics
dtable - Analyze table statistics