Re: DBMS_STATS - Global Statistics

Donna wrote:
>
> Has anyone experienced really slow completion time when trying to execute
> DBMS_STATS for GLOBAL STATISTICS? Are there ways to speed up the process?
>
> Platform:
> Oracle 8.1.7.2
> Solaris 8 - UNIX / 4 CPU machine
> Implementation - Star Schema Datamart with Partitioned Fact Tables
>
> I have a table with 50 partitions each of approx. 7 million rows. Each
> partition has partition level statistics on them and I am trying to generate
> GLOBAL STATISTICS. So far it is taking days to complete. I got as far as
> 8 partitions analyzed in a 48 hour period. Here is my script:
> (I have a line for each partition. Any ideas?)
>
> DBMS_STATS.GATHER_TABLE_STATS ('CPG','SORT_ANALOG','SA_P45',50,false,
> 'FOR ALL COLUMNS SIZE 1',4,'DEFAULT',true);
>
> Thanks
> Donna

If you turn a trace on you'll see what is taking all of the time. dbms_stats does a lot of sorting - cranking up the sort area size might give some benefit. Another option is just to lie and use dbms_stats to set (rather than gather) the stats.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."