Thanks for the question, Anuj.
Asked: March 03, 2020 - 4:00 pm UTC
Last updated: March 04, 2020 - 3:21 am UTC
Version: Oracle 12c
Viewed 1000+ times
You Asked
Hi,
I have 12 CPU server with
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 12.
But my Oracle parameters are cpu_count =12 and Number of process per cpu = 2. Also only one instance. Can you please suggest how much would be the optimal number of degree of parallelism for sql queries.
My confusion:
If Oracle can take degree of prallelism as 24 (12*2) then If I set it to 12 then how would the sytem behave? will only 6 CPUs be ustilized as I have Number of process per cpu set as 2?
and Connor said...
The answer is "It depends" - only some benchmarking is going to tell you the best.
For example ...
- I might have poor storage, and even just a few slaves floods the storage...so even with lots of CPU's I need *small* parallel
- I might have great storage, but my table volumes are such that I see diminishing returns after (say) 4 slaves, so why would I bother with 12 or 24 etc for a single query
- I might have non-parallel needs that must *always* be responsive, so I want to reserve (say) 40% of my CPU's to standard non-parallel sessions
I'm not entirely convinced of your numbers...I can't remember the last time I saw a CPU with a single core...I suspect you have a single socket machine with 12 cores. (Sometimes our AWR reports these things the wrong way around). In any event, do some testing/benchmarking on some sample queries to decide what parallel level works best for your business requirements, and then set init.ora parameters accordingly to get these results.
Is this answer out of date? If it is, please let us know via a Comment