Oracle: Automatic Statistics Collection

I have been reading about the new features in Oracle10g. One of the nice add-ons is the out-of-the-box automatic statistics collection.
This means one less script to run externally from the OS, whether it is cron or AT or whatever.

DBMS_SCHEDULER is a PL/SQL package which provides a complete scheduling system. You can have groups of windows of schedules. This provides plenty of flexibility to choose when and what you want run on the database. Plus given the some other packages (FILE_TRANSFER) this could include any log shipping, ETL or other tasks as well. I haven't tested this functionality yet, but it is on the to-do list.

So I checked out what scheduler_jobs where scheduled to run using this SQL

-- List scheduler jobs

column "Name" format a30
column "Start Date" format a21
column "Next Run" format a21

select owner||'.'||job_name "Name",
to_char(start_date, 'Mon DD HH24:MI:SS') "Start Date",
to_char(next_run_date, 'Mon DD HH24:MI:SS') "Next Run",
enabled,state,run_count,last_run_duration
from dba_scheduler_jobs
order by owner,job_name;

I found the in-built PURGE_LOGS and GATHER_STATS_JOB scheduled.
GATHER_STATS_JOB runs the package DBMS_STATS.GATHER_DATABASE_STATS.

However the GATHER_STATS_JOB didn't have a next_run_date, which was weird. So off to the metalink.oracle.com to check what was the story with the missing next_run_date. Not much, one forum question with a response to raise an iTAR.

Instead of rushing to log an iTAR I thought it might be more useful to check out how the GATHER_STATS_JOB was meant to be schedule.
The job is created during install through the running of the script in $ORACLE_HOME/rdbms/admin/catmwin.sql.
This script creates the job, two schedule windows, a schedule group and a schedule resource group.
I checked the various DBA_SCHEDULER_ views to check that everything was there and it was, I also dropped and recreated using the catmwin.sql script. Still the job had no next_run_date.

So I decided to create a new schedule called NIGHTLY which will run at 10pm each night. I assigned that schedule to the GATHER_STATS_JOB. Here is the PL/SQL

BEGIN
dbms_scheduler.create_schedule(
schedule_name=>'NIGHTLY',
start_date=>sysdate+7/24,
repeat_interval=>'freq=daily;byhour=22;byminute=0;bysecond=0',
end_date=>NULL,
comments=>'Nightly window for maintenance task');
dbms_scheduler.set_attribute('GATHER_STATS_JOB','SCHEDULE_NAME','NIGHTLY');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/

Note: You will have to set the start_date to some future date. I set it to 9.30pm (as it was 2.30pm when I run this).

I checked the scheduler_jobs again and bingo. The GATHER_STATS_JOB now has a next_run_date which is set to start at 10pm.
It would seem that the start_date information was not being resolved from the various windows within the window group.

Hopefully this will help you if you want to get the statistics collection happening faster.

This is just scratching the surface of the power of this package. It is used by several different packages within Oracle and suggests plenty of flexibility and power to perform database tasks.

Have Fun

Oracle
DBMS_SCHEDULER