Re: gather stats slows performance?

From: Mark Bole <makbo_at_pacbell.net>
Date: Sat, 07 Aug 2004 23:36:41 GMT
Message-ID: <dIdRc.4389$oR3.195@newssvr29.news.prodigy.com>

Ana C. Dent wrote:

> streib_at_cs.indiana.edu (Allan Streib) wrote in 
> news:e334e4a7.0408071154.5171332a_at_posting.google.com:
> 
> 

>>Oracle 9i R2.
>>
>>Application is an ASP app that has a fairly long history. There is
>>virtually no use of stored procs, and most of the queries are simply
>>static sql passed to Oracle via ODBC (i.e. no bind variables). I know
>>this is not ideal but we can't fix Rome in a day. Given this is the
>>case, I noticed that every time I look at a query plan it is using the
>>RBO. So I think that we probably don't have statistics in our schema.
>> (I now know how to check for that, but did not before I tried the
>>following)
>>
>>dbms_stats.delete_schema_stats
>>dbms_stats.gather_schema_stats (using defaults for all the options)
>>
>>After stats were gathered, queries now seem to be using the CBO. A
>>few particularly slow queries are now much faster, but overall,
>>application responsiveness has slowed. To confirm this, I deleted
>>stats and the overall performance picked back up while the few
>>troublesome queries are slow again.
>>

[...]

> 
> WRT the CBO vs. the RBO, which is "better"?
> It depends.
> It depends upon many factors.
> 
> Maybe by using OUTLINEs you can have the best of both.

Try using the non-default setting for the "CASCADE" option of dbms_stats.gather_schema_stats(). Check to see if your indexes have any statistics (which setting CASCADE => TRUE will accomplish).

As mentioned, OUTLINES (and individual HINTS) are useful to force plan stability once you find one you like (such as the one you have under RBO).

And don't assume statistics are the only factor involved in CBO operation. More memory for sorting can cause the CBO to favor sort-based joins over other kinds, for example.

In 10g, RBO is gone anyway, so you will have no choice. But on the plus side, there are also better, more automated tools for ensuring "good" statistics.

In Metalink search for Note 199083.1, there's lot's of information including a step-by-step query tuning decision tree (Note 233112.1).

--Mark Bole Received on Sat Aug 07 2004 - 18:36:41 CDT