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".
dbms_stats.gather_system_stats procedure is especially
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:
-- xxx delay
during high workload
see the execution schedule you can run these queries:
client_name = "auto optimizer stats collection";