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

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. Received on Fri Feb 01 2008 - 12:40:37 CST