Oracle Explain Plan Tree Report
This script displays a tree-style execution plan for a previously explained SQL statement. It reads data from the PLAN_TABLE and uses a hierarchical query to present the execution plan structure, helping developers analyze query execution flow and optimize SQL performance.
oraclesqlquery-optimizationv1.0.0
0 stars1 downloads16 views0 comments
By OracleDba • Created
Code
(35 lines)1234567891011121314151617181920212223242526272829303132333435
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/explain.sql
-- Author : Tim Hall
-- Description : Displays a tree-style execution plan of the specified statement after it has been explained.
-- Requirements : Access to the plan table.
-- Call Syntax : @explain (statement-id)
-- Last Modified: 15-JUL-2000
-- -----------------------------------------------------------------------------------
SET PAGESIZE 100
SET LINESIZE 200
SET VERIFY OFF
COLUMN plan FORMAT A50
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN bytes FORMAT 9999999999
COLUMN cost FORMAT 9999999
COLUMN partition_start FORMAT A20
COLUMN partition_stop FORMAT A20
SELECT LPAD(' ', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = '&1'
CONNECT BY PRIOR pt.id = pt.parent_id
AND pt.statement_id = '&1';