Job occasionally not running as expected.
- From: "Stuart Clowes" <stuart.clowes@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 14 Jul 2006 15:12:20 +0100
Summary:
We have a job that is supposed to run every 3 seconds. It actually runs
every 5 seconds (no worries there), but occasionally doesn't seem to run at
all for a period of up to approx 3 minutes.
Detail:
Our software suppliers have a job in their/our database.
JOB LOG_USER PRIV_USER
---------- ------------------------------ ------------------------------
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC
NEXT_DATE
------------------------------ --------- -------- --------- --------
---------
NEXT_SEC TOTAL_TIME B
-------- ---------- -
INTERVAL
--------------------------------------------------------------------------------
FAILURES
----------
WHAT
--------------------------------------------------------------------------------
NLS_ENV
--------------------------------------------------------------------------------
MISC_ENV INSTANCE
---------------------------------------------------------------- ----------
339 MANHATTEN MANHATTEN
MANHATTEN 14-JUL-06 14:50:17
14-JUL-06
14:50:20 1014568 N
SYSDATE + 3/86400
0
BEGIN manhatten_com.poll_label; END;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$'
NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='YYYY-MM-DD
HH24:MI:SS'
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
0102000000000000 0
As you can see, the intention is to run every 3 seconds. The jobs usually
takes a very small fraction of a
second, but occasionally takes up to 15 seconds. This is as we expect.
Also, as _job_queue_interval=5, we actually see this usually run every 5
seconds, as evidenced by running
this SQL in a loop. (The loop has a sleep 2 in it, I've taken out some of
the repeated data in the data given
below).
select what, to_char(last_date,'DD-MON-YYYY HH24:MI:SS') last_date ,
to_char(next_date,'DD-MON-YYYY HH24:MI:SS') next_date,
to_char(this_date,'DD-MON-YYYY HH24:MI:SS') this_date,
decode(this_date,null,null,'EXECUTING')
from dba_jobs
where what like '%poll_label%'
order by next_date
/
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 11:32:40 14-JUL-2006 11:32:43
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 11:32:45 14-JUL-2006 11:32:48
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 11:32:50 14-JUL-2006 11:32:53
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 11:32:55 14-JUL-2006 11:32:58
So far, so good. However, every so often, the job 'appears' not to run for a
few minutes.
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:50:31 14-JUL-2006 12:50:34
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:50:31 14-JUL-2006 12:50:34 14-JUL-2006 12:50:36 EXECUTING
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:50:31 14-JUL-2006 12:50:34 14-JUL-2006 12:50:36 EXECUTING
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:50:31 14-JUL-2006 12:50:34 14-JUL-2006 12:50:36 EXECUTING
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:50:31 14-JUL-2006 12:50:34 14-JUL-2006 12:50:36 EXECUTING
.... job finishes, next_date is set to three seconds in the future.
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:50:36 14-JUL-2006 12:50:39
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:50:36 14-JUL-2006 12:50:39
.... repeated approx 30 times....... We've gone way beyond 12:50:39 - why
hasn't my job
executed?
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:50:36 14-JUL-2006 12:50:39
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:50:36 14-JUL-2006 12:50:39
.... and finally, it appear to have executed again.
WHAT
--------------------------------------------------------------------------------
LAST_DATE NEXT_DATE THIS_DATE DECODE(TH
-------------------- -------------------- -------------------- ---------
BEGIN manhatten_com.poll_label; END;
14-JUL-2006 12:54:01 14-JUL-2006 12:54:04
Additional info:
SCLOWES @ pkxxl> show parameter job_q
NAME TYPE VALUE
------------------------------------ ----------- ------------
job_queue_processes integer 35
SCLOWES @ pkxxl> select count(*) from dba_jobs;
COUNT(*)
----------
24
I don't ever see non-zero values in FAILURES column.
Can anybody give some pointers as to what may be happening here?
Stuart
Other related posts:
