Oracle-L: Moving From Rule-Based to Cost-Based

From: Sam Bootsma <SamB_at_cpas.com>
Date: Fri, 05 Apr 2002 08:49:12 -0800
Message-ID: <F001.0043D131.20020405084912@fatcity.com>

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: SamB_at_cpas.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 Apr 05 2002 - 10:49:12 CST