In weblogic JDBC config there is an option to cache the statement, what exactly is JDBC statement caching.. and moreover how is it related to open cursors parameter in database?
Read in some oracle documentation that open cursor in db value is directly propotional to cache size, how are they related? how does cache functions?
JDBC statement cache helps improve the performance of an application by storing compiled copies of prepared/callable statements thus decreasing CPU cycles on the Database. Since, this involves linking to the database, the statement cache functionality in WebLogic may use a cursor (open a cursor) on most databases (very sure for ORACLE DB). Thus, setting the statement cache size to a higher value will directly increase the number of cursors opened in the database. So, we have to be very careful (calculative) when choosing a value. Below documentation discusses the above points in a more elaborate, clear way and with examples.
Focus on the below explanations from the documentation. They will give a clear picture on statement cache size:
"When WebLogic Server caches a prepared or callable statement, the statement may open a cursor in the database. If you cache too many statements, you may exceed the limit of open cursors for a connection. 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."
"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. By caching statements, 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.
For example, if you have a data source with 10 connections deployed on 2 servers, if you set the Statement Cache Size to 10 (the default), you may open 200 (10 x 2 x 10) cursors on your database server for the cached statements."
Please let me know if you have any further questions.