Oracle job scheduler problem

From: Steve <no_at_anon>
Date: Tue, 26 Mar 2002 12:09:21 -0000
Message-ID: <1017144474.15583.0.nnrp-01.c2d92da9@news.demon.co.uk>

In a nutshell jobs are not running until I adjust number of job_queue_processes using alter system.

Details:

I have the following parameters set:

job_queue_processes = 3
job_queue_interval = 60

Start the database, then in a SQL*Plus session I issue a job as follows:

sql> var jn number;
sql> execute dbms_job.submit(:jn, 'DO_STATS;', sysdate + 2 / 1440, 'sysdate + 2 / 1440');

I won't actually have do_stats running every two minutes, this is just for test purposes at the moment.

Selecting from dba_jobs I get

JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC FAILURES WHAT INTERVAL
---- --------- -------- --------- -------- --------- -------- ---------- ---

----------------- --------------------
  41                                       26-MAR-02 11:59:59
DO_STATS;         sysdate + 2 / 1440

Current Date Time is

TO_CHAR(SYSDATE,'DD-

---

26-mar-2002 11:58:33

I wait for a few minutes:

TO_CHAR(SYSDATE,'DD-

---

26-mar-2002 12:01:59

Check dba_jobs again

  JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC FAILURES

WHAT                 INTERVAL

----- --------- -------- --------- -------- --------- -------- ---------- --

------------------ --------------------
   41                                       26-MAR-02 11:59:59
DO_STATS;         sysdate + 2 / 1440

Job has not run.

Log in as System and issue

sql> alter system set job_queue_processes = 4

Query dba_jobs again

  JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC FAILURES

WHAT                 INTERVAL
----- --------- -------- --------- -------- --------- -------- ---------- --
------------------ --------------------
   41 26-MAR-02 12:07:18                    26-MAR-02 12:09:18          0
DO_STATS;         sysdate + 2 / 1440

Job has run and continues to do so every two minutes.

Why did none of the initial 3 SNP processes pick the job up, why did I have to create another SNP process?

Regards Received on Tue Mar 26 2002 - 06:09:21 CST