Understood. Thanks Jonathan.
The possible plan switch is interesting. Something to keep in mind.
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:1024431573.18755.0.nnrp-12.9e984b29_at_news.demon.co.uk...
>
> 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 ?
> >
> >
>
>
>
>
>
>
Received on Wed Jun 19 2002 - 05:08:44 CDT
