Tom Kyte On Parallel Query
More from Tom Kyte, this time on Parallel Query. Tom also quotes an interesting line from Jonathan Lewis' Practical Oracle 8i book regarding the scalability of Parallel Query:
"You might want to get Jonathan Lewis's book "practical Oracle8i -- building efficient databases". I think he put it beautifully when he described parallel query as a "non scalable operation". It does not scale up with users (more users, less scalability). It gives you the ability to consume the ENTIRE machine with your one query. It is designed to use EVERYTHING, it is very selfish."
In other words, Parallel Query stops being effective when you increase the number of concurrent users querying the database:
"PQ was designed to let a few users totally consume a machine. As you add more and more users -- you have less and less resources -- hence by definition I would expect things to run slower and slower.
Now the question is -- given the resources you have (24 cpus) -- and more then 20 users -- what is the fair and equitable ratio of USERS to CPUS. It is getting nearer and nearer to 1:1 as the number of users approaches and then exceeds the number of CPUs.
Think what would happen if you got 50 users doing parallel 10? 500 concurrently execution processes doing CPU grabbing and context switching all over the place.
This is exactly why connection pooling, connection concentration, connection multiplexing was invented -- to reduce the amount of concurrently executing stuff as the machine gets swamped.
20 users doing parallel query on a 24 cpu machine cannot reasonably expect the performance that same machine gives 1 user, 5 users.
What you should be looking for is a predicable, near linear change in response times as concurrency goes up and demand for these shared resources increases."