Tuning WebLogic's Prepared Statement Cache

In [10], the top 9 tuning recommendations for WebLogic Server includes

Use the Prepared Statement Cache

The primary utility of a cached prepared statement is its association with a compiled query plan in the DBMS.  In this article, we will show how to tune Prepared Statement Cache in Oracle WebLogic Server for better web application performance.

Caching Prepared Statements

There are two steps to complete a SQL request: 

• Compiling the SQL statement
• Executing the SQL statement

By using prepared statements (java.sql.PreparedStatement), you can reduce unnecessary compilation, saving time.  A prepared statement contains SQL statements that have already been compiled, thus making their execution faster. If you’re going to use a SQL statement more than once, you should use a prepared statement.

However, when you use a prepared statement or a callable statement (a callable statement object provides a way to call stored procedures in a standard way for all RDBMs) in an application, there’s additional overhead due to the need for communication between WebLogic Server and the database.

To minimize the processing costs, WebLogic Server can cache prepared and callable statements used in your applications. When an application or EJB calls any of the statements stored in the cache, WebLogic Server reuses the statement stored in the cache. Reusing prepared and callable statements can:

• Reduce CPU usage on the database server 
• Improve the statement’s performance on the application server

Statement Cache

The statement cache caches statements from a specific physical connection.  Each connection in a data source has its own individual cache of prepared and callable statements used on the connection. However, you configure statement cache options per data source. That is, the statement cache for each connection in a data source uses the statement cache options specified for the data source, but each connection caches it's own statements. Statement cache configuration options include:

• Statement Cache Type—The algorithm that determines which statements to store in the statement cache. See Statement Cache Algorithms.
• Statement Cache Size—The number of statements to store in the cache for each connection.

Configuring Statement Cache Size

You can configure the size of the statement cache from the WebLogic Server Administration Console by navigating to:

• Services -> Data Sources -> ApplicationDB -> Configuration -> Connection Pool

The value can be from 0 to 1024 (default: 10).  If you set the size of the statement cache to 0, it runs off statement caching.

JDBC DataSource Runtime Statistics

Each connection in the connection pool has its own cache of statements. JDBC DataSource Runtime Statistics shown in the table are the sum of the number of cached statements for all connections in the connection pool.

• Prep Stmt Cache Access Count 
• The total number of times the statement cache was accessed
• Prep Stmt Cache Add Count
• The total number of statements added to the statement cache for all connections
• Prep Stmt Cache Current Size
• The number of prepared and callable statements currently cached in the statement cache
• Prep Stmt Cache Hit Count 
• The running count of the number of times the server used a statement from the cache
• Prep Stmt Cache Miss Count
• The number of times that a statement request could not be satisfied with a statement from the cache

Access Count is the sum of Hit Count and Miss Count.  When you tune for the cache performance, you want to reduce the miss ratio (i.e., Miss Count / Access Count) based on the guideline below.

Tuning Guideline

By increasing the statement cache size, you can increase your system performance. However, you must consider how your DBMS handles open prepared and callable statements. In many cases, the DBMS will maintain a cursor for each open statement. This applies to prepared and callable statements in the statement cache.

If you cache too many statements, you may exceed the limit of open cursors on your database server. If the DBMS is Oracle, you will get ORA-1000 error.  To avoid exceeding the limit of open cursors for a connection, you can change the limit in your database management system or you can reduce the statement cache size for the data source.

References

1. Oracle WebLogic Server 11g Administration Handbook
2. JDBC statement cache
3. Why Prepared Statements are important and how to use them "properly"
4. Statement Cache Algorithms
5. Monitoring and Tuning Oracle Fusion Applications
6. Monitoring WebLogic JDBC Connection Pool at Runtime (XML and More)
7. Oracle JDBC Memory Management (Oracle Database 12c)
8. Fusion Middleware Performance and Tuning for Oracle WebLogic Server
9. Oracle® Fusion Middleware Tuning Performance of Oracle WebLogic Server 12c (12.2.1)
10. Top Tuning Recommendations for WebLogic Server