Re: Does Autotrace give the actual execution plan?

hitman wrote:
> 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?

It does not necessarily show you the "real execution plan" that the query will run with.

A 10046 trace does show the real execution plan. Often they are the same but don't bet your life on it.

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

It shows how tables are accessed ( by an index or not, etc). It shows how 2 or more tables are joined together and in what order, etc.

Guy Harrison has an old and somewhat outdated book "SQL High Performance Tuning" that may be worth taking a look at. Plus free oracle documentation at http://tahiti.oracle.com and Tom Kyte's website http://asktom.oracle.com

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

Sorry no autotrace will not necessarily show you the plan that was used for the SQL. It will show you a plan. That plan may or may not be what the query will actually use ... if often is correct but not always. Received on Thu Jul 06 2006 - 07:17:56 CDT