RE: RULE versus CHOOSE - sorry it's long

Just some comment on following lines ....

> Another comment: I believe the 9i doc states that RBO is
> desupported. Gosh, I highly doubt it, I think Oracle Apps run RBO.
> But Oracle has been saying for a long time that RBO is "going away".

        Oracle themselves have shifted from RBO to CBO beginning with Oracle Applications release 11i .

        So there shouldn't be major hassle dessuporting RBO from 9i onwards.

HTH,
Rajesh
OCDBA 8&8i
> -----Original Message-----
> From: Koivu, Lisa [SMTP:lisa.koivu_at_efairfield.com]
> Sent: Friday, July 06, 2001 6:14 PM
> To: 'ORACLE-L_at_fatcity.com'; VIVEK_SHARMA
> Subject: RE: RULE versus CHOOSE - sorry it's long
>
> Hi Vivek, comments inline. List, please correct me if I am wrong.
>
> -----Original Message-----
> From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
> Sent: Friday, July 06, 2001 1:56 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RULE versus CHOOSE
>
> Database = Oracle 8.1.7.0.0 on SunOS 5.6 .
> Current Database Size = 20 GB
> This is only a Test One & the Live Production will be a BIGGER (100
> GB)
> One .
> OPTIMIZER_MODE = RULE & NO Statistics Exist Currently
>
> Qs.1 How does optimizer_mode=CHOOSE Compare with RULE ?
> [Lisa Koivu]
> Choose invokes the cost based optimizer (CBO). If the optimizer_goal
> = RULE, it is rule, period (RBO).
>
> Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE
> WITHOUT
> having Any Statistics Existent on the Application Tables , Indexes ?
> [Lisa Koivu]
> Well, that equates to rule, I believe. However, if anyone does
> something like put statistics on an index somewhere and forget to
> delete them, and that index is used in a query, your optimizer will
> change to CBO and you may end up with unexpected query plans. I also
> believe that degree > 1 will invoke CBO. (not exactly sure?)
>
>
> If Statistics are DELETED on ALL Objects , yet with optmizer_mode =
> CHOOSE , does it behave in Exactly the Same manner as having
> optmizer_mode set to RULE Or are there Still Some Advantages which can
>
> be Reaped ?
> [Lisa Koivu]
> Same as answer above.
>
> Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ?
> [Lisa Koivu]
> Can't comment specifically on that, I haven't had the opportunity to
> play with 8.1.7
>
> Qs.4 In Choose mode are there any Commonly known Standard
> Important
> Statistics' Fields/Values which can be Looked at to understand why
> optimizer took a particular path ?
> What Causes a Path to be Chosen in CHOOSE , we are largely ignorant
> about .
> [Lisa Koivu]
> Read up on histograms and exactly what the statistics mean
> (DBA_TABLES, etc). With CBO it isn't always exactly clear why it did
> what it did - for example, I have tried in the past to eliminate all
> FTS's from a query. CBO did not like that, it wanted to FTS at least
> one table. That's one thing you will find in CBO - it will favor
> FTS's more so than RBO.
>
> NOTE - At a Customer's Database , Our Development Section Head
> wants to
> set optmizer_mode=RULE & keep it so . His Reasons :-
> - The path of the optimizer is more predictable when set to RULE
> [Lisa Koivu]
> Well, yes. There is a published list of steps RBO will take to try to
> determine the query plan.
> - Any under-performance Issues would be Handled by Giving HINTS etc
> rather than Allowing the Optimizer to Choose / Compute it's own Path
> which may be a BAD One .
> [Lisa Koivu]
> Well, have you tried it? Some view hints as hard-coding. However, in
> some cases it is warranted.
>
> - A Correct Path being Taken Today may in Time get Automatically
> Changed
> to a Worse Path somewhere in future (with the Stats getting OLD etc.)
> .
> [Lisa Koivu]
> You avoid this by keeping your statistics fresh at all times. You may
> have to mess with the statistics for any skewed columns (again, this
> is histograms) but the bottom line is stale statistics mean
> sub-optimal query plans. I believe there's a package called DBMS_STATS
> that will monitor your objects for stale statistics. I don't know
> what the threshold is for determining if statistics are stale, I
> haven't investigated this package. However, there's also a school of
> thought that says ANY change to your data renders the statistics
> invalid, period. Also, deleting statistics from an object and
> following this step with analyzing the object renders much better
> behavior. Don't ask why... it's just another quirk (list, correct me
> if I am wrong)
>
> - Lastly his Team will Take Responsibility for Any Performance
> Issues
> arising out of a Code underperforming .
> [Lisa Koivu]
> Well, that's a challenge. Tuning your top ten bad statements should
> be an ongoing task... Are they qualified to do this? are there any
> SQL tuning experts on his team?
>
> Qs Are there Any Best practices Documents / Links on RULE vs.
> CHOOSE ?
> [Lisa Koivu]
> CBO is best suited for DSS environment, where FTS is common and is not
> viewed as evil. CBO has several features built into it that exploit a
> proper star schema design and deliver plans more suited to the volume
> of data (index combining, etc.) I have yet to see an OLTP or
> hybrid-type system running successfully on CBO, but then again I have
> only been looking at this closely over the last three years. Maybe the
> list can shed mroe light on this question.
>
> Qs. What do you Folks Advice ? Should I Given in to the 80-20
> Rule Or
> Still Endeavour to persist for optimizer_mode=CHOOSE ?
> [Lisa Koivu]
> I can tell you that just analyzing everything, setting optimizer_goal
> = CHOOSE and restarting the app most likely will not work. Moving to
> CBO will involve a certain amount of trial and error, with you and the
> support team learning why CBO does what it does, what can be done
> differently in the schemas/sql statements, etc. In short, it can be
> viewed as a sort of migration.
>
> Another comment: I believe the 9i doc states that RBO is
> desupported. Gosh, I highly doubt it, I think Oracle Apps run RBO.
> But Oracle has been saying for a long time that RBO is "going away".
> Be absolutely sure to read up on this on Metalink/Technet and present
> your findings to this damager. If this is truly going to happen, this
> migration will need to happen sooner or later.
>
> Sorry to be so long winded. I hope this helps you.
>
> Lisa Koivu
> Data BORED Administrator
> Ft. Lauderdale, FL, USA
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infy.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rajesh Dayal
  INET: Rajesh_at_ohitelecom.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).