Re: views on views on views

From: Riyaj Shamsudeen <>
Date: Fri, 27 Mar 2009 13:49:57 -0500
Message-ID: <>

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.


Riyaj Shamsudeen
Principal DBA,
Ora!nternals -
Specialists in Performance, Recovery and EBS11i

On Fri, Mar 27, 2009 at 1:05 PM, Tanel Poder <> 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
> Oracle Certified Master
> Oracle ACE Director
> OakTable Network Member
Received on Fri Mar 27 2009 - 13:49:57 CDT

Original text of this message