Re: Analyze is taking lot of time for a set of tables in particular schema

From: qihua wu <staywithpin_at_gmail.com>
Date: Fri, 13 Feb 2009 11:45:29 +0800
Message-ID: <2689c1070902121945m6073fc85td3e8dc70aa26f24e_at_mail.gmail.com>

Did any tables grow a lot? Did you set sort area size in the session? If a sort that could be done in memory in the past but need temporary tablespace as the table grows, performance will degrade.

When the table got truncated and populated again, whether re-gather stats depends on
1: the data volume are the nearly the same for each repopulate for each table?
2: Any column whose value increase/decease all the time? such as a column based on a sequence? If there are such columns, you need the correct highest/lowest value by re-gather stats.

Thanks,
Daniel.

On Tue, Feb 10, 2009 at 4:16 PM, Shastry(DBA) <shastry17_at_gmail.com> wrote:

> Thanks,
> Could anyone please suggest how these analyze and dbms_stats differs and
> why it is taking so much time, because I have read dbms stats are much
> efficient than analyze, does indexes matters in analyze?
>
> For your information, the activity includes pulling data from some other
> database and inserted in to a stage tables and once the activity(process) is
> completed the table gets truncated. The gather stats will be done while the
> insert of data is completed, but it is taking more than expected. i believe
> that once the stage table gets truncated, there needs a fresh new stats, is
> that true please correct and hence it is taking more time.
>
> Thanks,
> Ann
>
>
> On Mon, Feb 9, 2009 at 4:36 PM, Harshan Vasudevan Eppurath <
> harshan.eppurath_at_capgemini.com> wrote:
>
>> Hi Ann,
>>
>>
>>
>> As far as I know there is no need for deleting statistics before
>> generating it.
>>
>>
>>
>> Regards,
>>
>> Harshan
>>
>>
>> ------------------------------
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Shastry(DBA)
>> *Sent:* Monday, February 09, 2009 3:02 PM
>> *To:* Syed Jaffar Hussain
>> *Cc:* oracle-l
>> *Subject:* Re: Analyze is taking lot of time for a set of tables in
>> particular schema
>>
>>
>>
>> Should I drop the statistics and then I need to run freshly dbms_stats or
>> what do you suggest? Sorry If I am wrong.
>>
>>
>>
>> Thanks,
>>
>> Ann
>>
>> On Mon, Feb 9, 2009 at 1:08 PM, Syed Jaffar Hussain <
>> sjaffarhussain_at_gmail.com> wrote:
>>
>> Oh man. You are collecting 100% stats, and if the table size is larger, it
>> gonna take time.
>>
>> I strongly recommend you to use dbsm_stats with estimate percent 10%, that
>> would be good to start with.
>>
>>
>>
>> You can use the following command to use dbms_stats with 10% statistics
>> collection.
>>
>>
>>
>> exec
>> dbms_stats.gather_tablespace('USERNAME','TABLENAME',estimate_percent=>10);
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jaffar
>>
>>
>>
>> On Mon, Feb 9, 2009 at 10:33 AM, Shastry(DBA) <shastry17_at_gmail.com>
>> wrote:
>>
>> Hello Syed,
>>
>>
>>
>> Thanks for the quick reply.
>>
>>
>>
>> Here are the details:
>>
>>
>>
>> Oracle database version : 9.2.0.6
>>
>> OLTP database
>>
>>
>>
>> I am using ANALYZE TABLE <table_name> COMPUTE STATISTICS;
>>
>>
>>
>> Please advice.
>>
>>
>>
>> Thanks,
>>
>> Ann
>>
>>
>>
>> On Mon, Feb 9, 2009 at 12:26 PM, Syed Jaffar Hussain <
>> sjaffarhussain_at_gmail.com> wrote:
>>
>> Very insufficient information provided. What is your db version? are you
>> collecting 100% stats? What is the size of your db?
>>
>> is it OLTP or DW? Database?
>>
>> Why don't you use dbms_stast rather than analyze?
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jaffar
>>
>>
>>
>> On Mon, Feb 9, 2009 at 9:51 AM, Shastry(DBA) <shastry17_at_gmail.com> wrote:
>>
>> Hi all,
>>
>>
>>
>> Analyze is taking too much time on particular schema(PROD database), i see
>> the latest time stamp in last_analyzed column. But these days the gather
>> stats is taking too much time. Any workarounds on this please. It will
>> be grateful.
>>
>>
>>
>> Thanks in advance,
>>
>> Ann
>>
>>
>>
>> --
>> Best Regards,
>>
>> Syed Jaffar Hussain
>> Oracle Certified Master (10g)
>> http://www.oracle.com/technology/ocm/shussain.html
>> Oracle ACE
>>
>> http://apex.oracle.com/pls/otn/f?p=19297:4:1579866181463918::NO:4:P4_ID:126
>> OCP 8i,9i & 10g DBA
>> RAC Certified Expert
>> Official Oracle RAC SIG Representative for Saudi Arabian region (
>> http://www.oracleracsig.org/)
>> I blog at
>> http://jaffardba.blogspot.com/
>> --------------------
>> "Winners don't do different things. They do things differently."
>>
>>
>>
>>
>>
>>
>> --
>> Best Regards,
>>
>> Syed Jaffar Hussain
>> Oracle Certified Master (10g)
>> http://www.oracle.com/technology/ocm/shussain.html
>> Oracle ACE
>>
>> http://apex.oracle.com/pls/otn/f?p=19297:4:1579866181463918::NO:4:P4_ID:126
>> OCP 8i,9i & 10g DBA
>> RAC Certified Expert
>> Official Oracle RAC SIG Representative for Saudi Arabian region (
>> http://www.oracleracsig.org/)
>> I blog at
>> http://jaffardba.blogspot.com/
>> --------------------
>> "Winners don't do different things. They do things differently."
>>
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 12 2009 - 21:45:29 CST