Statistics collection

From Oracle 10g onwards, Oracle has included a job that automatically collects optimizer statistics for us. So, there is no real need to make such a process again. It might look like inventing a different-colored wheel, but it can be useful if you want more control on how the statistics are collected.

For this, we will make a simple package named sched_stats. As many readers are more than likely to be still using Oracle 10g, the code that we will use is compatible for Oracle 10g as well as 11g. The package has the following four procedures:

• schedule_run

• run

• statob

• Drop_jobs

The schedule_run procedure

The Schedule_run procedure creates the job that is actually scheduled on a twice-daily basis and has all the parameters with defaults that can be overridden:

procedure schedule_run
( jobclass in varchar2 default 'DEFAULT_JOB_CLASS',
jobname in varchar2 default 'run_stats',
sjobprefix in varchar2 default 'stats_',
repinterval in varchar2 default'FREQ=DAILY;BYHOUR=5,17;BYMINUTE=4...