Explain Plan

J.O. Aho · *nix forums Guru Joined: 19 Feb 2005 Posts: 912

Or better find some software that helps you to read that execution plan.

DA Morgan · *nix forums Guru Joined: 06 Mar 2005 Posts: 1042

Niall Litchfield · *nix forums Guru Wannabe Joined: 29 Apr 2005 Posts: 128

<amerar@iwc.net> wrote in message news:1107548968.828618.55110@l41g2000cwc.googlegroups.com...

amerar@iwc.net · *nix forums Guru Wannabe Joined: 27 Jul 2005 Posts: 185

Actually you are wrong. We are using Locally Managed. And again,
there is nothing wrong with the explain plan I displayed.

What I am asking for is how or where I can find info on understand that
hunk of junk.

Sybrand Bakker · *nix forums Guru Joined: 03 Apr 2005 Posts: 1766

On 4 Feb 2005 06:06:58 -0800, amerar@iwc.net wrote:

amerar@iwc.net · *nix forums Guru Wannabe Joined: 27 Jul 2005 Posts: 185

Well, this was the output of running some PL/SQL procedure. There is
nothing wrong with this query. What I am getting at, is how would one
interpert all that?

I mean, I'd like to be able to figure out something like: First it did
a full table scan using an index, then it did a sort on those records,
next it joined with table B using columns C & D, then it did a full
table scan without an index on table B.........

Knowing exactly what happened and in what order helps to understand the
path that the query took. I have no clue by looking at this what
happened first, and to what objects and what was done.........that is
what I am trying to accomplish.

Arthur

DA Morgan · *nix forums Guru Joined: 06 Mar 2005 Posts: 1042

amerar@iwc.net wrote:

amerar@iwc.net · *nix forums Guru Wannabe Joined: 27 Jul 2005 Posts: 185

Hi All,

Ok, so now that I've turned on tracing, generated a trace file, ran
TKPROF and have some output, how the heck an I supposed to interpt
this:

I mean, in plain english, how can I look at my query and know what
exactly happened. I just do not know how to read these.....

I have to do these a lot, so I need to know if there is a method to
understanding all this stuff:

Rows Row Source Operation
------- ---------------------------------------------------
14 MERGE JOIN OUTER
15 VIEW
15 SORT GROUP BY
14 NESTED LOOPS
15 NESTED LOOPS
15 NESTED LOOPS
15 FIXED TABLE FULL X$KCCFE
28 FIXED TABLE FIXED INDEX #1 X$KCCFN
28 FIXED TABLE FULL X$KCCTS
14 FIXED TABLE FIXED INDEX #1 X$KCVFH
9 SORT JOIN
9 VIEW
9 SORT GROUP BY
9 VIEW DBA_FREE_SPACE
9 UNION-ALL
1 NESTED LOOPS
2 NESTED LOOPS
15 INDEX FAST FULL SCAN (object id 39)
15 TABLE ACCESS CLUSTER FET$
28 INDEX UNIQUE SCAN (object id 7)
1 TABLE ACCESS CLUSTER TS$
8 NESTED LOOPS
9 NESTED LOOPS
14 TABLE ACCESS FULL TS$
21 FIXED TABLE FIXED INDEX #1 X$KTFBFE
8 INDEX UNIQUE SCAN (object id 39)

Thanks,

Arthur

Google