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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RULE versus CHOOSE

RE: RULE versus CHOOSE

From: Terrian, Tom <tterrian_at_daas.dla.mil>
Date: Fri, 06 Jul 2001 05:39:56 -0700
Message-ID: <F001.00342A04.20010706052024@fatcity.com>

Jon,

Great write up.

Several times you mentioned creating Histograms for skewed data distributions. I am just curious if you have ever studied the performance impact with and without them? We used to maintain histograms but when we studied the performance impact (with and without them) we determined that there was very little benefit with histograms. The down side with them is that they drastically increased the amount of time it took to analyze the tables at night. We decided to do without them. Have you ever studied their benefits verse drawbacks?

Tom

Tom Terrian
Oracle DBA
WPAFB - DAASC
tterrian_at_daas.dla.mil
937-656-3844

-----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: Jon Walthour
  INET: jonw_at_fuse.net

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: Terrian, Tom
  INET: tterrian_at_daas.dla.mil

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 - 07:39:56 CDT

Original text of this message

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