| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: OPTIMIZER_MODE recommendation for 9.2
Bug #564434, logged against 7.3.3, is not "visible" on MetaLink, but it displayed a lovely row between Oracle Support (advocating the customer logging a TAR) and Oracle Development (paraphrased here):
Support: Setting ALL_ROWS or FIRST_ROWS forced the use of CBO, which performs poorly in the absence of gathered statistics. Objects owned by SYS should not be analyzed, per Oracle's own admonitions. Therefore, CBO queries against the data dictionary perform poorly. Conclusion: there is a bug in ALL_ROWS or FIRST_ROWS functionality...
Development: Effective CBO use requires gathered statistics. CBO is performing as designed and documented. It is not a bug.
Support: *sputter* *spit* WHAT!?!? But it doesn't work correctly the way it has been designed! Redesign it! This bug was present from v7.x through v8.1.7.0.0, at least. It seems to be fixed in v9.2.0.1. I don't know the status from v8.1.7.1 through v9.0.1.x because I don't have any databases to test upon...
--- I have a standard query against DBA_INDEXES and DBA_IND_COLUMNS which easily exhibits the problem in earlier versions (script "i.sql", online at http://www.evdbt.com/tools.htm, used for displaying indexes associated with a table). I've embedded a RULE hint in it. However, when I change that hint to ALL_ROWS or FIRST_ROWS in v9.2.0.1, it has no impact on the EXPLAIN PLAN or STATISTICS. Same sub-second response time... However, in v8.1.7.0.0, I still see the problem occurring. Leave the hint at RULE or set it to CHOOSE, and everything is fine with sub-second response time. Set the hint to FIRST_ROWS or ALL_ROWS, and you can watch an entire stage of the Tour de France before it completes... --- So, in summary, be very selective about how you use FIRST_ROWS and ALL_ROWS. Try not to set it at the "init.ora" level -- do it using ALTER SESSION or hints if absolutely necessary. As Connor noted and as MetaLink supports in the excellent note #35934.1 (i.e. "CBO - Common Misconceptions", just updated this week), there isn't a great deal of difference between CHOOSE and ALL_ROWS anyway... --- My own personal speculation: I expect the distinction between ALL_ROWS and FIRST_ROWS to disappear eventually, leaving only RULE and CHOOSE. If you read that MetaLink note, one can conclude that this has essentially already occurred. With RBO disappearing in Oracle10i, I expect the parameter OPTIMIZER_MODE to disappear as well. Just my $0.02... ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, August 05, 2002 10:13 AMReceived on Sat Aug 10 2002 - 13:23:25 CDT
> CHOOSE is the same as ALL_ROWS assuming you have
> statistics loaded on all of your tables. CHOOSE
> simply means
>
> - if there are NOT stats, use RULE
> - if there ARE stats, use ALL_ROWS
>
> hth
> connor
>
> --- Cherie_Machler_at_gelco.com wrote: >
> > Someone in our development group is recommending
> > that OPTIMIZER_MODE be set
> > to ALL_ROWS in our 9i databases. Everything that
> > I've read recommends
> > setting OPTIMIZER_MODE to choose.
> >
> > These are a medium-size ODS database that will have
> > reads and writes and a
> > smallish data warehouse and datamart that are going
> > to be used mostly for
> > reporting purposes.
> >
> > Why wouldn't I want to let Oracle choose? I think
> > the optimizer is
> > working better than ever in 9.2. Why would I want
> > to set up the whole
> > database as ALL_ROWS? Normally, I just use that as
> > a hint in batch jobs
> > or reports. Am I off-base?
> >
> > Thanks,
> >
> > Cherie Machler
> > Oracle DBA
> > Gelco Information Network
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author:
> > INET: Cherie_Machler_at_gelco.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
> =====
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
>
> "Remember amateurs built the ark - Professionals built the Titanic"
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
> INET: hamcdc_at_yahoo.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: Tim_at_SageLogix.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |