I have provided below a short script that I keep in my SQL Plus directory, named Explain.SQL. Any time I want to view the explain plan sequence for some difficult SQL select, I do the following in a SQL Plus session:
Type EXPLAIN PLAN FOR and press Enter.
Paste all the lines of the select statement from my PFE edit session.
Execute the statement -- (just press enter)
The Explain.SQL script below does all the rest. Now I just wish I could find an explanation somewhere that would tell me exactly what the Explain Plan output means, step by step.
------ Begin EXPLAIN.SQL script -----
set echo off
set feedback off
-- This select interprets the output of an
-- EXPLAIN PLAN FOR Select.... statement.
-- In order to run Explain Plan, the table Plan_Table must exist in
-- your schema. If it does not, then run (from SQL Plus) the sql
-- script C:\ORAWIN\RDBMS71\ADMIN\utlxplan.sql to create it.
-- Each time you run Explain Plan, you should either run this script
-- OR be sure to delete everything in Plan_Table. All you need is to
-- enter is: delete plan_table;
-- Note that this script does this at the end.
-- If you don't clear out plan_table, then this script loops. You
-- can stop the looping by pressing Ctrl-C.
update plan_table set statement_id='A' where statement_id is null;
Select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name
||' '||decode(id,0,'Cost = '||position) "Query Plan"
Start with id = 0 and statement_id ='A'
connect by prior id = parent_id and statement_id ='A';
set feedback on
set echo on
------ End of EXPLAIN.SQL script -----
If you don't have UTLXPLAN.SQL, just run this:
create table PLAN_TABLE (
timestamp date, remarks varchar2(80),
operation varchar2(30), options varchar2(30),
object_node varchar2(128), object_owner varchar2(30),
object_name varchar2(30), object_instance numeric,
object_type varchar2(30), optimizer varchar2(255),
search_columns numeric, id numeric,
parent_id numeric, position numeric,
Return to : Oracle
Database, SQL, Application, Programming Tips