[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


What is the maximum recommended value for "statement cache size" for ODP on 10G R2?

Posted on 2007-10-03
Medium Priority
Last Modified: 2013-12-19
I am using Oracle 10g R2 and the ODP for .Net version

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.

Question by:JimBrandley
LVL 23

Accepted Solution

Ashish Patel earned 800 total points
ID: 20011954
I searched once for the same solution, but like your application we also had many hundreds sessions working at a time. And i am using the cache size set to 500 depending on the mem capacity we have. It works great.
LVL 48

Assisted Solution

schwertner earned 400 total points
ID: 20014108
  What is Statement Caching?
   ODP.NET statement caching allows a specified number of server-side cursors
   to remain open, thereby allowing the database to execute same statements
   without re-parsing them.  Significant performance gains can be observed by
   enabling statement caching for applications that re-execute the same
   collection of statements repeatedly.
   The maximum size should be anounced in the documentation, but this is
   also a RAM and number of connection consuming. So you have to investigate the

In the same time Oracle caches the parsed statement in the Shared Pool.
If you use bind variables in your SQLs you can increase the shared pool
in Oracle SGA in order to keep the achive "soft" parsing that is less
time consuming as the so called "hard" (or full) parsing.
LVL 18

Assisted Solution

sventhan earned 800 total points
ID: 20014322
STMT_CACHE_SIZE is the initial ( = max size if STMT_CACHE_NOLIMIT=0) size (KB) of the SQL statement shared cache.

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month18 days, 23 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question