Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: rm RULE based optimizer != GOOD IDEA

RE: rm RULE based optimizer != GOOD IDEA

From: Michael Fontana <>
Date: Mon, 25 Apr 2005 11:01:19 -0500
Message-Id: <>

What we have found in our shop, where we have tons of purchased products and have just implemented Siebel's first CBO release, is that you have to get down to the nuts and bolts details of your problem queries. Once you do, you will often find that your statistics are flawed, which in turn causes a few (sometimes often critically) poor performing queries.

Some examples -

A batch process loads a million rows to a table but doesn't regenerate stats.

Solution: Stats are stale - there is now an option in the optimizer to detect this and regenerate them.

Cardinality stats are not granular enough for a large, key table.

Solution: Run stats for specific tables with parms to selectively drill down and return the correct information for such tables.

It may be a lot of work, but we've been able to run CBO for several major apps (both in-house and third party) including Siebel, SAP, Peoplesoft and Oracle Apps, without resorting to the RBO rule "copout".

And we've had numerous development consultants try to sneak them in.

We simply won't allow it.

Over the long haul, it will cost still more time and money to repair the downstream damage....

-----Original Message-----

From: [] On Behalf Of Post, Ethan
Sent: Friday, April 22, 2005 12:50 PM
To: Lex de Haan;; Pete Sharman; Christian Antognini
Subject: RE: rm RULE based optimizer != GOOD IDEA

If you head over to you might be able to find a video (a month or two ago I think) which is a tour of SQL Server's automated testing facility, it is pretty impressive.

-----Original Message-----

From: Lex de Haan []=20 Sent: Friday, April 22, 2005 12:39 PM
To:; Post, Ethan; 'Pete Sharman'; 'Christian Antognini'
Subject: RE: rm RULE based optimizer !=3D GOOD IDEA

you don't have the faintest idea about the size and complexity of Oracle's
regression tests,
and the frequency they run with ... bug-free software is an utopia.

kind regards,


Visit my website at


-- Received on Mon Apr 25 2005 - 12:05:40 CDT

Original text of this message