RE: views on views on views
- From: TESTAJ3@xxxxxxxxxxxxxx
- To: tanel@xxxxxxxxxx
- Date: Fri, 27 Mar 2009 14:12:34 -0400
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@xxxxxxxxxx>
To:
"'Greg Rahn'" <greg@xxxxxxxxxxxxxxxxxx>
Cc:
<ganstadba@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date:
03/27/2009 02:06 PM
Subject:
RE: views on views on views
Sent by:
oracle-l-bounce@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx]
> Sent: 27 March 2009 19:34
> To: tanel@xxxxxxxxxx
> Cc: ganstadba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
