Skip to Main Content
  • Questions

  • Jobs_queue_processes parameter and its impact

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Nil.

Asked: March 31, 2009 - 1:27 am UTC

Last updated: April 03, 2009 - 7:27 am UTC

Version: 9i

Viewed 1000+ times

You Asked

On one of our test databases(9i) when we set jobs_que_processes to 20, the CPU goes to 100% hanging all applications. Previous to this, the parameter was set to 0 by DBA when they found about 700 jobs running, they set this parameter to 0 after dropping all these 700 jobs.
But this resulted in halting execution of all newly created 40 jobs which are required for application processing.Then the DBA again set it to 20 thats oracle default parameter and resulted in 100%CPU usage.

Can you please comment on this and also what is the impact of this parameter and whether we should change this to any other value?

and Tom said...

you have 40 jobs in the queue.

When you set job queue processes to 20, you are saying "let 20 of them run at a time"

When you run 20 things on your machine - which probably has less than 21 cpu's - you will - well - use 100% of your cpu. It is just "math" at this point, you are asking for 20 things to be running RIGHT NOW, with everything else.

So, maybe what you want to do is use a more reasonable number for your system - say 1, or 2, depending on

a) your current workload
b) your available resources.



It is the 20 copies of YOUR CODE that is consuming the cpu, not the job queue itself, but rather YOUR CODE.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Job scheduling continued

Nil desh, April 02, 2009 - 12:44 am UTC

Thanks for your reply. I understand your point. However I have one doubt in my mind. How does the same setting runs well in production server which also has one CPU. Is it then something related to how the jobs need to be scheduled or something else also? Please comment on this. It will be very usefull for us
Tom Kyte
April 02, 2009 - 9:42 am UTC

it works the same in production as it does on whatever this machine is.

However, on production - it is likely you do not have the same jobs scheduled, if you did - you would see the SAME EXACT THING.

this parameter just (as in only) controls the number of jobs that will run simultaneously. If you have a single cpu, you shouldn't really set it above 2 - and if you have jobs in the queue, you should expect (as in HOPE, ANTICIPATE) that the CPU jumps to 100% - if not, something is wrong.

You have a single cpu, if you ask to do N things on it, you expect it to be fully utilized.

Job scheduling continued

Nil desh, April 03, 2009 - 12:45 am UTC

Thanks for your comments. We have reset the value of parameter job_queue_processes to 5 now. Its working fine now. I have one question, if the machine has 1 cpu and i set the parameter job_queue_processes=2 as you have suggested then what will be the effect if there are 3 jobs scheduled like this:-
1st jobs is scheduled at 12:00 AM and expected to complete in 30 minutes (means at 12:30 AM)
2nd job is scheduled at 12:10 AM and expected to complete in 30 minutes (means at 12:40 AM)
3rd job is scheduled at 12:20 AM and is expected to complete in another 30 mins (means at 12:50 AM).
What will be the effect on scheduled jobs, because we see here 2 jobs are overlapping the execution time of 1st job.
Also another last question - is there any other database parameter on which job_queue_processes depends or is affected and vice-versa.

Thanks in advance for your replies.
Tom Kyte
April 03, 2009 - 7:27 am UTC

use logic....

the job queue will pull off the first job at 12:00 am and start it.
the job queue will pull off the second job at 12:10 am and start it.
The job queue will do nothing until one of the two finish.
at 12:30am, job from 12:00am finishes, the 12:20am job is selected and run


job_queue_processes is as simple and basic as it sounds - it is simply YOU telling US how many jobs to allow to run at the SAME TIME. Nothing more, nothing less.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.