Question: How can I have system run and collect
dbms_stats statistics on a daily basis?
Answer: Starting in Oracle 10g, Oracle has
automatic statistics collection. You also have the
"alter table tablename monitoring".
The
dbms_stats.gather_system_stats procedure is especially
useful for
multi-mode Oracle shops that run OLTP during the day and
DSS at night. You invoke the
dbms_stats.gather_system_stats procedure as an elapsed
time capture, making sure to collect the statistics during a
representative heavy workload:
execute
dbms_stats.gather_system_stats('Start');
-- xxx delay
during high workload
execute
dbms_stats.gather_system_stats('Stop');
To
see the execution schedule you can run these queries:
select
state
from
dba_scheduler_jobs
where
job_name =
'GATHER_STATS_JOB';
'select *
from
dba_autotask_client
where
client_name = "auto optimizer stats collection";
