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

From: Mohammed Shakir <mshakir08816_at_yahoo.com>
Date: Fri, 05 Apr 2002 18:53:18 -0800
Message-ID: <F001.0043DBB6.20020405185318@fatcity.com>

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 <SamB_at_cpas.com> 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: 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).

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: mshakir08816_at_yahoo.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 - 20:53:18 CST