Operating systems such as UNIX/Windows provide a facility called a cron program, which is used to execute certain tasks at specified times and/or repeated at regular intervals at the operating system level. This facility is very useful to run commands on a regularly scheduled basis, to perform periodic operations, to run programs at night for fear of slowing down other users if it were run during the day, and so forth. The Cron is executed at the operating system level.
You need a similar way of scheduling jobs in the database. This article explains job scheduling in the database with an example in detail.
Sometimes, you need to execute certain operations at predefined times and/or repeated at regular intervals. This happens exclusively in the database.
If you use cron to schedule the jobs for executing database-related tasks, you need to embed passwords in script files. Most operating systems’ implemented facility has a number of drawbacks; for example, it won’t check whether the database is running or not when the job starts. How do you prevent the job running again if something is seriously wrong? You need a facility to solve these kinds of drawbacks.
Oracle incorporated a new facility to run stored procedures, or “jobs,” at specified times and frequencies as background processes. The major advantage of this new facility is that it solves most of the previously mentioned problems in a simple, straightforward manner. The database itself is responsible for invoking the scheduled procedures, so if the database is down at the scheduled time, no matter; the procedure will be started when the database is restarted. No need to reveal the critical password. The procedure runs with the privileges of the user that schedules it. If you don’t want it run again, simply set the job status to “broken.” It won’t be run again until the status is changed.
Oracle Job Queue
The Oracle job queue allows for the scheduling and execution of PL/SQL routines (jobs) at predefined times and/or repeated job execution at regular intervals. Oracle provides a built-in package DBMS_JOB to schedule the jobs. The DBMS_JOB package is actually an API into an Oracle subsystem known as the job queue. The DBMS_JOB package is created when the Oracle database is installed.
The DBMS_JOB package has EXECUTE privileges with public access. All Oracle users can reference and use this package.
There are several data dictionary views that display information about the Oracle job queue. These are called DBA_JOBS, USER_JOBS, and DBA_JOBS_RUNNING.
Before you can schedule job execution, you need to make sure that the database is set up to process jobs. One important thing is that the job queue must have its dedicated background processes started to process the jobs. This is accomplished by setting an initialization parameter, job_queue_processes, in the INIT.ORA file for the database.
If the job is not running properly once it is scheduled, the first thing you need to do is check the number of job queue processes available to execute jobs. For that, you check the job_queue_processes initialization parameter. The following SQL statement displays the job_queue_processes initialized value.
SQL> show parameter job_queue_processes NAME TYPE VALUE ———————————— ———– —————— job_queue_processes integer 100
This parameter should be set higher than the maximum number of simultaneous jobs you expect to run. The maximum limit is 1000.
If this parameter is set to 0, jobs will not be processed. If you want to start processing jobs, the parameter can be set dynamically with the ALTER SYSTEM command.
ALTER SYSTEM SET job_queue_processes = 100;
This command enables job queue processing by starting the CJQ0 process. Similarly, you can disable all job queue processing and stop the CJQ0 process by setting the parameter to 0.
ALTER SYSTEM SET job _queue_processes = 0;
Changes to the parameter with the ALTER SYSTEM command will be in effect only until the database is restarted. You should make changes in init or spfile if you want the changes to be permanent.
The parameter is:
JOB_QUEUE_PROCESSES = n
where n is a number between 1 and 36.
The following is the list of procedures defined for the DBMS_JOB packages.
- BROKEN: Marks the job as broken; do not re-execute
- CHANGE: Changes job parameters that can be set by user
- CHECK_PRIVS: Checks the privileges
- INTERVAL: Changes execution interval for job
- ISUBMIT: Submits a new job specifying job number
- NEXT_DATE: Changes next execution date for the job
- REMOVE: Removes existing job from the queue
- RUN: Runs the job immediately in the current session
- SUBMIT: Submits a new job, obtaining a new job number
- USER_EXPORT: Creates text of call to recreate a job
- WHAT: Changes PL/SQL executed for job
How to define the job
The programs in DBMS_JOB share a set of parameters that define jobs, their execution times, and frequency of execution. All of the DBMS_JOB procedures manipulate one or more of these parameters:
- Job parameter: The job parameter is an integer that uniquely identifies the job. It can be selected either by the user or automatically assigned by the system. The DBMS_JOB.SUBMIT procedure automatically assigns the job number by obtaining the next value from the sequence SYS.JOBSEQ. It is returned as an OUT parameter so the caller can subsequently identify the job submitted. DBMS_JOB.ISUBMIT allows the user to assign a specific unique integer identifier to the job.
Job numbers cannot be changed other than by removing and resubmitting the job. The job number is retained even when the database is exported and imported.
- What parameter: The what parameter is a character string that evaluates to a valid PL/SQL call to be executed automatically by the job queue. You must enclose the what parameter in single quotes if you are using a string literal. Alternatively, you can use a VARCHAR2 variable containing the desired string value. The actual PL/SQL call must be terminated with a semicolon.
what => 'begin proc1 (param1); end;'
- next_date parameter: next_date specifies the next execution date of the job. This parameter defaults to SYSDATE in both the DBMS_JOB.SUBMIT and BROKEN procedures, indicating that the job should be run immediately. Whenever a NULL value is passed for the next_date parameter, the next execution date for the job is set to January 1, 4000. This effectively keeps the job from being executed without removing it from the job queue.
- interval parameter: The interval parameter is a character string representing a valid Oracle date expression. This date expression is evaluated each time the job begins execution. When a job completes successfully, this date becomes the next execution date for the job.
declare v_repeat_interval number; ---- interval=>'sysdate+'||to_char(v_repeat_interval) ||'*(1/24/60/60)')
- broken parameter: Flags job as broken and not to be executed. If the job fails 16 times, the job is marked as broken.
For example, suppose that a procedure called proc1 has the following specification:
Suppose also that you submit proc1 to be executed by the job queue as follows:
DECLARE jobno INTEGER; BEGIN DBMS_JOB.SUBMIT (job =>jobno, What => 'begin proc1; end ;', next_date => SYSDATE, interval => 'SYSDATE+1/24'); COMMIT; END; /
The above job will be started immediately after submission and repeats at every one hour.
you can find out the job number from the USER_JOBS dictionary view by executing the following query.
SQL> Select job from user_jobs where what='begin proc1; end ;' ;
In telecom infrastructure applications, service is invoked by thousands of subscribers every second. In such cases, the telecom operator wants to monitor the performance and raise alerts based on some threshold settings. Assume that the application populates the service invocation details in a table. The access gateway calculates the start_time and end_time for each request to the service operation. Also, assume that the database table is populated as shown in the following table:
|Broker_IP_ Address||application_ name||service_ name||operation_ name||total_ request||success_ count||err_ count||start_ time||end_ time|
|10.14.88.22||App1||LocationSvc||getLocation||100||50||50||2007-9-18.14.25. 30. 149000000||2007-9-18.14.26. 30. 153000000|
|10.14.88.23||App2||SMSService||sendSMS||50||35||15||2007-9-18.14.26. 30. 153000000||2007-9-18.14.27. 30. 146000000|
|10.14.88.24||App2||SMSService||sendSMS||100||100||0||2007-9-18.14.26. 30. 153000000||2007-9-18.14.27. 30. 146000000|
You need to aggregate all one minute data from the above mentioned table and put this information into another table, PM_MEDIATION_TB. This mediated data will be used for further comparison with the threshold and raise appropriate alerts when the metrics exceed threshold values.
The PM_MEDIATION_TB table has the following structure.
The above task is independent of other on-line systems and happens exclusively within the database. This task can be scheduled as a database job. The database job has its own control table to take care of failures for rerun independent of the scheduler table managed by Oracle. This control table contains the data window that is independent of the run window.
The function is required to run only on a timed cycle or at a denoted time. The DB job is akin to a batch process that is data-centric and can crunch efficiently through large volumes offline without affecting the on-line systems such as the access gateway.
The DB Job uses the following control table to maintain the timestamp. The DB job should start from specific time. This timestamp will be maintained in the Prev_timestamp column. When the DB job is started for the first time, the control table will be empty. When the job runs for the first time, you should get the min (start_time) from the PERFORMANCE_METRICS_TB as the prev_timestamp for the DB job to run.
The control table is given as below
|1||pr_AggregateMetrics||2007-9-18.14.25. 30. 149000000|
When the procedure “pr_AggregateMetrics” runs for the first time, it takes the prev_timestamp = min(start_time) from PERFORMANCE_METRICS_TB.
You also can configure the data collection interval and repeat interval in the table PM_DELTA_CONFIG. Assume that the application populates these data during startup.
|pr_AggregateMetrics||60 (in secs)||60 (in secs)|
The database job will take prev_timestamp from the CONTROL_TB table. The end time is prev_timestamp + DATA_COLLECTION_INTERVAL. Once the job is successfully executed, it will update prev_timestamp with the end_time. The DB job calculates the no_of_chunks of data to be processed as
No_of_chunks = REPEAT_INTERVAL / DATA_COLLECTION_INTERVAL
The db job processes the “n” minute data when it wakes up every time. This “n” minute is configured as “data_collection_interval” in the table PM_DELTA_CONFIG.
The following is the PL/SQL procedure that aggregates the data from Performance_Metrics_TB and populates the aggregated data into PM_MEDIATION_TB.
PROMPT Creating Package Specification For pkg_PerformanceMetrics CREATE OR REPLACE PACKAGE pkg_PerformanceMetrics AS -- Declaring the User Defined Exceptions in the Package Level. PM_Data_Not_Available_Excp EXCEPTION; PRAGMA EXCEPTION_INIT(PM_Data_Not_Available_Excp,-20100); Large_Data_Window_Excp EXCEPTION; PRAGMA EXCEPTION_INIT(Large_Data_Window_Excp,-20101); -- procedure declaration procedure pr_AggregateMetrics; END pkg_PerformanceMetrics; / PROMPT Creating Package Body For pkg_PerformanceMetrics CREATE or REPLACE package body pkg_PerformanceMetrics IS PROCEDURE pr_AggregateMetrics AS -- Declaring and Initializing the Variables V_Err_Msg varchar2(4000); V_Err_Code varchar2(100); V_Err_Date DATE:=sysdate; V_Proc_Name varchar2(50):= 'pr_AggregateMetrics'; v_currenttime timestamp ; v_prevtimestamp timestamp ; v_datacollection_interval number; v_repeat_interval number; v_noofchunks number; v_start_time timestamp; v_commit_cnt number := 0; v_Metrics_found boolean := false; v_min_time timestamp; v_reccount number; -- Defining the Cursor with parameters previous time and current -- time cursor PM_MetricsInfo_cur(v_prevtimestamp IN timestamp, v_currenttime IN timestamp) is select application_name,service_name, operation_name,start_time,end_time, sum(total_request) as total_request, sum(success_count)as success_count, sum(err_count) as failure_count , (sum(err_count)+sum(success_count))/sum(total_request) as availability from PERFORMANCE_METRICS_TB where start_time>=to_timestamp(to_char(v_prevtimestamp, 'dd-mm-yyyy hh24:mi.ss.'),'dd-mm-yyyy hh24:mi.ss.') and to_timestamp(to_char(end_time,'dd-mm-yyyy hh24:mi.ss.'), 'dd-mm-yyyy hh24:mi.ss.')<=v_currenttime group by application_name,service_name,operation_name, start_time,end_time; BEGIN -- Getting the data_collection_interval,repeat interval from -- PM_DELTA_CONFIG BEGIN select DATA_COLLECTION_INTERVAL, REPEAT_INTERVAL into v_datacollection_interval, v_repeat_interval from PM_DELTA_CONFIG where PROCESS_NAME='pr_AggregateMetrics'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(V_Err_Date ||'—'|| V_Proc_Name ||'—'|| 'Configuration details are missing in PM_DELTA_CONFIG'); raise; WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(V_Err_Date ||'—'|| V_Proc_Name ||'—'|| 'Exact fetch returns more than one row. Please check PM_DELTA_CONFIG tables'); raise; END; IF (v_datacollection_interval>v_repeat_interval) THEN V_Err_Msg:='Data Window is larger than the repeat interval'; --DBMS_OUTPUT.PUT_LINE(sysdate ||'—'|| V_Proc_Name ||'—'|| V_Err_Msg); raise_application_error(-20101, 'Data Window is larger than the repeat interval'); END IF; -- Finding out the number of chunks of data v_noofchunks := v_repeat_interval/v_datacollection_interval; For i in 1..v_noofchunks Loop Begin Begin -- Get the previous timestamp from the control table for this -- procedure "pr_AggregateMetrics" select prev_timestamp into v_prevtimestamp from CONTROL_TB where process_name ='pr_AggregateMetrics'; Exception WHEN TOO_MANY_ROWS THEN V_Err_Msg:='Exact fetch returns more than one row.. Check the CONTROL_TB for PROCESS_NAME= pr_AggregateMetrics1'; --DBMS_OUTPUT.PUT_LINE(sysdate ||'—'|| V_Proc_Name ||'—'|| V_Err_Msg); raise; WHEN NO_DATA_FOUND THEN BEGIN -- No data found in control table. -- Getting the min(start_time) from PERFORMANCE_METRICS_TB -- for the first run. select min(start_time) into v_start_time from PERFORMANCE_METRICS_TB; -- Updating the Control table with the start time. IF (v_start_time is not null) THEN insert into CONTROL_TB(process_ID,process_name, prev_timestamp) values(1,'pr_AggregateMetrics1',v_start_time); commit; v_prevtimestamp := v_start_time; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN V_Err_Msg:='Performance Metrics data is not yet populated'; raise_application_error(-20100,'Performance Metrics data is not yet populated'); END; End; v_currenttime:= to_timestamp(to_char(v_prevtimestamp + v_datacollection_interval*(1/24/60/60), 'dd-mm-yyyy hh24:mi:ss.')||to_char(to_number (to_char(v_prevtimestamp,'FF'))+250), 'dd-mm-yyyy hh24:mi:ss.FF'); FOR Metric_rec in PM_MetricsInfo_cur(v_prevtimestamp,v_currenttime) LOOP BEGIN -- DBMS_OUTPUT.PUT_LINE(sysdate ||'—'|| V_Proc_Name ||'—'||' -- Processing record with service name ' || -- Metric_rec.service_name); v_Metrics_found := true; SELECT count(*) into v_reccount FROM PM_MEDIATION_TB WHERE service_name=Metric_rec.service_name and application_name= Metric_rec.application_name and operation_name = Metric_rec.operation_name and start_time=Metric_rec.start_time and end_time=Metric_rec.end_time; IF (v_reccount=0) THEN insert into PM_MEDIATION_TB (service_name, application_name, operation_name, start_time, end_time, tot_req, success_count,failure_count, availability) values(Metric_rec.service_name, Metric_rec.application_name, Metric_rec.operation_name, Metric_rec.start_time, Metric_rec.end_time, Metric_rec.total_request, Metric_rec.success_count, Metric_rec.failure_count, Metric_rec.availability ); ELSE UPDATE PM_MEDIATION_TB SET tot_req=Metric_rec.total_request, success_count=Metric_rec.success_count, failure_count=Metric_rec.failure_count, availability=Metric_rec.availability WHERE service_name=Metric_rec.service_name and application_name= Metric_rec.application_name and operation_name = Metric_rec.operation_name and start_time=Metric_rec.start_time and end_time=Metric_rec.end_time; END IF; END; END LOOP; -- If the metrics are collected successfully, -- update the prevtimestamp into the CONTROL_TB IF (v_Metrics_found) THEN UPDATE CONTROL_TB SET prev_timestamp=v_currenttime WHERE PROCESS_NAME='pr_AggregateMetrics'; END IF; -- Making the DB changes permanent COMMIT; IF (not v_Metrics_found) THEN select min(start_time) into v_min_time from PERFORMANCE_METRICS_TB where start_time>v_prevtimestamp; -- dbms_output.put_line('start_time : ' ||v_min_time -- || ' v_prevtimestamp : '||v_prevtimestamp); IF (v_min_time is not null) THEN UPDATE CONTROL_TB SET prev_timestamp=v_min_time WHERE PROCESS_NAME='pr_AggregateMetrics'; COMMIT; END IF; END IF; END; v_Metrics_found := false; End Loop; -- for i loop EXCEPTION WHEN TOO_MANY_ROWS THEN V_Err_Msg :=SQLERRM; V_Err_Date:=sysdate; --DBMS_OUTPUT.PUT_LINE (sysdate ||'—'|| V_Proc_Name ||'—'|| --V_Err_Msg); WHEN NO_DATA_FOUND THEN V_Err_Msg :=SQLERRM; V_Err_Date:=sysdate; ROLLBACK; --DBMS_OUTPUT.PUT_LINE (sysdate ||'—'|| V_Proc_Name ||'—'|| --V_Err_Msg); WHEN PM_Data_Not_Available_Excp THEN V_Err_Msg :='Performance Data is not available'; V_Err_Date:=sysdate; --DBMS_OUTPUT.PUT_LINE (sysdate ||'—'|| V_Proc_Name --||'—'|| V_Err_Msg); WHEN Large_Data_Window_Excp THEN V_Err_Msg:=''; WHEN OTHERS THEN V_Err_Msg :=SQLERRM; ROLLBACK; --DBMS_OUTPUT.PUT_LINE (sysdate ||'—'|| V_Proc_Name --||'—'||V_Err_Msg); END pr_AggregateMetrics; END pkg_PerformanceMetrics; /
Once the procedure is created successfully, you need to schedule the job by submitting the job into the Oracle Job Queue. The following is the PL/SQL code to schedule the job.
DECLARE v_jobno number; v_repeat_interval number; BEGIN BEGIN SELECT repeat_interval INTO v_repeat_interval FROM PM_DELTA_CONFIG WHERE process_name='pr_AggregateMetrics'; EXCEPTION WHEN NO_DATA_FOUND THEN v_repeat_interval := 60; END; dbms_job.submit( job =>v_jobno, what =>'begin pkg_PerformanceMetrics.pr_AggregateMetrics; end;', next_date =>sysdate+15*(1/24/60/60), -- start at 15 secs -- from sysdate interval=>'sysdate+'||to_char(v_repeat_interval)||'* (1/24/60/60)'); -- wakes up every 60 seconds -- (based on v_repeat_interval) COMMIT; END;
Once the job is submitted into the job queue, the job starts 15 seconds from the sysdate for the first time and repeats every 60 seconds thereafter.
I hope this article helps you understand more about the Oracle job queue sub system and its usage in scheduling and executing PL/SQL routines at predefined times and/or repeated job execution at regular intervals.
About the Author
Jeyarani Venkatasamy holds a master’s degree in Computer Applications from Manonmaniam Sundaranar University, India. She has worked as an Associate System Analyst in the Communications, Media and Entertainment division, Hewlett-Packard, Bangalore for more than three years involving Web services, Web Service Testing tools and J2EE technologies. The domain of her experience includes telecom, web services, XML, Java, and J2EE-related technologies. She can be reached at [email protected].