Hi,
I have a question about the job_queue_processes parameter. Please bear with me on this, it looks like a long description, but it's really a very simple question.
I always thought that job_queue_processes managed jobs in the
scheduler (as well as DBMS_JOBs of course), and indeed from the tests I've done, it does seem to have an impact on scheduler jobs.
Here we have a windows 2003 Standard Edition server with Oracle 10.2.0.4.0 database on it. In the database are various schemas, each with the same set of tables and dbms_scheduler jobs etc (they're all different development schemas based on our one application schema).
If I set job_queue_processes=1, and then query dba_scheduler_running_jobs:
SYS@orcl AS SYSDBA> alter system set job_queue_processes=1;
Sistema modificado.
SYS@orcl AS SYSDBA> select owner, job_name, session_id, elapsed_time from dba_scheduler_running_jobs;
OWNER JOB_NAME SESSION_ID ELAPSED_TIME
------------------------------ ------------------------------ ---------- -------------------------------------------------
SCHEMA1 OUR_JOB_1 520 +000 00:00:03.50
This fairly consistently returns 1 row (sometime no rows). The schema name and job name change, but only 1 row. The session_id is always 520.
Now I set job_queue_processes to something higher (in this case 16) and check running scheduler jobs again:
SYS@orcl AS SYSDBA> alter system set job_queue_processes=1;
Sistema modificado.
SYS@orcl AS SYSDBA> select owner, job_name, session_id, elapsed_time from dba_scheduler_running_jobs;
OWNER JOB_NAME SESSION_ID ELAPSED_TIME
------------------------------ -------------- ---------- ------------------------------------
SCHEMA1 OUR_JOB_1 399 +000 00:00:01.48
SCHEMA3 OUR_JOB_1 372 +000 00:00:05.61
SCHEMA2 OUR_JOB_1 412 +000 00:00:06.13
SCHEMA2 OUR_JOB_2 390 +000 00:00:07.11
SCHEMA1 OUR_JOB_2 381 +000 00:00:02.00
SCHEMA4 OUR_JOB_1 367 +000 00:00:19.25
etc.
16 filas seleccionadas.
It (consistently) returns 16 rows, with various schemas and various jobs all getting a look in now.
That looks fairly conclusive to me; a positive value for the parameter job_queue_processes
does affect jobs running in the scheduler - and this is what I always thought to be case. (With a value of "0" then I see all sorts of results, between 7 and 21 concurrent jobs for example.)
BUT I just read in The 10.2 Administrator's Guide, chapter 28:
Using DBMS_SCHEDULER and DBMS_JOB at the Same Time
...The JOB_QUEUE_PROCESSES initialization parameter is only used for DBMS_JOB.
That sentence seems to contradict what I thought (and my tests). The sentence is repeated in the same document in the next paragraph.
Scheduler Attribute max_job_slave_processes
The initialization parameter JOB_QUEUE_PROCESSES only applies to DBMS_JOB. When DBMS_SCHEDULER is used, the coordinator will automatically determine how many job slaves to start based on CPU load and the number of outstanding jobs.
So, what's going on? Have I misunderstood the documentation?
Regards,
Ados