One option would be to: a) collect outlines for the app whilst running under rule b) change to cost but use the outlines c) piecemeal remove each outline and see if the sql runs the same (or better). If yes, then this sql is now "cbo-enabled", if not, look at changing sql
hth connor --- Sam Bootsma <[EMAIL PROTECTED]> wrote: > Hello All, > > At our site, we are use rule based optimization. We > are careful to write > SQL that performs well under this mode. Some of our > SQL also contains > hints. When we provide our application to clients, > we default it to use > Rule-based. However, some clients may choose to run > our application in > cost-based mode. This will be as simple as > analyzing all tables and indexes > in our schemas', and changing the mode to use cost > based. > > My Questions: > 1. How likely is it that our application will > perform worse under > cost-based than it does under rule-based. What has > been the experience of > others on the list who have moved from Rule-based to > Cost-based? > 2. What are some of the underlying reasons for > performance to decline when > making such a move? What are some "gotchas" to > watch for? > 3. Is there more that needs to be done to move from > rule to cost? (Other > than changing the optimization mode and analyzing > tables and indexes)? > > Most clients run our database on NT, although some > use UNIX. Assume Oracle > 9i. Our database (at client sites) tends to be > small, with the largest > being about 25 GB. > > Thanks for any suggestions, > > Sam Bootsma > Technical Support Analyst > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Sam Bootsma > 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). ===== Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
