Hello

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',cas cade=>true,degree=>4).

Right on Monday, people were complaining that jobs run about 50 times
SLOWER than before. Measurable.

I thought that I had to refine the command. I changed it to:
exec
dbms_stats.gather_schema_stats(ownname=>'blah',cas cade=>true,degree=>4,
granularity=>'ALL',method_opt=>'for all columns size
10',option=>'GATHER').

So I would collect statistics for partitioned tables and histograms
too. But I did not see several processes running in parallel (as usual
before) and examined what was running. I discovered that Oracle
actually executes the old analyze command instead, transforming the
dbms_stats command to:

analyze table xxx compute statistics for all tables for all.... etc.

Weird, isn't? Now explain me all that, if you can.

Business is jammed and the whole company is waiting for me to "do the
d.... statistics" (it takes about two days). I have tried the
monitoring option for tables and the GATHER STALE option for the
dbms_stats command, was not that cute.

Bye
Rick Denoire

This is all on Oracle 8.1.7/Solaris Enterprise, I forgot to add.

Rick Denoire

"Rick Denoire" <100.17706@germanynet.de> wrote in message
newsc6q301tbh2sf7s8d8tcnr6c6hkrqvnmre@4ax.com...
> Hello

Hi
>
> 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',cas cade=>true,degree=>4).
>
> Right on Monday, people were complaining that jobs run about 50 times
> SLOWER than before. Measurable.

so jobs that took 1 minute were taking 1 hour? not good.

>
> I thought that I had to refine the command. I changed it to:
> exec
> dbms_stats.gather_schema_stats(ownname=>'blah',cas cade=>true,degree=>4,
> granularity=>'ALL',method_opt=>'for all columns size
> 10',option=>'GATHER').

leaving aside the move to degree=4, you have moved from analyze table XXX
compute statistics; to analyze table compute statistics for table for all
columns size 10; or in other words from no histograms to one of 10 buckets
on every column. it isn't entirely surprising that the performance is
different.

an exact equivalent of what you had previously would be (I'm pretty sure)
dbms_stats.gather_schema_stats(ownname=>'blah',cas cade=>false,degree=>4).you
might omit the degree


> So I would collect statistics for partitioned tables and histograms
> too. But I did not see several processes running in parallel (as usual
> before) and examined what was running. I discovered that Oracle
> actually executes the old analyze command instead, transforming the
> dbms_stats command to:

changes in 9.

>
> analyze table xxx compute statistics for all tables for all.... etc.
>
> Weird, isn't? Now explain me all that, if you can.

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
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

Niall Litchfield wrote:

> "Rick Denoire" <100.17706@germanynet.de> wrote in message
> newsc6q301tbh2sf7s8d8tcnr6c6hkrqvnmre@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',c ascade=>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

"Rick Denoire" <100.17706@germanynet.de> wrote in message
news:vm7q30p25ct7enu9dhcg1c7739ek7nnhb7@4ax.com...
> This is all on Oracle 8.1.7/Solaris Enterprise, I forgot to add.
>
> Rick Denoire
>
>

Hi,

As a point of interest we had a consultant from Oracle in for a few days to
tune our 8.1.7 database on solaris and he told us NOT to use the DBMS_STATS
package to analyze the scheme. He pointed to a number of bugs and problems
and suggested we continue to use the analyze table method for statistic
gathering (seems problems are resolved in later releases). I do this using
a stored procedure. Maybe you want to consider that??

Regards,
Cookie.

Also note that you can save your old stats, with DBMS_STATS.
So if something bad happens, you can retrieve them (very quickly) and you're
back to your old stats and response time.

I use this feature for one table where old stats make queries run faster,
for some reason. I have a TAR open on this. Newly calculated stats on this
particular table makes everything so much slower.

If you can manage to restore your (old backup) db somewhere, save the stats,
import them to your prod db, and re-apply them. That may or may not be
quicker to doing the analyze again... you're the only one who can tell.

HTH

--
Syltrem

OpenVMS 7.3-1 + Oracle 8.1.7.4
http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
---zulu is not in my email address---

Cookie Monster wrote:

> As a point of interest we had a consultant from Oracle in for a few days to
> tune our 8.1.7 database on solaris and he told us NOT to use the DBMS_STATS
> package to analyze the scheme. He pointed to a number of bugs and problems
> and suggested we continue to use the analyze table method for statistic
> gathering (seems problems are resolved in later releases). I do this using
> a stored procedure. Maybe you want to consider that??
>
> Regards,
> Cookie.

I've seen no evidence that your consultant's advice is correct. Can
you point to anything specific?

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

There is a (generic) bug for which I installed a patch:

Patch 1407738
Description DBMS_STATS.GATHER_SCHEMA_STATS GIVES ORA-6502 WITH STALE/EMPTY

It does not mean the stats are incorrect or that one should stay away from
DBMS_STATS though.

--
Syltrem

OpenVMS 7.3-1 + Oracle 8.1.7.4
http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
---zulu is not in my email address---
"Daniel Morgan" <damorgan@x.washington.edu> a écrit dans le message de
news:1077818071.139873@yasure...
> Cookie Monster wrote:
>
> > As a point of interest we had a consultant from Oracle in for a few days
to
> > tune our 8.1.7 database on solaris and he told us NOT to use the
DBMS_STATS
> > package to analyze the scheme. He pointed to a number of bugs and
problems
> > and suggested we continue to use the analyze table method for statistic
> > gathering (seems problems are resolved in later releases). I do this
using
> > a stored procedure. Maybe you want to consider that??
> >
> > Regards,
> > Cookie.
>
> I've seen no evidence that your consultant's advice is correct. Can
> you point to anything specific?
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/e...ad/oad_crs.asp
> http://www.outreach.washington.edu/e...oa/aoa_crs.asp
> damorgan@x.washington.edu
> (replace 'x' with a 'u' to reply)
>

"Niall Litchfield" <niall.litchfield@dial.pipex.com> wrote:

>leaving aside the move to degree=4, you have moved from analyze table XXX
>compute statistics; to analyze table compute statistics for table for all
>columns size 10; or in other words from no histograms to one of 10 buckets
>on every column. it isn't entirely surprising that the performance is
>different.

You didn't get my message right. The performance problem appeared when
using a more simple form of the dbms_stats command, without
histograms.

>changes in 9.

Funny that Oracle discourages the use of the analyze command and still
keeps it internally.
>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.

I did not ask to explain my own command to me ;-)

My question is about the impact of using different methods for
gathering statistics, which Oracle itself shuffles at its will.

Bye
Rick Denoire

Mark Bole <makbo@pacbell.net> wrote:


>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).

4 CPUs. Of course the issue is about performance at execution time of
regular queries and transactions.

>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...)

dbms_stats is "real SQL", it even has an execution plan ;-)
while analyze is a kind of special utility.

>exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => upper('schema_owner'), -
> degree => DBMS_STATS.DEFAULT_DEGREE, -
> cascade => true, -
> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE );

Will this command be translated to an analyze command by Oracle?

Bye
Rick Denoire