Parameters job_queue_processes and Oracle jobs

Oracle jobs provides great database maintenance convenience for Oracle developers and database administrators. Before Oracle 9i, Oracle jobs were implemented by dbms_jobs, but after 10 Gb, DBMS_Scheduler was added. The two can also add Oracle jobs, but DBMS_Scheduler has more powerful functions. When using Oracle jobs, we have to pay attention to the job_queue_processes parameter, which is used to set the number of processes that can be started in the job queue. This article is about this.

 

1. job_queue_processes Parameters

Alter system set job_queue_processes = 0, and 1000

The following is the description of the 11g Reference:
Job_queue_processes specifies the maximum number of job slaves per instance that can be created for the execution of dbms_job jobs and Oracle scheduler (DBMS_Scheduler) jobs. dbms_job and Oracle scheduler share the same job coordinator and job slaves, and they are both controlled by the job_queue_processes parameter.

If the value of job_queue_processes is set to 0, then dbms_job jobs and Oracle scheduler jobs will not run on the instance. if job_queue_processes is set to a value in the range of 1 to 1000, then dbms_job jobs and Oracle scheduler jobs will run. the actual number of job slaves created for Oracle scheduler jobs is auto-tuned by the scheduler depending on several factors, including available resources, resource manager settings, and currently running jobs. however, the combined total number of job slaves running dbms_job jobs and Oracle scheduler jobs on an instance can never exceed the value of job_queue_processes for that instance. the number of job slaves running Oracle scheduler jobs is additionally limited to the value of the max_job_slave_processes scheduler attribute.

Advanced Replication uses Oracle Scheduler for data refreshes. oracle streams Advanced Queuing uses Oracle schedation for message propagation. materialized views use ORACLE Scheduler for automatic refreshes. setting job_queue_process to 0 will disable these features as well as any other features that use ORACLE schedures or dbms_job.

 

A. From the above description, we can see that for the Oracle job process, including the coordination process (main process) and the slave process (sub process ).
B. The value range of job_queue_processes is 0 to 1000. The total number of job processes that can be created is determined by the job_queue_processes parameter.
C. When job_queue_processes is greater than 1 and the job is executed in parallel, at least one process is the coordination process. The total number does not exceed the value of job_queue_processes.
D. The value of job_queue_processes is that dbms_job is shared with DBMS_Scheduler.
E and job_queue_processes parameters. When this value is set to 0, jobs created in any way will not run.
F. For job_queue_processes with a non-zero value, the number of job sub-processes depends on available resources. The resource configuration method and the number of currently running jobs are adjusted from the row.
G. In addition, the scheduler jobs mode is restricted by the setting of the max_job_slave_processes attribute of scheduler.
H. You can use dbms_scheduler.set_scheduler_attribute to set max_job_slave_processes.

 

2. Test the condition that job_queue_processes is 1.

