Re: Does Autotrace give the actual execution plan?

HKLN wrote:
> My questions:
> 1) In the Autotrace section of Oracle9i tuning guide, it states that
> "The execution plan output is generated using the EXPLAIN PLAN
> command." Does it mean that the Autotrace's plan is just a theoretical
> execution plan, not the acutal execution plan?
>
> 2) EXPLAIN PLAN shows the theoretical execution plan. What can I do
> with this plan? I heard that it helps comparing the relative cost of
> two queries.
>
> Thanks.

What do you mean by "theoretical" plan? It just shows the current plan (which really does change often if you use Cost Based Optimiser, but I am confident that if I did everything to help it - gather relevant statistics etc, - that it will give the best of itself.)

What to do with the results? Seeing that each table is being accessed efficiently, that the driving table has the best filter, check the join order in each step, see if the join method is appropriate for the number of rows being returned... etc, (no need to go on transcribing the rest of the page 6-9 entitled Reviewing the Execution Plan.) As you mentioned, comparing the relative cost of two queries really makes sense too.

DS Received on Thu Jul 06 2006 - 06:20:36 CDT