Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

dbms_stats.gather_schema_stats running for long hours

991835Feb 19 2013 — edited Feb 19 2013
Hi,
We are trying to run dbms_stats.gather_schema_stats procedure on our production DB. The parameters passed are as below.

p_schema_name=<OURSCHEMANAME>
p_gather_stats_cd=SCHEMA
p_estimate_percent=
p_degree=
p_method_opt=
p_cascade=
p_options= GATHER STALE

The above parameters are passed to below proc.

If p_gather_stats_cd='SCHEMA' then
dbms_stats.gather_schema_stats (
ownname => p_schema_name,
estimate_percent => nvl(p_estimate_percent,DBMS_STATS.AUTO_SAMPLE_SIZE),
method_opt => nvl(p_method_opt,'FOR ALL COLUMNS SIZE AUTO'),
degree => nvl(p_degree,DBMS_STATS.DEFAULT_DEGREE),
cascade => nvl(p_cascade,DBMS_STATS.AUTO_CASCADE),
options => p_options,
granularity => 'ALL'
);
end if;


The schema stats gathering is taking long hours to run. Mostly all default parameters are passed.
It took 7 hours last week and 10 hours this week to gather entire schema stats?
How can we reduce the time taken to run the stats gather job?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 19 2013
Added on Feb 19 2013
7 comments
3,956 views