RE: Cost vs Rule
Here's a convincing stat for your architect: There will be no RBO in a future version of Oracle. That version is not far away (conjecture is perhaps as early as 10). As often occurs, this is a case of do what we say, not what we do, since RBO is still used in the data dictionary tables. However that is a very special case of RBO for a primarily clustered, legacy schema that is in the process of being migrated to CBO. You can bet that when the data dictionary moves to CBO, RBO is going to go away. Now if you want to tie your application to a feature that severely limits the new features you can use, reduces scalability (forget bitmap indexes, partition pruning, function-based indexes, etc.), and is strongly discouraged by the vendor, then anchor yourself to the rock of RBO. Just make sure you don't have a short chain and a rising tide. While your architect is at it, make sure they use lots of the LONG datatype. It's really inconvenient and not long for this world too. And by the way, CBO works better than RBO in the great majority of cases. You need to investigate why your PK index is not being picked up in your query, not toss the entire current technology and fall back to heuristic optimization. As someone else mentioned, a hint is just a comment when viewed by other RDBMS. You can and should use hints and init.ora parameters to tune server and optimizer behavior. Regards, Chris Gait On 13 Mar 2002, at 7:08, Magaliff, Bill wrote: Date sent: Wed, 13 Mar 2002 07:08:25 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California yes, everything analyzed. sr tech arch has decided he wants to use RBO due to predictability in production. not much I can do at this point, unless I can really come up with convincing stats -Original Message- Sent: Wed, March 13, 2002 6:34 AM To: Multiple recipients of list ORACLE-L Well, since hints are implemented within comments, I would assume that other databases would simply ignore them. If anyone has direct experience, that would be interesting. Being completely database-agnostic may play against tuning. Just a thought. I suppose you analyzed all tables when you were testing CBO? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 07, 2002 2:00 PM To: Multiple recipients of list ORACLE-L not much - desire is to keep sql ANSI compliant due to cross-platform issues (want to be able to run the app on multiple db's) -Original Message- Sent: Thu, March 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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
RE: Cost vs Rule
not much - desire is to keep sql ANSI compliant due to cross-platform issues (want to be able to run the app on multiple db's) -Original Message- Sent: Thu, March 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). winmail.dat
Re: Cost vs Rule
[EMAIL PROTECTED] wrote: Well, since hints are implemented within comments, I would assume that other databases would simply ignore them. If anyone has direct experience, that would be interesting. Being completely database-agnostic may play against tuning. it has been my experience that those applications which choose to run on multiple databases are almost impossible to tune on any one of them. -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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). winmail.dat
RE: Cost vs Rule
Well, since hints are implemented within comments, I would assume that other databases would simply ignore them. If anyone has direct experience, that would be interesting. Being completely database-agnostic may play against tuning. Just a thought. I suppose you analyzed all tables when you were testing CBO? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 07, 2002 2:00 PM To: Multiple recipients of list ORACLE-L not much - desire is to keep sql ANSI compliant due to cross-platform issues (want to be able to run the app on multiple db's) -Original Message- Sent: Thu, March 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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). winmail.dat
Re: Cost vs Rule
How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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). winmail.dat
Re: Cost vs Rule
At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. Maybe setting optimizer mode to first_rows would do the same. Then you would still be using CBO, allowing your SQL to take advantage of optimizations that are not available with RBO. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
RE: Cost vs Rule
I see a new MYTH developing, and that is that RULE is better than COST. I have to believe that some thought has been put into the CBO. I also have to believe that if we all switched to RULE in the next ten minutes that the overwhelming response would not be Wow, look at the incredible improvement in performance! - Ethan -Original Message- Sent: Wednesday, March 13, 2002 3:34 PM To: Multiple recipients of list ORACLE-L At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. Maybe setting optimizer mode to first_rows would do the same. Then you would still be using CBO, allowing your SQL to take advantage of optimizations that are not available with RBO. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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).
RE: Cost vs Rule
Title: RE: Cost vs Rule Bill, If you really want to use CBO I would have thought that the plan stability features of Oracle would be sufficient to squash any argument about predictability. I have not used these myself, but they seem to have been included because of the need to guarantee predictable performance. Just my two centsI find the CBO much more complex than the RBO and I find myself wondering whether dealing with the complexity is actually worth the effort when the RBO provides sufficient performance for the applications I've worked on. I tend to take the simple is best approach. Cheers, Craig. -Original Message- From: Magaliff, Bill [mailto:[EMAIL PROTECTED]] Sent: Thursday, 14 March 2002 2:08 AM To: Multiple recipients of list ORACLE-L Subject: RE: Cost vs Rule yes, everything analyzed. sr tech arch has decided he wants to use RBO due to predictability in production. not much I can do at this point, unless I can really come up with convincing stats -Original Message- Sent: Wed, March 13, 2002 6:34 AM To: Multiple recipients of list ORACLE-L Well, since hints are implemented within comments, I would assume that other databases would simply ignore them. If anyone has direct experience, that would be interesting. Being completely database-agnostic may play against tuning. Just a thought. I suppose you analyzed all tables when you were testing CBO? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 07, 2002 2:00 PM To: Multiple recipients of list ORACLE-L not much - desire is to keep sql ANSI compliant due to cross-platform issues (want to be able to run the app on multiple db's) -Original Message- Sent: Thu, March 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists
Re: Cost vs Rule
keep sql ANSI compliant due to cross-platform issues (want to be able to run the app on multiple db's) They are telling you not to solve performance problems with hints. They want to be ANSI compliant and run on multiple db's. But it's OK solve performance problems by flipping the switch to RBO? RBO isn't part of the ANSI standard, so I don't think you're allowed to use it either. ;-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
Re: Cost vs Rule
Bill, There are some good notes on Metalink about why CBO avoids using an index when one is available. I'll see if I can find a note number but you might try searching on index and optimizer. Sometimes you need to modify the code in order to get better performance under CBO. Can you test modified code. Harrison has a good book Oracle SQL High-Performance Tuning which is very good. Burleson also has a book Oracle High-Performance SQL Tuning which is very good. If you're not under tremendous schedule pressure, I'd recommend that you tune the poorly performing SQL to run better under CBO. RBO is not getting any new features and will eventually go away. CBO continues to evolve and get better so if this is a new project, it's best to start out with CBO, in my opinion. If you have to, you can set the entire database to CBO and then add rule-based hints to the poorly-performing statements, if there aren't too many of them. That is, if you have the capability to add hints. Cherie Machler Oracle DBA Gelco Information Network Magaliff, Bill Bill.Magaliff@len To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dware.com cc: Sent by: Subject: Cost vs Rule [EMAIL PROTECTED] 03/07/02 01:23 PM Please respond to ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Cost vs Rule
This shouldn't be a problem, hints just look like comments to other db's. John [EMAIL PROTECTED] wrote: not much - desire is to keep sql ANSI compliant due to cross-platform issues(want to be able to run the app on multiple db's)-Original Message-Sent: Thu, March 07, 2002 2:44 PMTo: Multiple recipients of list ORACLE-LHow much have you played with Oracle Hints???-Joe--- "Magaliff, Bill" [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We'vebeen running CBO in all of our dev environments, but we have a fewlong txnsthat just take forever. At the request of some savvy developers, Iturnedon RBO, and it brought down execution times dramatically.I've been analyzing affected tables often (we do a lot of bulkload/unloadfor testing), and have played with partitioning and clustering,particularlyon one table that's just a dog. CBO will always do a FTS where RBOuses thePK to retrieve data.Where to go next? I've been unable to alter the costs dramaticallyenoughto make any real difference in execution time.thx-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Magaliff, Bill INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / MailingLists To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).__Do You Yahoo!?Try FREE Yahoo! Mail - the world's greatest free email!http://mail.yahoo.com/
RE: Cost vs Rule
Bill, In addition to the many excellent suggestions, may I also suggest generating adequate number of histograms and using them by using literals instead of bind variables (horrors!). You may also want to look at 9i - the CBO therein looks at the value of the bind variables prior to parsing and can thus use histograms. This was one of the drawbacks of Histograms that seems to have been addressed in 9i. (Would any of the Guru's please confirm this? I don't have access to a 9i instance to test out :( John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
Re: Cost vs Rule
How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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).
RE: Cost vs Rule
Bill - Are you saying that you switched to RBO for everything? I thought the better procedure would be to remain CBO and put hints into individual SQL queries. Is this a possibility in your environment? You mention that you are a development shop. Does this mean that you create software and sell it to others? Personally, I wouldn't be pleased if my company purchased some software and found it required me to switch my database to RBO. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 07, 2002 1:23 PM To: Multiple recipients of list ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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).
RE: Cost vs Rule
not much - desire is to keep sql ANSI compliant due to cross-platform issues (want to be able to run the app on multiple db's) -Original Message- Sent: Thu, March 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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).
Re: Cost vs Rule
Is your setting for db_file_multiblock_count too high? What's the optimizer mode? Have you set any of the optimizer_index_ parameters in the initialization file? These parameters might be driving Oracle to prefer a full table scan over a index scan. My 1.2 cents (after NY Taxes). Raj Joe Raube jraube@yahooTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Re: Cost vs Rule root@fatcity. com March 07, 2002 02:43 PM Please respond to ORACLE-L How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: Cost vs Rule
Interesting point . . . but the flip side of your argument is that by putting hints in we, as the vendor, while not requiring you to use CBO (since RBO will just ignore the hints) are saying, 'Hey, you had BETTER use CBO if you want this thing to work the way we've designed it' interesting to continue this discussion thread, although it's separate from the tuning issue I need to look at -biill -Original Message- Sent: Thu, March 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L Bill - Are you saying that you switched to RBO for everything? I thought the better procedure would be to remain CBO and put hints into individual SQL queries. Is this a possibility in your environment? You mention that you are a development shop. Does this mean that you create software and sell it to others? Personally, I wouldn't be pleased if my company purchased some software and found it required me to switch my database to RBO. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 07, 2002 1:23 PM To: Multiple recipients of list ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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).
RE: Cost vs Rule
have you tried adjusting optimzer_index_caching and optimizer_index_cost_adj? -Original Message- Sent: Thursday, March 07, 2002 1:23 PM To: Multiple recipients of list ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 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).
RE: Cost vs Rule
Well, since hints are implemented within comments, I would assume that other databases would simply ignore them. If anyone has direct experience, that would be interesting. Being completely database-agnostic may play against tuning. Just a thought. I suppose you analyzed all tables when you were testing CBO? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 07, 2002 2:00 PM To: Multiple recipients of list ORACLE-L not much - desire is to keep sql ANSI compliant due to cross-platform issues (want to be able to run the app on multiple db's) -Original Message- Sent: Thu, March 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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).
Re: Cost vs Rule
Tim Gorman wrote a good paper about using these parameters: http://www.evdbt.com/SearchIntelligenceCBO.doc Shaw John-P55297 wrote: have you tried adjusting optimzer_index_caching and optimizer_index_cost_adj? -Original Message- Sent: Thursday, March 07, 2002 1:23 PM To: Multiple recipients of list ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos 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).
RE: Cost vs Rule
always analyze - it's step 1 in any tuning I attempt usually use compute, but if estimate it's at least 15 or 20% -Original Message- Sent: Thu, March 07, 2002 3:19 PM To: Multiple recipients of list ORACLE-L Well, since hints are implemented within comments, I would assume that other databases would simply ignore them. If anyone has direct experience, that would be interesting. Being completely database-agnostic may play against tuning. Just a thought. I suppose you analyzed all tables when you were testing CBO? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 07, 2002 2:00 PM To: Multiple recipients of list ORACLE-L not much - desire is to keep sql ANSI compliant due to cross-platform issues (want to be able to run the app on multiple db's) -Original Message- Sent: Thu, March 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L How much have you played with Oracle Hints??? -Joe --- Magaliff, Bill [EMAIL PROTECTED] wrote: I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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:
RE: Cost vs Rule
just changed index_cost_adj from 100 to 1 what is index_caching? -Original Message- Sent: Thu, March 07, 2002 3:04 PM To: Multiple recipients of list ORACLE-L have you tried adjusting optimzer_index_caching and optimizer_index_cost_adj? -Original Message- Sent: Thursday, March 07, 2002 1:23 PM To: Multiple recipients of list ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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).
Re: Cost vs Rule
[EMAIL PROTECTED] wrote: Well, since hints are implemented within comments, I would assume that other databases would simply ignore them. If anyone has direct experience, that would be interesting. Being completely database-agnostic may play against tuning. it has been my experience that those applications which choose to run on multiple databases are almost impossible to tune on any one of them. -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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).
RE: Cost vs Rule
awesome article - tanks -Original Message- Sent: Thu, March 07, 2002 3:44 PM To: Multiple recipients of list ORACLE-L Tim Gorman wrote a good paper about using these parameters: http://www.evdbt.com/SearchIntelligenceCBO.doc Shaw John-P55297 wrote: have you tried adjusting optimzer_index_caching and optimizer_index_cost_adj? -Original Message- Sent: Thursday, March 07, 2002 1:23 PM To: Multiple recipients of list ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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).
RE: Cost vs Rule
The following is from the Tim Gorman paper · OPTIMIZER_INDEX_CACHING This initialization parameter represents a percentage value, ranging between the values of 0 and 99. The default value of 0 indicates to the CBO that 0% of database blocks accessed using indexed access can be expected to be found in the Buffer Cache of the Oracle SGA. This implies that all index accesses will require a physical read from the I/O subsystem for every logical read from the Buffer Cache, also known as a 0% hit ratio on the Buffer Cache. This parameter applies only to the CBO's calculations of accesses for blocks in an index, not for the blocks in the table related to the index. · OPTIMIZER_INDEX_COST_ADJ This initialization parameter is also a percentage value, ranging between 1 and 1, representing a comparison between the relative cost of physical I/O requests for indexed access and full table-scans. The default value of 100 indicates to the cost-based optimizer that indexed access is 100% as costly (i.e., equally costly) as FULL table scan access. As it turns out, the default values for each of these parameters are wildly unsuitable and unrealistic. I'll prove this assertion later in this paper, but for now, suffice to say that OPTIMIZER_INDEX_CACHING should be set to 90 and OPTIMIZER_INDEX_COST_ADJ should be set to a value which usually ranges between 10 and 50 for most online transaction processing (OLTP) systems, For data warehousing or other decision-support systems (DSS), it might be prudent to simply set this parameter to 50. -Original Message- Sent: Thursday, March 07, 2002 2:54 PM To: Multiple recipients of list ORACLE-L just changed index_cost_adj from 100 to 1 what is index_caching? -Original Message- Sent: Thu, March 07, 2002 3:04 PM To: Multiple recipients of list ORACLE-L have you tried adjusting optimzer_index_caching and optimizer_index_cost_adj? -Original Message- Sent: Thursday, March 07, 2002 1:23 PM To: Multiple recipients of list ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE
