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: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Sun, 22 Aug 1999 11:36:31 +0800
Message-ID: <37BF703F.7E3E@yahoo.com>


Thomas Kyte wrote:
>
> 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

We had a similar requirement in the "mixing" of rule versus cost. We implemented a financials app (not Oracle) where the vendor would only support RBO (dunno why - but that was their constraint)...

But for end user adhoc query we wanted CBO. So we set RBO in init.ora and created a set of CBO hinted views for the adhoc queries...

Once we move to 8.1 (ie when our vendor supports a release higher than 7.3!) will probably mimic the same thing using a logon trigger with 'alter session' to set the optimizer mode for the end users.

Cheers
--



Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Sat Aug 21 1999 - 22:36:31 CDT

Original text of this message

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