| 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 Vs Cost
Surendra,
Saw no replies, so just pitching in. Moving over to the CBO does give you flexibility and the option for a query to 'self-tune' itself depending on the object statistics. (Dare I say 'self-tune' with all the notes flying around :) Anyway - what I meant was that _most_ queries would adjust their execution path depending on these stats. The two inputs to this process is the objects stats themselves as well as the algorithm that operates on these stats. This 'algorithm' can further be influenced (for want of a better term) using a number of init.ora parameters (documented and undocumented). The art is to determine the right values, but there are a few values such as OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ and that you will not go wrong with - Tim Gorman deals with these two in his excellent paper on the CBO. Browse at Tim's site at 'http://www.evdbt.com' .
At the same time, moving to the CBO without understanding this complex beast
is like getting into a souped-up Porshe when you have just got your Trainee
driver permit.
There are a number of issues with this that even seasoned DBAs miss... I
tried to cover these issues in my paper - just follow the second link on
'http://www.geocites.com/john_sharmila/links.htm'.
One way of performing a gradual move to the CBO is by gathering stats and then using an ON-LOGON trigger to switch the optimizer_goal to CHOOSE for selected programs/sessions. A final hint: Use PQ with caution - overuse and ill-planned implementation can be potentially deadly and overwhelme your CPUs. IMHO PQ is to be used ony in certain cases and that too only from the SQL using Hints, which will limit PQ only to that SQL.
John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002
Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com <http://www.klove.com/>
-----Original Message-----
[mailto:Surendra.Tirumala_at_mail.state.ky.us]
Sent: Friday, September 13, 2002 9:18 AM
To: Multiple recipients of list ORACLE-L
Hello All,
I am observing that some of our production instances(8i) are running on Rule Based Optimizer. I am dealing with the task of tuning a bunch of reports. While I continue my adventures, can you guys please let me know if there is any reason to continue with age old Rule based optimizer when we have so many advantages with CBO? I am exploring the option of using PQ with couple of reports but the RBO is forcing me to stop thinking about it.
All your suggestion are very much appreciated.
Thanks in advance.
Surendra Tirumala
Oracle DBA
Cabinet for Workforce Development
Commonwealth of Kentucky
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
INET: john.kanagaraj_at_hds.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 Sep 13 2002 - 21:13:19 CDT
 |
 |