.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Tuesday, August 30, 2005

An Easier Explanation

The 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.

* see comment from Pete ... I missed the "BASIC" parameter value in the original post.

5 Comments:

At 10:08 AM, Blogger Pete Scott said...

Isn't TYPICAL the default?

 
At 7:17 PM, Anonymous Anonymous said...

AUTOTRACE in SQL*Plus 10.2 uses dbms_xplan. See http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14357/whatsnew.htm#sthref14
and http://www.oracle.com/technology/oramag/oracle/05-sep/o55asktom.html

A convenient way to try SQL*Plus 10.2 is to download the Instant Client verion from http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html
Install notes are at http://www.oracle.com/technology/docs/tech/sql_plus/10103/readme_ic.htm

-- CJ

 
At 7:36 PM, Blogger David Aldridge said...

Pete,

Um ... just testing to see if someone would catch that ... take a pound from the till ... erm ... I'll change it :(

Anon

That's handy ... I'll have to look that up anbd give it a try. thanks for the tip

 
At 5:31 PM, Anonymous Anonymous said...

what's wrong with

SQL> @xplan

PLAN_TABLE_OUTPUT
---------------------------
...snip...

 
At 11:52 PM, Blogger David Aldridge said...

neil,

the best solution for anyone would be the one that works for them ... stored scripts wouldn't work so well for me because I'd have to get them loaded on each developers machine, and I ain't going there. Nothing to say that a developer couldn't do that for him/her/itself of course.

 

Post a Comment

<< Home