Re: DBMS_STATS compared to the analyze command: weird!

From: Mark Bole <makbo_at_pacbell.net>
Date: Thu, 26 Feb 2004 04:05:24 GMT
Message-ID: <8ge%b.17289$VS.12593@newssvr29.news.prodigy.com>

Niall Litchfield wrote:

> "Rick Denoire" <100.17706_at_germanynet.de> wrote in message
> news:oc6q301tbh2sf7s8d8tcnr6c6hkrqvnmre_at_4ax.com...

>

>>Last weekend, I finnaly switched the weekly script to gather
>>statistics from the analyze command to the dbms_stats package.
>>
>>Instead of
>>analyze table xxx compute statistics"
>>I did
>>exec
>>dbms_stats.gather_schema_stats(ownname=>'blah',cascade=>true,degree=>4).
>>
>>Right on Monday, people were complaining that jobs run about 50 times
>>SLOWER than before. Measurable.

>

>[...]

>
> well you have said method_opt=> for all columns... all that means is collect
> histograms on all columns with the size I specify - hence the equivalence to
> the analyse I had above. Your original statement didn't collect histograms,
> the dbms_stats one did - for every column.
>
>
> cheers
> Niall Litchfield

How many CPU's do you have on this server (how did you pick "degree=>4"?) The dbms_stats package can be quite a resource hog while it's running on large tables in parallel. Was the original performance slow-down seem while the gathering was going on, or afterward? ( the former is a hardware bottleneck, the latter is an optimizer issue).

As a side note, it is always a good idea to test a change like this first before rolling to production on a Monday morning, but you know that now... ;-)

Try something like the following, if you have a one or more schemas of primary interest, under Oracle 9.2. My guess is it will use more resources per minute, for a shorter overall duration, than a single-threaded "analyze" statement, but nevertheless dbms_stats is Oracle's stated future direction (under version 10g with dynamic sampling, all is forgiven...)

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => upper('schema_owner'), -

          degree => DBMS_STATS.DEFAULT_DEGREE, -
          cascade => true, -
          estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE  );

--Mark Bole Received on Wed Feb 25 2004 - 22:05:24 CST