RE: Parallel procesing configuration.
Date: Fri, 4 Dec 2009 16:06:51 -0500
Message-ID: <790A66C0DE294C73873BBF26B8CBBA69_at_rsiz.com>
Usually when someone writes OLTP, that means there are at least a few interactive transactions where you do not want to keep a human being waiting. Humane designers of applications suites plan so that there is sufficient headroom of processing capability that the interactive users do not grow a beard while waiting for the next interaction. If you unleash parallelism on a single job, which is sometimes the way to crank big jobs through fastest a small number of jobs at a time on a machine with no interactive users, then the interactive users grow a beard and it turns white.
So one way to still get something like full utilization of all but the headroom is to use a process manager, such as the concurrent manager from Oracle’s very own e-Business suite. So instead of unleashing jobs with a parallel degree higher than 1, you configure something to try to run batch jobs but leave enough headroom to avoid the aforementioned beard growth.
Now in pure batch, as told in an eloquent paper by Cary Millsap, it takes just about 2 jobs per processor to tie them up. Now unlike some of the actual mathematical laws and theorems Cary from time to time references, this is just a thumbrule of experience based on the coincidence, which might vary some, that jobs batch jobs preempt themselves from the cpu to do i/o about half the time. (His paper really explains this quite nicely, and that is only an approximation.) So whatever concurrency of jobs you set up to get parallellism by number of jobs instead of parallelism within a single job, remember to leave the headroom you need.
If you have some “workshifts” where interactive users are absent, and you have some whopping big job that needs all your horsepower to finish before the interactive user population returns, then you *might* stop submitting new jobs through your hypothecated process manager and turn that bad boy loose using the whole machine. But usually you just up the number of jobs run concurrently to eat up the headroom and take that back down so the headroom returns before the user population. Workshifts might vary by day, timezone, and various and sundry other things.
In summary, you probably want degree 1 on OLTP systems, and to manage utilization by managing the number of batch or psuedo batch jobs that are run in parallel with each other.
Regards,
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Juan Miranda
Sent: Wednesday, December 02, 2009 11:52 AM
To: oracle-l_at_freelists.org
Subject: Parallel procesing configuration.
Hi
With today multicore servers, parallel query is very important to get the max performance from a server.
Itīs common to see 16 core servers using only one of them…
Do you put some little parallelism on OLTP databases?
Where do you configure it? At table level? Instance level?
Please, share your kwnoledge, interesting docs, hints, etc about this complex topic.
This is my biggest question about Oracle configuration.
Thanks.
Juan
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 04 2009 - 15:06:51 CST