--> Demo environment SQL> select * from V $ version where rownum <2; banner charts Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit production --> Create test table tb_job (job_name varchar2 (5), update_dt varchar2 (20 )); --> add multiple Oracle jobs to execute SQL concurrently> Ho more add_job.sqldeclare job_name varchar2 (20); begin dbms_output.put_line ('curr Ent sysdate is '| to_char (sysdate, 'yyyymmdd hh24: MI: ss'); for I in 1 .. 5 loop job_name: = 'job _ '| to_char (I); sys. dbms_scheduler.create_job (job_name => job_name, start_date => sysdate + 1/1440, repeat_interval => 'freq = minutely; interval = 1', end_date => null, job_class => 'default _ job_class ', job_type => 'plsql _ Block', job_action => 'inininsert into tb_job select ''' | job_name |'' ', To_char (sysdate, ''yyyymmdd hh24: MI: ss') from dual; dbms_lock.sleep (60); Commit; end;', enabled => true, comments => 'my test job'); End loop; end;/SQL> @ add_jobpl/SQL procedure successfully completed. --> View job_queue_processes parameter value SQL> show parameter jobname type value -------------------------------------------- job_queue_processes integer 1 --> A job-related process is 00sql> Ho PS-Ef | grep rac11g | grep ora_j | grep-V greporacle 9692 1 4? 00:00:01 ora_j000_rac11g1 --> View the newly added job --> The following next_run_date is slightly different in seconds. In fact, when defining a job, this is due to the delay caused by the previous for loop --> ignore this subtle difference here SQL> @ jobsowner job_name enabl job_class next_run_date ------------------ ----- begin when Scott job_1 true default_job_class 19-Mar-13 12.21.33.000000 PM + 08: 00 Scott job2 true default_job_class 19-Mar-13 12.21.33.000000 PM + 08: 00 Scott job_3 true default_job_class 19-Mar-13 12.21.33.000000 PM + 08: 00 Scott job_4 true default_job_class 19-Mar-13 12.21.34.000000 PM + 08: 00 Scott job_5 true default_job_class 19-Mar-13 12.21.34.000000 PM + 08:00 --> job execution, we can see that five jobs are executed one by one --> although the difference in next_run_date is 1 second, at this time, after the job is executed, every difference is 1 minute --> job_1 and job_5 differ by more than 4 minutes. This is because we have defined dbms_lock.sleep (60) for 1 minute --> next we can see that there is only one job process, so each job is executed one by one SQL> select * From tb_job; job_n update_dt ----- ------------------ job_1 20130319 12: 21: 33job_2 20130319 12: 22: 35job_3 20130319 12: 23: 37job_4 20130319 12: 24: 39job_5 20130319 12:25:41

3. Test the condition that the job_queue_processes parameter is greater than 1

--> First remove the previous jobsql> Ho more remove_job.sqldeclare job_name varchar2 (10); begin for I in 1 .. 5 loop job_name: = 'job _ '| to_char (I); sys. dbms_scheduler.drop_job (job_name, force => true); End loop; end;/SQL> @ remove_jobpl/SQL procedure successfully completed. --> set job_queue_processes to 6sql> alter system set job_queue_processes = 6; System altered. --> Empty test table SQL> truncate table tb_job; Table truncated. --> Oracle started two processes for the job. SQL> Ho PS-Ef | grep rac11g | grep ora_j | grep-V greporacle 3477 1 9? 00:00:01 ora_j000_rac11g1oracle 3491 1 4? 00:00:00 ora_j0020.rac11g1 --> add multiple jobsql> @ add_jobpl/SQL procedure successfully completed. --> author: Robinson --> blog: http://blog.csdn.net/robinson_0612SQL> @ jobsowner job_name enabl job_class next_run_date ----------- begin using Scott job_1 true default_job_class 19-Mar-13 12.31.55.20.00 PM + 08: 00 Scott job2-true default_j Ob_class 19-Mar-13 12.31.56.000000 PM + 08: 00 Scott job_3 true default_job_class 19-Mar-13 12.31.56.000000 PM + 08: 00 Scott job_4 true default_job_class 19-Mar-13 12.31.56.000000 PM + 08: 00 Scott job_5 true default_job_class 19-Mar-13 12.31.56.000000 PM + 08:00 --> after a moment, we can see that the total number of job processes reaches 6 SQL> Ho PS-Ef | grep rac11g | grep ora_j | grep -V greporacle 7668 1? 00:00:01 ora_j000_rac11g1oracle 7678 1 0? 00:00:01 ora_j0020.rac11g1oracle 7700 1 1? 00:00:01 ora_j002_rac11g1oracle 9230 1 0? 00:00:00 ora_j003_rac11g1oracle 9257 1 2? 00:00:01 ora_j005_rac11g1oracle 9353 1 7? 00:00:00 ora_j004_rac11g1 --> View tb_job status SQL> select * From tb_job order by 1, 2; job_n update_dt ----- ------------------ job_1 20130319 12: 31: 57job_1 20130319 12: 32: 58job_1 20130319 12: 33: 59job_2 20130319 12: 31: 58job_2 20130319 12: 32: 59job_2 20130319 12: 34: 00job_3 20130319 12: 31: 58job_3 20130319 12: 32: 59job_3 20130319 12: 34: 00job_4 20130319 12: 31: 59job_4 20130319 12: 33: 00job_4 20130319 12: 34: 01job_5 20130319 12: 31: 58job_5 20130319 12: 32: 59job_5 20130319 12:34:00 --> the preceding query results show that the last and next execution interval of each job is basically one minute --> the execution time of each job is basically the same, this is totally different from job_queue_processes when it is set to 1 --> that is to say, even job_5 is basically executed at the same time as job_1, instead of being executed after all the preceding execution as in the previous test --> removing jobsql> @ remove_jobpl/SQL procedure successfully completed. --> Remove test table SQL> drop table tb_job purge; Table dropped.

4. Summary
A. The job_queue_processes parameter determines the total number of processes that a job can use.
B. When this parameter is set to 0, no job will be executed. We recommend that you set this parameter to at least 1.
C. Set the job running time as much as possible.
D. If the number of jobs running at the same time is large and the parameter value is too small, the job has to wait. Excessive parameter values consume more system resources.
F. Merge dependency jobs into a job, such as using chain.

 

For more information, see:

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)