The Data Source connections are required to retrieve / store the application data from/to the database. The Web logic Server provides the below types of statements to be executed to exchange the data from database.

1. Statements (General SQL Statements)
2. Prepared Statements
3. Callable Statements (Stored Procedure)

When an application connects to database for fetching the required application data, there are chances to have high processing overhead due to huge data size. Thus Web Logic Server provides the concept of caching the Prepared Statements and Callable Statements so that Web Logic Sever can reuse the statement stored in the cache.

The Statement Cache is applicable on the Data Source, however each connection maintains its own statement cache for Prepared Statements and Callable Statements

Possible configurations for Statement Cache:
1. Statement Cache Type:
The statement cache type uses the below given algorithm to identify which statements to be stored in the statement cache.
LRU Algorithm: LRU is Least Recently Used algorithm defined as default for the statement cache type.
When a Prepared Statement or Callable Statement is received by the Web Logic Server for processing, it validates from the Cache statements with the statements already in cache.
– If the received statement exists, then Web Logic Server returns the statement and execute the existing statement for processing.
– If the received statement does not exist and considered to be as new statement, then Web Logic Server validates statement Cache Size or confirm if total cache size is reached or not.
o If the cache size is not reached, then Web Logic Server will store the statement
o If the cache size is reached, then Web Logic server will check for the statement which is least recently used and replace the least recently used statement with the new statement.

Fixed Algorithm:
In case of Fixed algorithm, Web Logic Server accepts and store the statements in cache till the statement Cache Size is reached. Once the statement Cache Size is reached, the newly received statements will not be cached.

2. Statement Cache Size:
The statement cache size is the total number of Prepared Statement or Callable Statement which are allowed to be cached for each connection of the data source in the Web Logic Server.
It is to be noted that too many open connections might impact the DBMS resource cost if cursors are included for execution.

The memory consumption depends on the below calculations
number of data sources * number of connections * number of statements
Example: Let’s assume the below numbers
Data Source connection: 10
Deployed Servers: 2
Statement Cache Size: 10
As per the calculation the total open cursors required at database server will be (10 *2*10) = 200 cursors for the cache statements.

Setting the size of the statement cache to 0 turns off statement caching
Minimum value: 0
Maximum value: 1024