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

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

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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 18 Aug 1999 18:37:23 GMT
Message-ID: <37c8fbc4.199999554@newshost.us.oracle.com>


A copy of this was sent to rspeaker_at_my-deja.com (if that email address didn't require changing) On Wed, 18 Aug 1999 17:33:15 GMT, you 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.

thats a documented fact, it is true.

> 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.

correct, don't know what it maxes out at though. It 'guesses' basically.

> 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.

happens all of the time -- we have to make up statistics for tables where none exist.

>Supposedly
>the Quest tool performed an explain plan against the statement, and
>showed a small number of rows in the large table.

well, the quest tool ran the Oracle explain command which showed the estimated number of rows...

>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.

correct.

>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.

that seems pointless. If you analyze you don't want to use RBO. If you spent the time and energy to analyze, might as well use CBO. If you don't analyze and use CHOOSE, you'll get RBO unless you HINT a query in which case, you would HINT is more then just "first rows" -- if you are going out of the way to hint it, you would tell us what tables to drive with and what indexes to use maybe.

>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.
>

but again, if you are going to collect stats for the tables -- you might as well use the CBO.

>Any thoughts on this from the Oracle experts here?
>

RBO pretty much tries to generate a 'FIRST_ROWS' type of plan anyway. If you are using this just for FIRST_ROWS and want to use RBO for every other query, just use RBO -- don't go through the trouble of analyzing all of your data.

OTOH -- many of the new query features (bitmapped indexes, star queries, function based indexes, extensible datatypes, context queries, materialized views etc) make use of the CBO exclusively (you cannot use these features without using CBO) so if I was going to analyze my tables -- I would use CBO anyway....

>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 18 1999 - 13:37:23 CDT

Original text of this message

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