Oracle Built-in Packages

Job Queue Architecture

The job queue is really a subsystem within an Oracle database, which uses dedicated background processes and catalog tables to execute user PL/SQL procedures automatically without user intervention. It is useful to get a good conceptual understanding of the job queue, because some of the behavior of this queue is not obvious. Figure Figure 13.1 shows a schematic of the job queue architecture.

Figure 13-1. Schematic of job queue architecture

INIT.ORA Parameters and Background Processes

These three INIT.ORA parameters are instrumental in controlling the job queue:

JOB_QUEUE_PROCESSES

JOB_QUEUE_INTERVAL

JOB_QUEUE_KEEP_CONNECTIONS

JOB_QUEUE_PROCESSES

The job queue (or SNP^[18]) background processes are started when the Oracle instance is started. There are as many SNP processes started as specified in the INIT.ORA parameter JOB_QUEUE_PROCESSES. The range of valid values is from 0 to 36, so there can be a maximum of 36 SNP processes per Oracle instance. Under most operating systems, the characters SNP will appear as part of the process name. For example, under UNIX, an Oracle instance called DEV with three job queue processes would show the following process names:

ora_DEV_snp0

ora_DEV_snp1

ora_DEV_snp2

One significant difference between the SNP background processes and other Oracle background processes is that killing an SNP process will not crash the instance. While you’re ...