Re: views on views on views

Those 10053 trace outputs from intermediate and final transformation is
extremely useful for tuning complex subqueries. Especially, 11g
transformations are quite complex and it will be almost impossible to tune
these access plan without these intermediate transformation printed.

I even wrote a paper about these intermediate transformations and various
final transformation in an hotsos symposium paper.

http://www.orainternals.com/papers/riyaj_cost_based_query_transformations_ppt.pdf
http://www.orainternals.com/papers/riyaj_cost_based_query_transformation_doc.pdf

-- 
Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals -  http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com


On Fri, Mar 27, 2009 at 1:05 PM, Tanel Poder <tanel@xxxxxxxxxx> wrote:

> Greg,
>
> I checked, yep at least on 11g the 10053 trace dumped "unparsed" queries in
> multiple locations (including the final one - after transformations). This
> is very useful too, I hadn't noticed that before.
>
> The _dump_qbc_tree still gives one advantage though, it lists which columns
> are requested from child rowsources which may help understand some
> index/table io related issues better (for example why is some table still
> accessed even though all the columns in select list are also in some index
> -
> the columns fetched for join/order conditions for example). It's possible
> to
> parse this stuff manually out (in your head) from SQL text too, but with
> large SQLs it may be time consuming..
>
> Anyway, this CBO thing is a helpful feature. You can take the eventual
> "unparsed" query and run it natively, tune it to acceptable speed and then
> see how to "backport" this fix into the view definitions :)
>
> Does anyone know since which version the CBO dumps this info out?
>
> --
> Regards,
> Tanel Poder
> http://blog.tanelpoder.com
> Oracle Certified Master
> Oracle ACE Director
> OakTable Network Member
>
>

Other related posts: