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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>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." >