dbms_stats.gather_schema_stats running for long hours
991835Feb 19 2013 — edited Feb 19 2013Hi,
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?