Re: Statistics and the rule based optomizer

Dan Dubinsky wrote:
> Hi All,
>
> I have an Oracle 8.1.7 database and it is configured to use the RULE
> based optomizer. There is a product called GERS using the database that
> requires this.
>
> I also have some custom queries that behave much better when using the
> cost based optomizer. I use the query hint /*+ALL_ROWS*/ or
> /*+FIRST_ROWS*/ to speed these up, but before these work properly I
> have to run ANALYZE TABLE UPDATE STATISTICS on the tables in the query.
>
> The problem is that this GERS product has a script that specifically
> deletes stats each night.
>
> I want to turn this script off, but I'm not sure why it would be there
> in the first place. Would there be any reason why having statistics
> updated on some of the tables in a database would cause trouble for
> queries using the rule based optimizer.
>
> Thanks in advance,
> Dan

LOL! Sorry, but I used to be in the same boat. I can't tell you how many queries I tuned and would send to our TP software vendor showing the CBO produced better results.

The bottom line is if they *require* the RBO for support, you may be fighting an uphill battle. I would research and produce some test cases that show the CBO produces better results (if it in fact, does).

In terms of the stats deletion, it sounds like they are paranoid. If the RBO is set to be the optimizer mode used for building execution plans, the absence or existence of stats will not impact those query execution plans.

Vendors such as that drive me bonkers. As I noted, I would produce test cases (during the day when stats are there) complete with traces that shows them where their thinking may be out of line.

Regards,

Steve Received on Tue Sep 12 2006 - 15:46:06 CDT