Oracle provides the package DBMS_JOB(Pre 10g) and DBMS_SCHEDULER(Post 10g) to allow for the automation of pre-scheduled routine tasks in the Oracle database, just as they are performed by CRONTAB on UNIX and the Scheduler on Windows. This article will explain the use of DBMS_JOB and DBMS_SCHEDULER to take automatic snapshots for the STATSPACK utility every two hours. It is important to realize that STATSPACK stores information related to system performance in the database, thus the more often you take snapshots, the great resources you will be consuming.

1. The DBMS_JOB package contains several procedures and functions for creation, modification and deletion of scheduled jobs for Oracle 9i and earlier versions. For Oracle 10g and higher versions use DBMS_SCHEDULER package, which provides for greater flexibility and control over job creation. A complete listing of the procedures and functions can be retrieved in SQL*PLUS with the command desc for both packages.

2. Use the SUBMIT procedure to create a new job to execute. The below statement will execute once every two hours.

For 9i or earlier use dbms_job.submit.

variable jobno number;

exec dbms_job.submit(job=>:jobno, what=>’begin statspack.snap;
end;’,next_date=>sysdate,interval=>’sysdate+1/12′);

For 10g or higher use dbms_scheduler.create_job (NOTE: start_date is a timestamp not a date value, so use the following query to determine the timestamp format for your database ()

begin

DBMS_SCHEDULER.CREATE_JOB (job_name => ‘PERSTAT_SNAP’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘statspack.snap;’,
start_date => ’02-AUG-09 07.00.00.000000 PM -04:00′,
repeat_interval => ‘FREQ=hourly; interval=2’,enabled => TRUE,
comments => ‘Gather perstat snapshots every two hours’);

end;
/

3. You can view the jobs and all details by querying the dictionary views of DBA_JOBS for DBMS_JOBS and DBA_SCHEDULER_JOBS for DBMS_SCHEDULER.

DBMS_JOBS:

SQL> select job, what, to_char(next_date, ‘DD-MON-YY HH:MI:SS’) from dba_jobs where what like ‘%snap%’;

JOB WHAT TO_CHAR(NEXT_DATE,
— —— ——————
244 begin statspack.snap; end; 05-AUG-09 01:02:01

DBMS_SCHEDULER:

SQL> select job_name, job_action, to_char(NEXT_RUN_DATE, ‘DD-MON-YY HH:MI:SS’) from dba_scheduler_jobs where job_name=’PERSTAT_SNAP’; 2

JOB_NAME JOB_ACTION TO_CHAR(NEXT_RUN_D
———- ———— ——————
PERSTAT_SNAP statspack.snap 05-AUG-09 04:11:28

SQL>

4. In DBMS_SCHEDULER, you can view the last execution of your job to verify successful execution or

SQL> select STATUS, ACTUAL_START_DATE from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name=’PERSTAT_SNAP’;

STATUS ACTUAL_START_DATE
——– ————————–
SUCCEEDED 05-AUG-09 03.20.20.250514 PM -04:00

SQL>

5. Dropping jobs.

SQL> select job, what from dba_jobs where what like ‘%snap%’;

JOB WHAT
—— ——–
244 begin statspack.snap; end;

SQL> exec dbms_job.remove(244);
PL/SQL procedure successfully completed.
SQL> select job, what from dba_jobs where what like ‘%snap%’;
no rows selected
SQL>

10g and higher

exec dbms_scheduler.DROP_JOB(job_name=>’PERSTAT_SNAP’);
SQL> exec dbms_scheduler.DROP_JOB(job_name=>’PERSTAT_SNAP’);
PL/SQL procedure successfully completed.
SQL>

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com