TIP #31: Jobs in dba_jobs are not running as per schedule

Today, I faced with strange situation for a client. I submitted a job in dba_jobs with a minute interval. This job was running simple stored procedure.
Job was not run every minute while manual run (with dbms_job.run) was successful.
Job_queue_process was 10 and _job_queue_inteval was 5 which means scheduler checks job for execution every 5 seconds.
Eventually found that background process cjq0 was the culprit.This background process is responsible for running scheduled job.It does initiate J00 processes for executing scheduled jobs.
This background process was hung, as the result it does not initiate any J00 process which means job was not run every minutes.

Resolution :

  1. Kill cjq0 process from OS
  2. alter system set job_queue_process=0;
  3. alter system set job_queue_process=10;
  4. Should fork new cjq0 process and all J00 process for running jobs.

2 comments:

RD said...

Also if you have more jobs in your instance, it should be more. I had the same issue even though it was set to 10. I checked that we had only 3 jobs running earlier and the developers created another 6 more jobs and it got struck. I had to increase it to 20, then it worked well.

kishankapoor said...

Right,Good to see these helpful information here Oracle Jobs .Thanks lots for sharing them with us.