Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vinod.

Asked: February 13, 2013 - 9:51 pm UTC

Last updated: February 14, 2013 - 9:37 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I have been asked to find out optimal value for job_queue_process parameter on Oracle 11g on specific hardware.

The approach that we have taken is as follows:

1. Set the parameter value to 1000 (max limit) and execute 1000 jobs concurrently. The job procedure will have an infinite loop with some select queries.

2. Monitor the O.S. resource impact.

3. If the above step has an impact on O.S. resources like CPU, Repeat the above steps by lowering the job_queue_process parameter value.

I had look at Oracle support and it suggest us to evaluate from application point of view. But as this request is to test max value that can be set on specific hardware.

Please let us know your approach/view on the same.

Thank you,
Vinod Sugur

and we said...

You can stop setting to 1,000 unless you have on the order of 250-500 cores on your machine.


Think about the math here.

question: If you have a four core machine - how many CPU intensive things can you do at the same time?

answer: about four.


question: if you have a four core machine - and you have some things that do a little IO, some cpu, some IO, some cpu - how many things can you do at the same time?

answer: about four to maybe 16 - depending on the amount of time they spend off of the CPU doing something else like physical IO.




question: So, if you have an N core machine - how many things can you schedule to run at the same time - assuming NOTHING ELSE IS ON THE MACHINE?

answer: about N to (four to ten) times N at most. Ten being an extreme rare case, four being pretty typical, and have strong arguments for a number less then N (machines typically do more than just run jobs in the job queue)




So, how do you determine what this number is - this multiplier that will be some real number between 0 and 10?


you have to look at the jobs - the jobs you are running (infinite loop with queries that will not do any physical IO probably after the first execution) - I would set job queue processes to AT MOST (N-1) - one less than the number of cores you have (you want to save some for the backgrounds and so you can still type ls at the command prompt).


If you were doing "normal" jobs - jobs that did IO and used some CPU - maybe a little more than N, but not much.


See this video:

http://www.youtube.com/watch?v=xNDnVOCdvQ0

watch it and appreciate what it is showing you - less is more!


and I wouldn't start with the maximum load. you should start with the minimum load (1 job in the queue) and measure OS utilization. then go up to 2, to 4, to 8, to 12, to 16, .... - measuring and plotting.

and just stop when the cpu gets to

a) 60% utilization on OLTP where other things will take place besides the job
b) ~80/90% utilization on a batch system where you are the only thing running


Rating

  (1 rating)

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

Comments

Thank you Tom

Vinod Sugur, February 14, 2013 - 9:41 am UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library