Re: DBMS_STATS compared to the analyze command: weird!

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:oc6q301tbh2sf7s8d8tcnr6c6hkrqvnmre_at_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
******************************************