RelationalDBDesign RelationalDBDesign


Parallel Server   «Prev  Next»
Lesson 6Controlling job execution with instance affinity
ObjectiveUse 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:
  1. 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.
  2. 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).

Navigate through the following Slide Show to see the steps in creating and running a job using DBMS_JOB to control job execution.
1) The buyer (consumer) buys the digital equivalent of money from
2) A digitized check is encrypted (we will discuss security in a later
3) A consumer enters credit card information on a Web order form

Using-dbms_job Control Job - Execution
The following parameters can be used with the DBMS_JOB.SUBMIT procedure.
  1. WHAT: Required parameter that contains a call to a procedure or some other PL/SQL command to run.
  2. NEXT_DATE: Tells the job queue when to run the job in the background. It defaults to SYSDATE.
  3. INTERVAL: Optional parameter that tells the job queue how often (in days) to run the job.
  4. 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.
  5. 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.
  6. 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.