| 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: RULE versus CHOOSE
See http://www.sucs.swan.ac.uk/~arthur/jargon/html/The-Jargon-Lexicon.html
At 09:20 AM 7/6/01 -0800, you wrote:
>Jon
>
>Great Answers Indeed .
>
>What does IMHO stand for though ?
>
>Thanks Again so much .
>
>Vivek
>
>
>> -----Original Message-----
>> Sent: Friday, July 06, 2001 8:11 AM
>> To: Multiple recipients of list ORACLE-L
>>
>>
>> > Qs.1 How does optimizer_mode=CHOOSE Compare with RULE ?
>>
>> There's a world of difference between them. Using the Cost-Based
>> Optimizer
>> (CBO) versus Rule-Based is like using a GPS system to navigate a city
>> you've
>> not been to in a while versus your own vague memories of it. In the
>> latter
>> case, you may only remember a few ways around and things may have
>> changed
>> dramatically. With the former, the current or near-current situation
>> is
>> always known if used properly.
>>
>> > Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE WITHOUT
>> > having Any Statistics Existent on the Application Tables , Indexes ?
>>
>> Using CHOOSE without analyzing the relevant schemas is the same as
>> using
>> RULE because Oracle has no current "map of the terrain" to utilize.
>> So, it
>> falls back on the standard rules.
>>
>> > Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ?
>>
>> Tons IMHO. Not in 8.1.7 in particular, but in general. Most databases
>> are
>> constantly changing with the addition, changing and removing of data.
>> What
>> the CBO offers is a roadmap to that changing data. Remember with the
>> CBO
>> that it is only as good as your statistics. In sites I work at, I
>> recommend
>> that relevant schemas are analyzed nightly to keep things current
>> (btw,
>> never analyze SYS). In conjunction with that, we create and update
>> histograms on relevant columns in the application schemas to handle
>> skewed
>> data distributions.
>>
>> > 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 .
>>
>> This is difficult to explain and quite complex. There surely is a
>> better
>> authority than I to answer this here and MetaLink could probably
>> enlighten
>> you as well.
>>
>> > 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
>> > - 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 .
>> > - 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.) .
>> > - Lastly his Team will Take Responsibility for Any Performance
>> Issues
>> > arising out of a Code underperforming .
>>
>> See what I said above about the changing face of data in a database.
>> And
>> what happens when his team changes? Are they always going to be there
>> to
>> handle the performance issues and keep on top of them? "A correct path
>> being
>> taken today may in time get automatically changed" is exactly the
>> reason as
>> far as I'm concerned to use the CBO. It takes the guesswork out of
>> most
>> query optimization if used properly.
>>
>> > Qs Are there Any Best practices Documents / Links on RULE vs. CHOOSE
>> ?
>>
>> I'm sure there are many other, but the biggies I work with are (1)
>> analyze
>> relevant schemas regularly, (2) create/maintain histograms on columns
>> that
>> have skewed data distributions and (3) don't analyze SYS.
>>
>> > Qs. What do you Folks Advice ? Should I Given in to the 80-20 Rule
>> Or
>> > Still Endeavour to persist for optimizer_mode=CHOOSE ?
>>
>> I would say persist in your argument for CHOOSE. The biggest reason to
>> do it
>> now IMHO is that, according to Oracle, RULE is going to be desupported
>> in
>> the near future. CHOOSE will soon be the default.
>>
>> Anyone with more experience than I here, please chime in.
>>
>> --
>>
>> Jon Walthour, OCDBA
>> Oracle DBA
>> Computer Horizons
>> Cincinnati, Ohio
>>
>>
>--
>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).
>
>
Dennis Taylor
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dennis Taylor INET: ismgr_at_pctc.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).Received on Fri Jul 06 2001 - 12:13:18 CDT
 |
 |