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

Posted on 2007-10-03
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

    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 47

    Assisted Solution

      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

    STMT_CACHE_SIZE is the initial ( = max size if STMT_CACHE_NOLIMIT=0) size (KB) of the SQL statement shared cache.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now