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