Home » RDBMS Server » Performance Tuning » DBMS_STATS running slow. (Oracle ,10.2.0.4,sun sparc)
DBMS_STATS running slow. [message #421471] Tue, 08 September 2009 10:02 Go to next message
replicated
Messages: 31
Registered: July 2009
Member
Hi

I am running following from crontab to gather stats on our test machine

DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => T_OWNER,
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
GRANULARITY => 'ALL',
METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',
DEGREE => DBMS_STATS.DEFAULT_DEGREE,
CASCADE => TRUE,
OPTIONS => 'GATHER AUTO');

It really takes a huge time to gather the stats and i tried the option (METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT ),it still takes a lot of time.
Also somehow when i run this script it makes dbms_sqlpa,DBMS_SUMADVISOR package invalid.Could it be that these packages are invalid its running slow.After compiling these packages i tried running the script again,it made them again invalid and again took forever to complete.The same script runs fine on Prod database.Any ideas .
Re: DBMS_STATS running slow. [message #421473 is a reply to message #421471] Tue, 08 September 2009 10:06 Go to previous message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>I am running following from crontab to gather stats on our test machine
WHY?

V10.2 automagically collects statistics.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Previous Topic: To limit Cpu consumption
Next Topic: I/O cost was very high
Goto Forum:
  


Current Time: Sun Sep 25 04:24:43 CDT 2016

Total time taken to generate the page: 0.05439 seconds