RE: RULE versus CHOOSE - sorry it's long

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Fri, 06 Jul 2001 05:35:10 -0700
Message-ID: <F001.00342A64.20010706054113@fatcity.com>

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).

Received on Fri Jul 06 2001 - 07:35:10 CDT