We are looking changing some of our parallel settings to get more out of Exadata system and have gotten mixed advice from consultants/internal dbas. Oracle consulting recommended 800 parallel servers per node whereas internally the recommendation was in the 200 range. System is a full rack that is not doing very much offloading due to various issues. I know the question is broad but I wanted to get a sense of what kind of parallel settings everyone was using in their environments. I know we are starting to use spatial which use higher amounts of parallelism but 800 per node seems pretty high to me.
It's hard to answer this question without knowing more about how you're using parallelism now. But in general, how heavily are your existing PQ servers used? If using auto DOP, are you seeing a lot of parallel statement queueing?
Off the top of my head, 800 PQ servers on a single X2-2 DB server seems like a lot; I'd start to get worried about running out of RAM that point.
Have you considered increasing PQ servers gradually and measuring the impact on performance and memory usage?
With only 96 cores in total in a full rack X2-2 it is a bit pointless to set it too 800, you will probably completely saturate your system resources. I have seen a client who set this to similar ranges as well on a full rack, the performance was completely gone due to a lack of system resources. I don't know how many instances your database has but the oracle documentation says the following about a default setting, go from there and like Marc said, slowly increase it (or not):
(CPU_COUNT x PARALLEL_THREADS_PER_CPU x (2 if PGA_AGGREGATE_TARGET > 0; otherwise 1) x 5)
Thanks for the replies. We are not using auto dop right now and we are not using a high number of parallel queries. we are looking at using parallelism to help the response time time of some ling running queries and to improve spatial query performance. By my calculations, our default should be 480 so i will see if we can start closer to there and increase/decrease as needed.
I cant think of any good reason to set parallel_max_servers to 800 on a 2s12c24t system as that is an instance level parameter. The default is already probably too much. Whomever recommended that needs to give some explanation on why that makes any sense at all.
It's not hard to make a system CPU bound using 2 x the logical core count for PX. So the question you have to ask yourself is how will you manage to keep your system in control? Resource Manager? Parallel Statement Queuing?
I'd say start with a more reasonable setting, like 4 x 24 logical cores = 96 and see how that works. If you set parallel_max_servers too large, it could result in your system being overloaded and thrashing.
Greg Rahn | blog | twitter | linkedin