RE: views on views on views

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




> -----Original Message-----

> From: Greg Rahn [mailto:greg_at_structureddata.org] 

> Sent: 27 March 2009 19:34

> To: tanel_at_poderc.com

> Cc: ganstadba_at_hotmail.com; oracle-l_at_freelists.org

> Subject: Re: views on views on views

> 

> IIRC you can also grab the view expanded SQL from a 10053 

> trace as well.

> 

> On Fri, Mar 27, 2009 at 9:21 AM, Tanel Poder <tanel_at_poderc.com> wrote:

> > Btw, in 11g the _dump_qbc_tree works as well, but the output is 

> > somewhat less readable (too much detail is outputted) but some 

> > post-processing with AWK should help..

> 

> --

> Regards,

> Greg Rahn

> http://structureddata.org


--
http://www.freelists.org/webpage/oracle-l