This discussion is archived
5 Replies Latest reply: Dec 10, 2012 4:53 AM by Richard Harrison . RSS

Gathering stats

user9077483 Newbie
Currently Being Moderated
Hi Experts,

I have used the following statement to gather the stats of a table..

EXEC dbms_stats.gather_table_stats('APP','SALE_AMT',ESTIMATE_PERCENT=>100,CASCADE=>True);

To gather the stats it is taking one hour and the table SALE_AMT is having 237213998 records.

Please help me is there any way to get the stats quickly.

Thanks in advance.
  • 1. Re: Gathering stats
    971895 Journeyer
    Currently Being Moderated
    Per my understanding there is no other option...
  • 2. Re: Gathering stats
    Justin Cave Oracle ACE
    Currently Being Moderated
    You're asking for a 100% sample on a table with 237 million rows and gathering statistics for all the dependent objects. An hour seems pretty reasonable for that.

    - How often are you gathering statistics? It seems unlikely that a table this large changes enough to justify gathering statistics all that frequently.
    - Is the table partitioned? Could it be? Assuming this is part of a load process, perhaps you really only need to gather statistics on the most recently loaded partition
    - Do you really need a 100% sample? Could you use a smaller sample size (or use the automatic sample size)? Or did you encounter problems using something less than a 100% sample?

    Justin
  • 3. Re: Gathering stats
    Think_dba Journeyer
    Currently Being Moderated
    user9077483 wrote:
    Hi Experts,

    I have used the following statement to gather the stats of a table..

    EXEC dbms_stats.gather_table_stats('APP','SALE_AMT',ESTIMATE_PERCENT=>100,CASCADE=>True);

    To gather the stats it is taking one hour and the table SALE_AMT is having 237213998 records.

    Please help me is there any way to get the stats quickly.

    Thanks in advance.
    You will surely need to decrease the sample parameter.
  • 4. Re: Gathering stats
    moreajays Pro
    Currently Being Moderated
    Hi,

    Run this it will use auto sampling & will reduce time without impacting performance

    EXEC dbms_stats.gather_table_stats('APP','SALE_AMT',CASCADE=>True,no_invalidate => false);


    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 5. Re: Gathering stats
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Try
    EXEC dbms_stats.gather_table_stats('APP','SALE_AMT',ESTIMATE_PERCENT=>100,CASCADE=>True,degree=8);

    where 8 is the degree of parallelism (this should be set depending on the number of your cpu's).

    Cheers,
    Harry

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points