| 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 - sorry it's long
One
more thing to consider, if you're using RBO, you may not be able to use lot of
features added to oracle 8i. features like function based indexes, bitmap
indexes, IOTs are applicable for CBO environment only.
<FONT face=Courier color=#0000ff
size=2>
<FONT face=Courier color=#0000ff
size=2>rgds
<FONT face=Courier color=#0000ff
size=2>amar
<FONT
size=2><FONT face=Courier
color=#0000ff>
<SPAN
class=918162906-07072001> -----Original Message-----From:
Koivu, Lisa [mailto:lisa.koivu_at_efairfield.com]Sent: Friday, July 06,
2001 5:41 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: RULE versus CHOOSE - sorry it's
long
Hi Vivek, comments inline.
List, please correct me if I am wrong.
-----Original Message----- <FONT
face=Arial size=1>From: <FONT face=Arial
size=1>VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com] <FONT
face=Arial size=1>Sent: <FONT face=Arial
size=1>Friday, July 06, 2001 1:56 AM <FONT face=Arial
size=1>To: <FONT face=Arial
size=1>Multiple recipients of list ORACLE-L <FONT face=Arial
size=1>Subject: <FONT
face=Arial size=1>RULE versus CHOOSE
Database = Oracle 8.1.7.0.0 on SunOS 5.6 .
Current Database Size = 20 GB <FONT
face=Arial size=2>This is only a Test One & the Live Production will be
a BIGGER (100 GB) One . <FONT
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?)
<FONT
face=Arial size=2>If Statistics are DELETED on ALL Objects , yet with
optmizer_mode = CHOOSE , does it behave
in Exactly the Same manner as having <FONT face=Arial
size=2>optmizer_mode set to RULE Or are there Still Some Advantages which
can be Reaped ? <FONT
face=Arial color=#0000ff size=2>[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 <FONT
face=Arial size=2>optimizer took a particular path ? <FONT
face=Arial size=2>What Causes a Path to be Chosen in CHOOSE , we are largely
ignorant about . <FONT
face=Arial color=#0000ff size=2>[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 :- <FONT
face=Arial size=2>- 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 <FONT face=Arial
size=2>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.
 |
 |