What is the maximum recommended value for "statement cache size" for ODP on 10G R2?
Posted on 2007-10-03
I am using Oracle 10g R2 and the ODP for .Net version 10.102.3.0
When I was running this application as a fat client app (C++ using the 8.1.7 OCI library), I kept up to 200 (shared) handles cached on the clients, and got a big performance gain after I started caching.
When using .Net 1.1 against 9i, I used statement cache size=200. I had about 150 - 175 of our top (in frequency) statements set to add to the cache. I was afraid to add more.
I have seen examples referring to values around 10 to 20. I was advised by one consultant to cache all our statements, but with a set of distinct statements somewhere around 2000, I am wary of that.
I have searched this site, Google Groups and Oracle's TechNet, and have found no recommendations.
Our application is a web-based Intranet MES System for large corporations. We have a few hundred to a few thousand sessions active at any given time. All screens are generated dynamically based on current data state and the permissions granted to the session user. So we hit the database a lot.
Any direction you can provide will be appreciated.