Release 2 (8.1.6)
Part Number A76956-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Guide Release 2 (8.1.6) Part Number A76956-01 |
|
This chapter describes how to use job queues to schedule the periodic execution of PL/SQL code, and includes the following topics:
To maximize performance and accommodate many users, a multi-process Oracle system uses some additional processes called background processes. Background processes consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. Background processes asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.
SNP background processes execute job queues. You can schedule routines, which are any PL/SQL code, to be performed periodically using the job queue. To schedule a job, you submit it to the job queue and specify the frequency at which the job is to be run. You can also alter, disable, or delete jobs you have submitted.
You must have at least one SNP process running to execute queued jobs in the background. SNP processes periodically wake up and execute any queued jobs that are due to be run. SNP background processes differ from other Oracle background processes, in that the failure of an SNP process does not cause the instance to fail. If an SNP process fails, Oracle restarts it.
SNP background processes will not execute jobs if the system has been started in restricted mode. However, you can use the ALTER SYSTEM statement to turn this behavior on and off as follows:
ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM DISABLE RESTRICTED SESSION;
When you ENABLE a restricted session, SNP background processes do not execute jobs; when you DISABLE a restricted session, SNP background processes execute jobs.
An instance can have up to 36 SNP processes, named SNP0 to SNP9, and SNPA to SNPZ. If an instance has multiple SNP processes, the task of executing queued jobs can be shared across these processes, thus improving performance. Note, however, that each job is run at any point in time by only one process. A single job cannot be shared simultaneously by multiple SNP processes.
Job queue initialization parameters enable you to control the operation of the SNP background processes. When you set these parameters in the initialization parameter file for an instance, they take effect the next time you start the instance.
The JOB_QUEUE_PROCESSES parameter specifies the number of job queue process per instance. Different instances can have different values. This initialization parameter can be altered dynamically using the ALTER SYSTEM statement as shown in the following example.
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 10;
The JOB_QUEUE_INTERVAL parameter specifies the interval between wake ups for the SNP processes. Different instances can have different values.
This section describes the various aspects of managing job queues and includes the following topics:
To schedule and manage jobs in the job queue, use the procedures in the DBMS_JOB package. There are no database privileges associated with using job queues. Any user who can execute the job queue procedures can use the job queue.
The following procedures of the DBMS_JOB package and are included in this section as noted.
| Procedure | Description |
|---|---|
|
SUBMIT |
Submits a job to the job queue. See "Submitting a Job to the Job Queue". |
|
REMOVE |
Removes a specified job from the job queue. See "Removing a Job from the Job Queue" |
|
CHANGE |
Alters a specified job that has already been submitted to the job queue. You can alter the job description, the time at which the job will be run, or the interval between executions of the job. See "Altering a Job". |
|
WHAT |
Alters the job description for a specified job. See "Altering a Job". |
|
NEXT_DATE |
Alters the next execution time for a specified job. See "Altering a Job". |
|
INTERVAL |
Alters the interval between executions for a specified job. See "Altering a Job". |
|
BROKEN |
Disables job execution. If a job is marked as broken, Oracle does not attempt to execute it. See "Broken Jobs" . |
|
RUN |
Forces a specified job to run. See "Forcing a Job to Execute". |
|
See Also:
Syntax information for the DBMS_JOB package can be found in the Oracle8i Supplied PL/SQL Packages Reference. For using the DBMS_JOB package in an Oracle Parallel Server environment, where other options are available, see Oracle8i Parallel Server Administration, Deployment, and Performance. |
To submit a new job to the job queue, use the SUBMIT procedure in the DBMS_JOB package. You specify the following parameters with the SUBMIT procedure:
| Parameter | Description |
|---|---|
|
JOB |
An output parameter, this is the identifier assigned to the job you created. You must use this job number whenever you want to alter or remove the job. See "Job Numbers". |
|
WHAT |
This is the PL/SQL code you want to have executed. See "Job Definitions". |
|
NEXT_DATE |
This is the next date when the job will be run. The default value is SYSDATE. |
|
INTERVAL |
This is the date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL. See "Job Execution Interval". |
|
NO_PARSE |
This is a flag. If NO_PARSE is set to FALSE (the default), Oracle parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE. |
As an example, let's submit a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table DQUON.ACCOUNTS. The statistics are based on a sample of half the rows of the ACCOUNTS table. The job is run every 24 hours.
VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'dbms_ddl.analyze_object(''TABLE'', ''DQUON'', ''ACCOUNTS'', ''ESTIMATE'', NULL, 50);', SYSDATE, 'SYSDATE + 1'); COMMIT; END; / Statement processed. PRINT jobno JOBNO ---------- 14144
When you submit a job to the job queue or alter a job's definition, Oracle records the following environment characteristics:
Oracle also records the following NLS parameters:
Oracle restores these environment characteristics every time a job is executed. NLS_LANGUAGE and NLS_TERRITORY parameters are defaults for unspecified NLS parameters.
You can change a job's environment by using the DBMS_SQL package and the ALTER SESSION statement.
|
See Also:
For more information on the DBMS_SQL package, see the Oracle8i Supplied PL/SQL Packages Reference. For use of the ALTER SESSION statement to alter a job's environment, see Oracle8i SQL Reference. |
Jobs can be exported and imported. Thus, if you define a job in one database, you can transfer it to another database. When exporting and importing jobs, the job's number, environment, and definition remain unchanged.
When you submit a job to the job queue, Oracle identifies you as the owner of the job. Only a job's owner can alter the job, force the job to run, or remove the job from the queue.
A queued job is identified by its job number. When you submit a job, its job number is automatically generated from the sequence SYS.JOBSEQ.
Once a job is assigned a job number, that number does not change. Even if the job is exported and imported, its job number remains the same.
The job definition is the PL/SQL code specified in the WHAT parameter of the SUBMIT procedure.
Normally, the job definition is a single call to a procedure. The procedure call can have any number of parameters.
There are special parameter values that Oracle recognizes in a job definition. These are shown below.
The following are examples of valid job definitions:
'myproc(''10-JAN-99'', next_date, broken);' 'scott.emppackage.give_raise(''JFEE'', 3000.00);' 'dbms_job.remove(job);'
The INTERVAL date function is evaluated immediately before a job is executed. If the job completes successfully, the date calculated from INTERVAL becomes the new NEXT_DATE. If the INTERVAL date function evaluates to NULL and the job completes successfully, the job is deleted from the queue.
If a job should be executed periodically at a set interval, use a date expression similar to 'SYSDATE + 7' in the INTERVAL parameter. For example, if you set the execution interval to 'SYSDATE + 7' on Monday, but for some reason (such as a network failure) the job is not executed until Thursday, 'SYSDATE + 7' then executes every Thursday, not Monday.
If you always want to automatically execute a job at a specific time, regardless of the last execution (for example, every Monday), the INTERVAL and NEXT_DATE parameters should specify a date expression similar to 'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'')'.
Below are shown some common date expressions used for job execution intervals.
If you submit a job that uses a database link, the link must include a username and password. Anonymous database links will not succeed.
SNP background processes execute jobs. To execute a job, the process creates a session to run the job.
When an SNP process runs a job, the job is run in the same environment in which it was submitted and with the owner's default privileges.
When you force a job to run using the procedure DBMS_JOB.RUN, the job is run by your user process. When your user process runs a job, it is run with your default privileges only. Privileges granted to you through roles are unavailable.
Oracle uses job queue locks to ensure that a job is executed one session at a time. When a job is being run, its session acquires a job queue (JQ) lock for that job. You can use the locking views in the data dictionary to examine information about locks currently held by sessions.
The following query lists the session identifier, lock type, and lock identifiers for all sessions holding JQ locks:
SELECT sid, type, id1, id2 FROM v$lock WHERE type = 'JQ'; SID TY ID1 ID2 --------- -- --------- --------- 12 JQ 0 14144 1 row selected.
In the query above, the identifier for the session holding the lock is 12. The ID1 lock identifier is always 0 for JQ locks. The ID2 lock identifier is the job number of the job the session is running. This view can be joined with the DBA_JOBS_RUNNING view to obtain more information about the job. See "Viewing Job Queue Information" for more information about views.
When a job fails, information about the failure is recorded in a trace file and the alert log. Oracle writes message number ORA-12012 and includes the job number of the failed job.
The following can prevent the successful execution of queued jobs:
If a job returns an error while Oracle is attempting to execute it, Oracle tries to execute it again. The first attempt is made after one minute, the second attempt after two minutes, the third after four minutes, and so on, with the interval doubling between each attempt. When the retry interval exceeds the execution interval, Oracle continues to retry the job at the normal execution interval. However, if the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it.
Thus, if you can correct the problem that is preventing a job from running before the job has failed sixteen times, Oracle will eventually run that job again.
|
See Also:
For more information about the locking views, see the Oracle8i Reference. For more information about locking, see Oracle8i Concepts. |
To remove a job from the job queue, use the REMOVE procedure in the DBMS_JOB package.
The following statement removes job number 14144 from the job queue:
DBMS_JOB.REMOVE(14144);
To alter a job that has been submitted to the job queue, use the procedures CHANGE, WHAT, NEXT_DATE, or INTERVAL in the DBMS_JOB package.
You can alter any of the user-definable parameters associated with a job by calling the DBMS_JOB.CHANGE procedure.
In this example, the job identified as 14144 is now executed every three days:
DBMS_JOB.CHANGE(14144, null, null, 'SYSDATE + 3');
If you specify NULL for WHAT, NEXT_DATE, or INTERVAL when you call the procedure CHANGE, the current value remains unchanged.
You can alter the definition of a job by calling the DBMS_JOB.WHAT procedure.
The following example changes the definition of the job identified as 14144:
DBMS_JOB.WHAT(14144, 'scott.emppackage.give_raise(''RBAYLIS'', 6000.00);'
You can alter the next date that Oracle executes a job by calling the DBMS_JOB.NEXT_DATE procedure, as shown in the following example:
DBMS_JOB.NEXT_DATE(14144, 'SYSDATE + 1');
The following example illustrates changing the execution interval for a job by calling the DBMS_JOB.INTERVAL procedure:
DBMS_JOB.INTERVAL(14144, 'NULL');
In this case, the job will not run again after it successfully executes.
A job is labeled as either broken or not broken. Oracle does not attempt to run broken jobs. However, you can force a broken job to run by calling the procedure DBMS_JOB.RUN.
When you submit a job it is considered not broken.
There are two ways a job can break:
DBMS_JOB.BROKEN(14144, TRUE)
Once a job has been marked as broken, Oracle will not attempt to execute the job until you either mark the job as not broken, or force the job to be executed by calling the procedure DBMS_JOB.RUN.
The following example marks job 14144 as not broken and sets its next execution date to the following Monday:
DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
If a problem has caused a job to fail 16 times, Oracle marks the job as broken. Once you have fixed this problem, you can run the job by either:
If you force the job to run by calling the procedure DBMS_JOB.RUN, Oracle runs the job immediately. If the job succeeds, then Oracle labels the job as not broken and resets its count of the number of failed executions for the job.
Once you reset a job's broken flag (by calling either RUN or BROKEN), job execution resumes according to the scheduled execution intervals set for the job.
There may be times when you would like to manually execute a job. For example, if you have fixed a broken job, you may want to test the job immediately by forcing it to execute. To force a job to be executed immediately, use the procedure RUN in the DBMS_JOB package.
When you run a job using DBMS_JOB.RUN, Oracle recomputes the next execution date. For example, if you create a job on a Monday with a NEXT_DATE value of 'SYSDATE' and an INTERVAL value of 'SYSDATE + 7', the job is run every 7 days starting on Monday. However, if you execute RUN on Wednesday, the next execution date will be the next Wednesday.
The following statement runs job 14144 in your session and recomputes the next execution date:
DBMS_JOB.RUN(14144);
You can terminate a running job by marking the job as broken, identifying the session running the job, and disconnecting that session. You should mark the job as broken, so that Oracle does not attempt to run the job again.
After you have identified the session running the job (via V$SESSION), you can disconnect the session using the SQL statement ALTER SYSTEM.
For examples of viewing information about jobs and sessions, see the following section, "Viewing Job Queue Information".
You can view information about jobs in the job queue via the data dictionary views listed below:
For example, you can display information about a job's status and failed executions. The following sample query creates a listing of the job number, next execution time, failures, and broken status for each job you have submitted:
SELECT job, next_date, next_sec, failures, broken FROM user_jobs; JOB NEXT_DATE NEXT_SEC FAILURES B ------- --------- -------- -------- - 9125 01-NOV-98 00:00:00 4 N 14144 24-OCT-99 16:35:35 0 N 41762 01-JAN-00 00:00:00 16 Y 3 rows selected.
You can also display information about jobs currently running. The following sample query lists the session identifier, job number, user who submitted the job, and the start times for all currently running jobs:
SELECT sid, r.job, log_user, r.this_date, r.this_sec FROM dba_jobs_running r, dba_jobs j WHERE r.job = j.job; SID JOB LOG_USER THIS_DATE THIS_SEC ----- ---------- ------------- --------- -------- 12 14144 JFEE 24-OCT-94 17:21:24 25 8536 SCOTT 24-OCT-94 16:45:12 2 rows selected.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|