DBMS_STATS compared to the analyze command: weird!

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',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 Received on Wed Feb 25 2004 - 16:11:21 CST