Re: Moving From Rule-Based to Cost-Based
It is hard to say what will happen to the execution plans if your SQL written for rule based are run with cost based optimizer. I would collect all my queries in one file, analyze all my tables, set for cost based optimization, run the queries and check my execution plans and compare. Some of the things you might notice is the table order in the execution plans might change. The driving table may change and that may make the world difference in execution speed. I once did this test. I went from Oracle 7.2 on OS2 to 8.1.5 onNT and my application ran twice as fast without any changes. I am sure some of the gain is coming from Oracle 8i but it was a pleasant surprise.Cost based optimizer has improved drastically since version 7 and does a decent job of creating proper execution plans. However, you have to keep an eye on it. I have found it more difficult to optimize code using Cost optimizer than rule based optimizer. There are too many variables in statistics etc that we do not know everything about and some time it is hard to get the execution plan you want. I still collect all the SQL in one file for all my applications. If I find that one of my process is taking very long time, I run this file and compare my execution plans. this provides me instant information on where I am having problems. Oracle is doing more work on cost based optimizer to support new features like partitioning and so on. So it would be to your advantage to move forward to cost based optimizer. There are lot more parameters that you can use to customize the optimizer to your liking and get the type of execution plans. You can use the same execution plans on test and production system by using Oracle new execution plan stability features. I would not simply give rule based SQL and let my customer run on cost based optimizer without me testing and making sure that it would not blow up my application. Even though I worked on over 100 gig DB, I would not consider a 25 gig DB a small database. --- 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). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir 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: Moving From Rule-Based to Cost-Based
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).
RE: Moving From Rule-Based to Cost-Based
Sam, Ah-ha, Just the question I want to answer :) (To explain further - I will soon be presenting a paper at IOUG appropriately titled 'Safely navigating the RBO-to-CBO minefield', so this is right up my alley!) Since I cannot distribute or make this paper available to the public (yet), I will stop with giving you the overall stuff that *may* be applicable to you. * Keep in mind that the RBO is just based on rigid rules, while the CBO allows itself to led down a path that it thinks is the best. The two factors affecting the CBO path are the data (object statistics) you feed the algorithms (that comes from Oracle Corp), along with the various optimizer related init.ora parameters (hidden and visible). At the risk of oversimplifying the issue, I would say that as long as you keep the beast fed with proper statistics and correct init.ora parameters, and you are at a reasonable version such as 8.1.7.x (where x is preferably 4), I would say you are generally Ok. * If you are using some of the newer Oracle 8+ objects or features, remember that CBO will be invoked regardless of mode (RULE) or presence of stats. PDML/PQ also invokes the CBO. * If you set CHOOSE, and remote DBs are involved, or your SQL operates across schemas/tables some of which have stats and some not, be aware that the CBO will default with some very ugly defaults on the objects where statistics are not present. * Be aware that some init.ora can _drastically_ affect plans for the CBO. Misconfigured defaults for OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ will make the CBO lean towards FTS. Add high values for SORT_AREA_SIZE and DB_FILE_MULTI_BLOCK_READ_COUNT and you a recipe for disaster... Read Tim Gorman's execellent paper on finding life in the CBO at www.evdbt.com for a thorough explanation for this. I have also seen major parse problems with CBO that have been fixed only by reducing 'OPTIMIZER_MAX_PERMUTATIONS' - this one is worth considering if you are using lots of joins and views that are joins themselves. * Do collect, store and report Oracle and OS stats and investigate any strange increases before/after the move. * Look at using DBMS_STATS rather than ANALYZE if you are at 8i + * Hint only as a last resort. Use Plan outlines if possible, but temporarily. This is really an exciting journey, despite the hidden 'mines'. As long as you understand how the CBO works and the common pitfalls (and myths) that may crop up, you will be Ok. 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 ** > -Original Message- > From: Sam Bootsma [mailto:[EMAIL PROTECTED]] > Sent: Friday, April 05, 2002 8:49 AM > To: Multiple recipients of list ORACLE-L > Subject: Moving From Rule-Based to Cost-Based > > > 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
Moving From Rule-Based to Cost-Based
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).
