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

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: