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

From: Shastry(DBA) <"Shastry>
Date: Mon, 9 Feb 2009 15:02:01 +0530
Message-ID: <5b294dab0902090132q4498661fjcb5874bef9b10e64_at_mail.gmail.com>

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 Mon Feb 09 2009 - 03:32:01 CST