RE: views on views on views

From: <TESTAJ3_at_nationwide.com>
Date: Fri, 27 Mar 2009 14:12:34 -0400
Message-ID: <OF0D8BEEB9.96DCE2DD-ON85257586.0064027D-85257586.00640708_at_lnotes-gw.ent.nwie.net>



it let me set it in 9.2.0.8

joe



Joe Testa, Oracle Certified Professional Senior Consultant
Data Engineering and Administration
Nationwide Investments

(Work) 614-677-1668
(Cell) 614-312-6715

Interested in helping out your marriage? Ask me about "Weekend to Remember"
Dec 11-13, 2009 here in Columbus.

From:
"Tanel Poder" <tanel_at_poderc.com>
To:
"'Greg Rahn'" <greg_at_structureddata.org> Cc:
<ganstadba_at_hotmail.com>, <oracle-l_at_freelists.org> Date:
03/27/2009 02:06 PM
Subject:
RE: views on views on views
Sent by:
oracle-l-bounce_at_freelists.org

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



> -----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 -- http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 27 2009 - 13:12:34 CDT

Original text of this message