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

.

Relevant Pages

  • Re: Please! Doesnt anyone know a better way to do this?
    ... account, they need to automatically be directed to the page to enter data ... session variable on the Account page. ... I assume here that you're checking a database when the user attempts to ... When a new user attempts to login or clicks to register, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Retrieving state information from a middle tier
    ... Now this very first call can make session root entry into an xml file like ... We have a middle tier which is made up ... > The current implementation only allows for one database to be served up. ... > longer use the middle tier as the source of the connection properties. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Horizontal scaling - advice needed
    ... the session can be unambiguously proxied to the right backend server, ... To start with have a single database machine. ... Full database clustering is challenging, but if your site is making you lots ... For transient session state, ...
    (comp.lang.ruby)
  • Re: VirtualPathProvider and Application Restart
    ... the session state to the Database Server and move a lot of lightweight ... to unload it from the AppDomain. ... When you update an ASPX file, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: form to email document
    ... >> append the Session ID for that user Session to the file name. ... >> name uploaded.This can come from a simple text file or a database. ... >>> I was hoping for something like the procedure used to upload a form, ...
    (microsoft.public.frontpage.programming)