DBMS_STATS compared to the analyze command: weird!
Rick Denoire
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.
I thought that I had to refine the command. I changed it to:
exec
dbms_stats.gather_schema_stats(ownname=>'blah',cascade=>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
Rick Denoire
Rick Denoire
Niall Litchfield
news:oc6q301tbh2sf7s8d...@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',cascade=>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',cascade=>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',cascade=>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
******************************************
Mark Bole
Niall Litchfield wrote:
> "Rick Denoire" <100....@germanynet.de> wrote in message
> news:oc6q301tbh2sf7s8d...@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
Cookie Monster
> 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.
Syltrem
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---
Daniel Morgan
> 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/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)
Syltrem
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" <damo...@x.washington.edu> a écrit dans le message de
news:1077818071.139873@yasure...
Rick Denoire
>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
Rick Denoire
>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
Rick Denoire
>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.
At times, Oracle performance tuning is like superstition...
I won't begin trying that kind of obscure things without exactly
understanding why it works like it works. It could turn out as a huge
waste of time too.
Bye
Rick Denoire
Syltrem
news:h20t30tq5qo48er6u...@4ax.com...
> I won't begin trying that kind of obscure things without exactly
> understanding why it works like it works. It could turn out as a huge
> waste of time too.
>
Not sure I understand you -- or that you understand me :-)
What I'm saying is:
Restore your db from a time *before* your started using DBMS_STATS and when
stats were fine, save those stats and import them into the production db.
This should be perfectly safe, giving immediate results. Then after you can
start experimenting with DBMS_STATS again.
Mark Bole
> Mark Bole <ma...@pacbell.net> wrote:
[...]
>
>>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
>
I have examined "EXPLAIN PLAN" output (via OEM stand-alone client) while
this command is running (ver 9.2 under Solaris) and it is clear that
internal tables are being updated. I decided I didn't really want to
know about them at the column and row level... ;-)
It is my impression that Oracle cost-based optmizer statistics have
changed quite a bit, with each major version, over the last [n] number
of years, since the decision to retire the RBO was made in the previous
century. I heard that in 10g, there are two separate solutions for
two separate problems:
1) failure to routinely gather fresh statistics - solution: default
regularly scheduled jobs
2) what to do when statistics are missing or suspected inaccurate --
depending on the estimated cost of the query, go out and sample some
statistics on the fly if the cost-benefit trade-off is worth it.
--Mark Bole