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

  • From: "Shastry(DBA)" <shastry17@xxxxxxxxx>
  • To: Harshan Vasudevan Eppurath <harshan.eppurath@xxxxxxxxxxxxx>
  • Date: Tue, 10 Feb 2009 13:46:06 +0530

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@xxxxxxxxxxxxx> wrote:

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

Other related posts: