Re: A potential bug (infinite loop) in Oracle: querying v$access

On Feb 1, 12:40 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Feb 1, 12:38 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > On Jan 24, 2:13 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > I am still looking for the source of the suggestion to use 'ALL' as
> > > the parameter for GATHER_FIXED_OBJECTS_STATS - it was apparently not
> > > from "Expert Oracle Database 10g Administation", as that book shows
> > > nothing after GATHER_FIXED_OBJECTS_STATS.
>
> > > Charles Hooper
> > > IT Manager/Oracle DBA
> > > K&M Machine-Fabricating, Inc.
> > Oracle has published Metalink Note 549895.1, regarding this same
> > problem.  Their solution is different, and doesn't involve fixed
> > object statistics.
>
> > David Fitzjarrell
>
> Thanks for the reference to the article.
>
> It is an interesting article in that the author did not investigate
> what was causing the bad execution plan, only that a "MERGE JOIN
> CARTESIAN" operation appeared in the plan - and the plan indicated
> that the join did (or should have) taken 0.01 seconds.  Instead of
> addressing the cause of the problem, they supplied a bandage to
> something that "might" have been the problem (even though it was not
> indicated in the plan) by making the following change:
> alter session set "_optimizer_cartesian_enabled"=false;
>
> Cartesian merge joins are not necessarily bad in all cases.  Page 387
> of "Cost-Based Oracle Fundamentals" contains a bit of information
> about this type of join, and how it might be helpful.
>
> I wonder who the author of that article is?  Did you notice this
> wording on the article: "This document is being delivered to you via
> Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV)
> process, and therefore has not been subject to an independent
> technical review."  Maybe it should have been subject to review?
>
> As far as I am aware, fixed object statistics are supposed to be
> gathered on Oracle 10g databases.
>
> On a side note, I found the source of the suggestion to use 'ALL' as
> the parameter for GATHER_FIXED_OBJECTS_STATS.
> "OCP Oracle Database 10g Exam Guide" by Alapati, who I believe is also
> the author of teh very useful book "Expert Oracle Database 10g
> Administation" by Sam Alapati:http://www.oracle.com/technology/books/pdfs/exam_ch5.pdf
>
> The suggestion to use 'ALL' can also be found here:http://www.oracle.com/technology/books/pdfs/book_10g_chap9_ce1.pdf
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

I. too, thought it odd that such a 'band-aid' was suggested by Oracle, absent any real investigative work. My choice is to compute the fixed objects statistics, as I'd rather not adversely affect every other query using a MERGE JOIN CARTESIAN in order to fix one errant example.

David Fitzjarrell Received on Fri Feb 01 2008 - 14:48:06 CST