Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Interesting Information ... BUT is it correct, or not?

Re: Interesting Information ... BUT is it correct, or not?

From: Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
Date: Wed, 18 Aug 1999 18:50:41 GMT
Message-ID: <37BB0081.13DBBB05@edcmail.cr.usgs.gov>


This seems to be totally different from the way the Oracle Press books talk about the subject. Recapping the Oracle Press books:

   The optimizer will default to CHOOSE unless you have modified your init.ora file. But you must have the table analyzed. If the table is not analyzed, then the optimizer will use the RULE method. Even if the optimizer is defaulted to CHOOSE and the table has been analyzed, then providing a hint will force the optimizer to use the RULE based method.

That's what I remember anyway. And it seems to contradict the Quest guy.

Hope that helps,
Brian Peasland
Raytheon Systems
peasland_at_edcmail.cr.usgs.gov

rspeaker_at_my-deja.com wrote:

> okay, here is one for the Oracle gurus. I recently attended a
> presentation by one of Quest Software's lead technical reps. He made
> an interesting statement, which he 'proved' via one of their tools (I
> don't remember which one), but I'd like to know if anybody has ever
> seen anything to confirm or refute this. I'll try to recap as best as
> I remember.
>
> If the optimizer mode is set to rule-based, but a query is issued with
> a hint, such as /*+ First_Rows */, the query will use cost-based
> optimization. If the table the hinted query is run against has not
> been analyzed, and thus has no statistics, Oracle will "make up" the
> statistics for the number of rows, maxing out at approximately the
> block size. The rep claims to have seen a situation where a user was
> joining a small reference table with a large detail table (1MM
> records), and was having performance problems on the query. Supposedly
> the Quest tool performed an explain plan against the statement, and
> showed a small number of rows in the large table. Since Oracle was
> only identifying the table as having a small number of rows (4072 I
> think) instead of over 1 million, it was performing a full table scan
> on this large table which was causing the problem. The rep said the
> solution, assuming you always want to use rule-based optimization, was
> to set ruled-based in the init.ora file, instead of choose, and analyze
> the table(s) in question. This would allow the cost-based-optimizer,
> forced by the hint, to use the actual table statistics instead of
> guessing, and would have no adverse effect on the database since rule-
> based was being forced.
>
> Any thoughts on this from the Oracle experts here?
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Wed Aug 18 1999 - 13:50:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US