Lesson 6	Controlling job execution with instance affinity
Objective	Use the `DBMS_JOB` package to define instance affinity.

Controlling Job Execution with Instance Affinity

When using OPS for batch jobs, there are times when your job only needs to run using a single instance. For example, perhaps you have a job that gathers sales statistics for a particular district office, whose data is all stored within a single database instance. Limiting the job to run on one instance can cut overhead costs because you can prevent the job from interacting with other instances that do not have any job data. Limiting a job to a single instance is called instance affinity^[1] and is a new feature in Oracle8i. Do not confuse this with disk affinity, which is another feature of OPS that we will discuss in the next lesson.

Two Initialization Parameters

Before you can run a batch job using DBMS_JOB package, you must adjust these two initialization parameters:

JOB_QUEUE_PROCESSES. This parameter defaults to zero. Increase it to one or more to enable jobs to run in the background. It can be a maximum of 36. Setting it higher than one causes the database to spawn multiple symmetric multiprocessing (SMP) background processes that can each run batch jobs simultaneously.
JOB_QUEUE_INTERVAL. This parameter defaults to 60 seconds, which means that the SMP background process wakes up every 60 seconds to start any background jobs scheduled to run at that time. You may leave this as is, or adjust it to be anywhere from 1 to 3,600 seconds (30 minutes).

Using DBMS_JOB to control Job Execution

The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package. In particular, if you are administering jobs to manage system load, you should consider disabling DBMS_JOB by revoking the package execution privilege for users.

The DBMS_JOB package schedules and manages jobs in the job queue.

Security Model

No specific system privileges are required to use DBMS_JOB. No system privileges are available to manage DBMS_JOB. Jobs cannot be altered or deleted other than jobs owned by the user. This is true for all users including those users granted DBA privileges.
You can execute procedures that are owned by the user or for which the user is explicitly granted EXECUTE. However, procedures for which the user is granted the execute privilege through roles cannot be executed.
Note that, once a job is started and running, there is no easy way to stop the job.

Navigate through the following series of images to see the steps in creating and running a job using DBMS_JOB to control job execution.

1) DBMS Job 1 — 1) Here is an example of submitting a job using DBMS_JOB. Notice the parameter called instance.
This contains the number of the instance in the OPS that is allowed to run this job.

2) DBMS Job 2 — 2) The USER_JOBS data dictionary view stores information about jobs in the job queue. Here is a query that shows the job that we created.

3) DBMS Job 3 — 3) To remove a job from the job queue, use the DBMS_JOB.REMOVE procedure. Here, we are removing the job we created earlier.

The following parameters can be used with the DBMS_JOB.SUBMIT procedure.

WHAT: Required parameter that contains a call to a procedure or some other PL/SQL command to run.
NEXT_DATE: Tells the job queue when to run the job in the background. It defaults to SYSDATE.
INTERVAL: Optional parameter that tells the job queue how often (in days) to run the job.
NO_PARSE: FALSE by default, meaning the string inside the WHAT parameter is parsed when you submit the job. You can set it to TRUE so the string is not parsed.
INSTANCE: Optional parameter that tells the job queue in which instance of the OPS the job runs. If this parameter is left out,the job runs on any instance.

FORCE: Optional Boolean parameter that is set to FALSE by default. This means that if the specified instance is not available, the job queue may select another instance.

The next lesson covers disk affinity.

Controlling job Execution - Exercise

Click the Exercise link below to practice executing DBMS_JOB in SQL*Plus to run job execution controls.
Controlling Job Execution - Exercise

[1]Instance affinity: A feature of DBMS_JOB package that limits a job in OPS to one database instance.