What is Statement Cache Size in Weblogic ?

If you use a prepared statement or callable statement in an application, there may be processing overhead in the communication between the application server and the database server . To handle this processing costs, WebLogic Server can cache prepared and callable statements used by your applications.

As Oracle Says

The Statement Cache Size attribute determines the total number of prepared and callable statements to cache for each connection in each instance of the data source.

So as it says, it can help in improving performance. But there is catch as well. In many cases, the database 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. This is not good for your database.

You may fall in this — ORA-01000: maximum open cursors exceeded

So use this parameter wisely.

We can find the impact on database as well. For example, if we have a data source with 100 connections deployed on 2 servers, if you set the Statement Cache Size to 20 (the default), you may open 4000 (100 x 2 x 20) cursors on your database server for the cached statements. So better to ensure everything is in place.

Keep Learning .