Hi Ann, As far as I know there is no need for deleting statistics before generating it. Regards, Harshan ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@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."