Explain Plan Tips
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)
Type @EXPLAIN
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"
From plan_table
Start with id = 0 and statement_id ='A'
connect by prior id = parent_id and statement_id ='A';
delete plan_table;
commit;
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 ( statement_id varchar2(30),
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,
other long);
2 comments:
Nice blog, thanks For sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Such a nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
Oracle Fusion HCM Online Training
Oracle Fusion Financials Online Training
Post a Comment