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 ?
Qs.2 Is there ANY Benefit of keeping optimizer_mode=CHOOSE WITHOUT
having Any Statistics Existent on the Application Tables , Indexes ?
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 ?
Qs.3 Are there any Disadvantages with Using RULE in 8.1.7.0 ?
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 .
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 .
Qs Are there Any Best practices Documents / Links on RULE vs. CHOOSE ?
Qs. What do you Folks Advice ? Should I Given in to the 80-20 Rule Or
Still Endeavour to persist for optimizer_mode=CHOOSE ?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
