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:
http://www.youtube.com/watch?v=xNDnVOCdvQ0&feature=youtube_gdata_player
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.
– Bobby
Pingback: Faster commit time with fewer sessions | Bobby Durrett's DBA Blog
Pingback: Shared servers results in lower CPU usage for top query in AWR | Bobby Durrett's DBA Blog
And the reference cpu count would be the one at the DB side or at the client side?
Ciro,
The cpu count is on the database server. In our one production database server that uses shared servers we are now using a 3 to 1 ratio and it is working well. We have 12 cpus on our database server and one database instance with 36 shared servers. We started at 2 to 1 and bumped it up to 3 to 1.
– Bobby