This week's giveaway is in the EJB and other Java EE Technologies forum. We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line! See this thread for details.
I was wondering what could be the reason that PreparedStatement cache is for each Connection object individually? Why we can't share cached PreparedStatement across multiple connections?
If we can't share PreparedStatement cache across connections then this is the problem I see, Consider we have Connection pool and for a Request1 the connection caches the PreparedStatement1 . Now, if there is Request2 and for the same PreparedStatement1 it ends up getting another Connection from the pool then the cache that exist in the other connection for this statement is not useful.
I don't think you need a DBA. All there is to know is that caching of Statements and PreparedStatements is handled differently by different DB vendors and JDBC drivers. In short there is no clear-cut answer to this.
Largely though I think you should just be happy with whatever caching exists because that is what the RDBMS and driver handle for you. The creators of that software spend most of their time configuring their applications to provide optimal levels of performance in terms of cacheing (statements and results) vs. memory usage and that sort of thing.