I attended an Oracle sponsored day yesterday on Oracle’s engineered systems. The main speaker was Tom Kyte who is a widely known Oracle performance expert. After his talk was complete he graciously agreed to talk with me about performance issues with a web site that has hundreds of sessions to the database in a connection pool. Tom said that the connection pool should be roughly twice the number of CPU cores and not many times that amount as they are in our case. He recommended this Oracle produced youtube video that demonstrates that fewer sessions in a session pool produce better throughput:
It isn’t immediately obvious to me why this is the case.
Tom Kyte recommended changing the application to use a smaller pool of connections or if that’s not possible to use shared servers. In my case shared servers seems like a better option because it would take more effort to modify the application tier. We could set the number of real connections to twice the number of cpus and allow the web servers to spin up hundereds of shared server connections instead of decidated server connections. We need to test it to prove it out of course. No guarantees until you test it in your own environment.