Re: newbie, getting cost plan

On Sep 7, 1:07 pm, codefragm..._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?
> (2) Autotrace On, I gather this will be the real plan?
> (3) tracing and tkprof, which can use explain plan, is this the real
> plan?
>
> and there are other ways I've seen mentioned?
> In my present scenario I can do what I want with the database and
> application.
>
> thanks

tracing and tkprof is the easiest, provided you do NOT use the explain= option (as this will run a new explain plan) and you left sql*plus properly before running tkprof (ie you issued a COMMIT or ROLLBACK which will have Oracle dump the real explain plan)

Other than that you could of course run statspack on level 6. This will save the real explain plan, in the statspack tables. Doing so, you can use sprepsql to retrieve the explain plan. Note: the method to do so is user unfrienly, however it works.

--
Sybrand Bakker
Senior Oracle DBA