Technical data

Increasing Performance with the Prepared Statement Cache
Administration Guide 16-35
Determining the Proper Prepared Statement Cache Size
To determine the optimum setting for the prepared statement cache size, you can
emulate your server workload in your development environment and then run the
Oracle statspack script. In the output from the script, look at the number of parses per
second. As you increase the prepared statement cache size, the number of parses per
second should decrease. Incrementally increase the prepared statement cache size until
the number or parses per second no longer decreases.
Note: Consider the usage restrictions for the prepared statement cache before you
decide to use it in your production environment. See “Usage Restrictions for
the Prepared Statement Cache” on page 16-33 for more information.
Using a Startup Class to Load the Prepared Statement
Cache
To make the best use of the prepared statement cache and to get the best performance,
you may want to create a startup class that calls each of the prepared statements that
you want to store in the prepared statement cache. WebLogic Server caches prepared
statements in the order that they are used and stops caching statements when it reaches
the prepared statement cache size limit. By creating a startup class that calls the
prepared statements that you want to cache, you can fill the cache with statements that
your applications will reuse, rather than with statements that are called only a few
times, thus getting the best performance increase with the least number of cached
statements. You can also avoid caching prepared statements that my be problematic,
such as those described in “Usage Restrictions for the Prepared Statement Cache” on
page 16-33.
Even if the startup class fails, WebLogic Server loads and caches the statements for
future use.
Note that each connection in effect has it’s own cache of statements. If you use a
startup class to cache statements, you must create the class in such a way that it gets
each connection from the pool and calls the prepared statements that you want to cache
on each statement.