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

Dan,

I would post something along the lines of

"upgrade to a supported version"
"use the cbo across the board"
"my eggs are too runny"

but I'm sure that others here will do that for me.

10g R1 allows for statistics to be locked for a segment (table, index). That of course does you no good being on 8i. 8i Enterprise Edition allows for the use of stored outlines. 8i Standard Edition does not (check v$option - ymmv). I believe that 9i R2 allows for stats to be saved off to a table, and be used at the session-level. Again that does you no good in 8i.

> I also have some custom queries that behave much better when using the cost based optimizer

Ah - now this is a session-based deal.
Perhaps a logon trigger for this user account could lend a hand.

It could call a procedure that would:
- gather stats for the segments of interest if they do not exist - alter the optimizer goal for the session to use the CBO

(first one in, pays the price of having to wait for stats to be gathered)

After the routine completes, you'd execute your statements as you normally would.

A logoff trigger would then call a procedure (in the same package as above) that would:
submit a dbms_job to delete the stats gathered above at some point in the future - this way the session could end without awaiting the stats to be removed. It also means that several sessions the same day would use the same set of stats. No logons that day means no stats to delete.

Or you could just upgrade a copy of the database to a version unsupported by the vendor in testing and see for yourself what works - and hammer them to support the newer, supported version of the Oracle database server software that uses the CBO exclusively (yeah, right) and actually has some security fixes created for it and thrown over the wall perhaps 4 times a year.

Perhaps you could get your auditors and/or security staff to insist upon using a supported version?

-bdbafh Received on Tue Sep 12 2006 - 16:51:47 CDT