Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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>...Received on Tue Jun 18 2002 - 15:20:29 CDT
>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 ?
>
>