Re: tkprof registering CTAS Explain Plan on 9.2

I don't think it's anything new to Oracle 9; it's only with an explicit EXPLAIN PLAN that you can get execution paths for CTAS. The plans you normally get from tkprof are the repeats from the STAT lines, which are dumped from the memory structure which has been exposed as v$sql_plan - but the SQL for DDL is not in the v$sql and all its relatives, so no STAT lines, so no plan. (If you check the hash_value (hv=)) in the trace file you'll find the value is zero).

Of course, you also have to be careful about CTAS execution plans anyway, as they need not be the same as the matching SELECT at the best of times - typically because Oracle recognises that a CTAS won't complete until it has completed (was that Yogi Berra ?) so it may switch from explicit FIRST_ROWS optimisation to implicit ALL_ROWS.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Telemachus wrote in message <5jJP8.2007$vB.13893_at_news.indigo.ie>...


>Just trying tkprof on 9.2

>That new waits summary is excellent.

>

>However a select will produce an explain plan in tkprof output but a CTAS

>on top of the same select won't

>

>( I pushed it over to CTAS since I wanted to remove the effects of the

>SQL*NET waits )

>

>Is this a known thing ?

>

>