XPLAN PLAN

Use the following to setup an explain plan table for a database.

I tested this using 10.2.0.2 so the plan table may differ, but theory should be the same from 9i onwards.


drop table plan_table;
create global temporary table plan_table(
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30),
other_xml clob
) on commit preserve rows;
drop public synonym plan_table;
create public synonym plan_table for plan_table;
grant all on plan_table to public;

create or replace view xplan_basic as
select * from table(dbms_xplan.display('plan_table',null,'BASIC'));
drop public synonym xplan_basic;
create public synonym xplan_basic for xplan_basic;
grant all on xplan_basic to public;

create or replace view xplan_typical as
select * from table(dbms_xplan.display('plan_table',null,'TYPICAL'));
drop public synonym xplan_typical;
create public synonym xplan_typical for xplan_typical;
grant all on xplan_typical to public;

create or replace view xplan_parallel as
select * from table(dbms_xplan.display('plan_table',null,'ALL'));
drop public synonym xplan_parallel;
create public synonym xplan_parallel for xplan_parallel;
grant all on xplan_parallel to public;

create or replace view xplan_serial as
select * from table(dbms_xplan.display('plan_table',null,'SERIAL'));
drop public synonym xplan_serial;
create public synonym xplan_serial for xplan_serial;
grant all on xplan_serial to public;

XPLAN PLANS Made Easy

Found this on David Aldridge's blog ... http://oraclesponge.wordpress.com

he DBMS_XPLAN supplied package gives results that are so much more usable than the old "set autotrace traceonly explain", or any manual method of reading the plan_table, that the only thing holding me back from using it more often was the lengthy syntax.

No more: I have created views to save my aching fingers. Feel free to use them, modify them, whatever for your own purposes -- just don't blame me if they break something ;)

create or replace view xplan_basic as
select * from table(dbms_xplan.display('plan_table',null,'BASIC'));*

create or replace view xplan_typical as
select * from table(dbms_xplan.display('plan_table',null,'TYPICAL'));

create or replace view xplan_parallel as
select * from table(dbms_xplan.display('plan_table',null,'ALL'));

create or replace view xplan_serial as
select * from table(dbms_xplan.display('plan_table',null,'SERIAL'));

I am now a mere "select * from xplan_parallel" away from my execution plans.

Thanks David.