Explain Plan

SQL> @ORACLE_HOME\rdbms\admin\utlxplan.sql

This creates the PLAN_TABLE for the user executing the script.

Run EXPLAIN PLAN for the query to be optimized:

1)explain plan for
select * from accounts;

2)select * from PLAN_TABLE; ---to check output


3)explain plan
SET STATEMENT_ID = 'ACC' FOR
select * from accounts;



4)

SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||
options||' '||object_name "Plan",cost "Cost by CBO",bytes/1024/1024 "MB",time "Time By CBO"
FROM PLAN_TABLE

CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'ACC'
ORDER BY id;



5)
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';

The following diagram demonstrates the procedures for running TRACE versus EXPLAIN PLAN:

TRACE It takes four hours to TRACE a query that takes four hours to run. Set up Init.ora Parameters Create PLAN_TABLE table Run Query Statement is executed PLAN_TABLE is populated Run TKPROF Output shows disk and memory reads in addition to EXPLAIN PLAN output

EXPLAIN PLAN It takes less than a minute to EXPLAIN PLAN a query that takes four hours to run. Create PLAN_TABLE table Explain Query PLAN_TABLE is populated Query PLAN_TABLE Output shows EXPLAIN PLAN