Shulou(Shulou.com)06/01 Report--

Research background

Because the call failed when testing the task combined with procedure+dbms_job (it seems that the task didn't run). After querying the database parameters, it is found that the value of JOB_QUEUE_PROCESS is 0.

Research content

The main sources of this survey data are Oracle official documents and MOS, showing the information related to JOB_QUEUE_PROCESS parameters in the form of entries. Let's take a look at the entries shown.

The role of parameters

JOB_QUEUE_PROCESS specifies the maximum number of subtasks that each instance of the Oracle database can create when performing DBMS_JOB and DBMS_SCHEDULER tasks.

The range of values allowed for the parameter

In versions 12.1 and earlier, the default values allowed for parameters were 0 to 1000. In versions 12.2 and 18c, the default values allowed for parameters are 0 to 4000.

Default values of parameters in different versions

In 9i and 10g, the default value is 0, in 11g and 12cR1, the default is 1000, and in 12cR2 and 18c, the default is 4000.

Here is very embarrassing, who changed my database parameters to 0 (database version is 11.2.0.4)

The method of modifying parameter values

The parameter value of job_queue_processes can be modified dynamically. The default is scope=both.

Alter system set job_queue_processes=

In the RAC database, execute the command

Alter system set job_queue_processes= sid='*'

When the parameter value is 0

In 10g and 11gR1, setting JOB_QUEUE_PROCESSES to 0 only causes the DBMS_JOB job to fail to run, but the DBMS_SCHEDULER job is not affected and will still run.

Starting with 11gR2, setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to fail to run.

Setting JOB_QUEUE_PROCESS to 0 disables the following features and any other features or features that use Oracle Scheduler or DBMS_JOB:

Advanced replication (Advanced Replication) uses Oracle Scheduler for data refresh.

Oracle AQ (Oracle Streams Advanced Queuing) uses Oracle Scheduler for message propagation.

Materialized views (Materialized Views) are automatically refreshed using Oracle Scheduler.

Note: the Oracle database overrides the job queue setting in upgrade mode to disable scheduling tasks. Therefore, you do not need to change the parameter settings when upgrading the Oracle database.

Ensure the rationality of the parameters

If you need to set JOB_QUEUE_PROCESSES to a low value, you should consider the following factors:

Ideally, the JOB_QUEUE_ processes value should be greater than all concurrent jobs running in the database. It's a good idea to make JOB_QUEUE_PROCESSES greater than the total number of jobs scheduled (using scheduler + dbms_job). This will ensure that even if some jobs exceed their normal running duration, we will not run out of JOB_QUEUE_PROCESSES.

In addition, scheduler jobs generated by the following methods may also incur corresponding process overhead:

Any advanced replication (Advanced Replication) refresh

Oracle AQ streaming message transmission

Materialized view (Materialized Views) refresh

DBMS_REDEFINITION data Table redefinition

In addition, there may be temporary jobs that can be performed in the database either manually or through some third-party tools. JOB_QUEUE_PROCESSES should also be satisfied with this.

Parameter characteristics of Multi-tenant Database

In 12.1.0.1, job_queue_process is a container database (CDB) modifiable parameter (at the global level).

In 12.1.0.2, the job_queue_process parameter is not CDB modifiable; on the contrary, it is PDB modifiable, but each pdb cannot correctly use its own job_queue_ processes value.

Since 12.2, JOB_QUEUE_PROCESSES is PDB modifiable:

If job_queue_processes in ROOT is 0, disable Scheduler in all PDB, including ROOT.

If job_queue_processes is set to 0 in a specific PDB other than ROOT, Scheduler is disabled only in that PDB.

Survey results

If you want to ensure that the system peak Oracle can also run a variety of tasks, then you need to set the JOB_QUEUE_PROCESS parameter to a reasonable value. The combination of "technical means acquisition" and "empirical judgment" is a good solution.

Readers can also follow the author's official Wechat account "IT Technical Cuisine" and communicate with the author.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.