Re:Moving From Rule-Based to Cost-Based

Sam,

    If your SQL has hints in it your using the Cost based optimizer as it is.
>From my experience an sql statement that is well written from a rule based
optimizer point of view will not adversely be affected by the cost based optimizer while the statistics are valid/accurate. Now a poorly written statement with invalid/inaccurate statistics will kill you. And a poorly written statement with valid/accurate statistics will help save the day. I switched out Operational Data Store from rule based to cost based optimization some months ago, like 24 months. And have been computing statistics every weekend. The end users who write their own SQL via Access have never been so pleased. Even their trashy SQL runs relatively quickly.

Dick Goulet

____________________Reply Separator____________________
Author: Sam Bootsma <SamB_at_cpas.com>
Date:       4/5/2002 8:49 AM

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.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).