Re: newbie, getting cost plan

codefragment_at_googlemail.com wrote:
> Hi
> I want to get the -real- cost and plan of an sql statement, not
> estimated I can use:
>
> (1) Explain Plan and DBMS_XPLAN from sqlplus, I gather this isn't the
> real plan?

Besides some special exceptions in most cases the plan shown will be the one used for execution.

> (2) Autotrace On, I gather this will be the real plan?
I believe DBMS_XPLAN and autotrace on in sqlplus are different approaches to get the same result.

> (3) tracing and tkprof, which can use explain plan, is this the real
> plan?

You can simply read it from the tracefile even without tkprof, look out for PARSING IN CURSOR and corresponding STAT lines. STAT lines are only created after a cursor is closed, so make sure, the session is terminated properly.

This will show the plan used in your testcase. But anyway if you create the tracefile with sqlplus it can differ from the one used in your application for the same reasons as in (1).

It's also possible to fetch the actual plan from the SGA while the statement is running using some v$ views. Tools like TOAD are also usefull to get this.

Jan Received on Fri Sep 07 2007 - 07:30:46 CDT