Web Logic Server provides the capability of connecting to databases using Data Sources. Through Data Sources Web Logic provides the data access and database connection management activities. The created Data Source in Web Logic Server contains the connection pool of database connections for providing connections at run time to perform database operations.

The database operation could be considered costly in terms of performance if not tuned properly. Let’s identify the options what could be applied in different scenarios to achieve high performance while using data source connection pool.

1) JNDI Lookup Calls: Each Database call includes JNDI lookups which could be costly if same statement requires to be executed multiple times. Thus it is important for caching an object that required a look up in client
2) Reuse available connection: It could be useful if the application can reuse the received connection from the pool instead of closing and waiting to reacquire new connection.
3) Timely Connection Release: Connection should be released once it has performed the required action and should not be kept holding.
4) Configuring Row Pre Fetch: Row prefetching improves performance when receiving more rows from the external server to the client server in a single server access. Increasing Row Pre Fetch value will increase performance, until a particular value is reached
It is also to be noted that it is applicable only for external clients, not for clients in the same Java Virtual Machine (JVM) as the WebLogic Server.
Minimum value: 2
Maximum value: 65536
It is advisable to keep Minimum value as 100 as after 100 rows the performance hardly increases.
5) Connection Testing: Web Logic Server provides the way to validate and test the database connection before allocating it to the objects / applications.
The parameter Test Connection On Reserve enables the web logic server instance to check the database connection prior to returning the connection to a client.
The parameter Test Frequency is also used to validate the database connection.
Both these parameters are fairly expensive in terms of performance and should be avoided in the production environment.
6) Prepared and callable Statements: 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.

Read the below blog on the detailing about statement cache in Web Logic Server Tune Data source Connection using Statement Cache

7) Limit Waiting Connection Request: If the connection is requested from the Data Source Connection Pool and multiple connection requests are waiting as Connection Pool has reached to its maximum capacity, then it might result into blockage of threads waiting to acquire new connection.
For this reason, the parameter value for Maximum Waiting for Connection (HighestNumWaiters) should be minimum. If the parameter value for Maximum Waiting for Connection is set to 0, then connection requests cannot wait for acquiring a connection and thus SQL exception – PoolLimitSQLException will be returned.

8) Connection Reserve Timeout: When Data Source Connection Pool reaches to its maximum capacity, then PoolLimitSQLException will be returned to the application(s). To avoid receiving the immediate connection timeout exception, configure Connection Reserve Timeout value (in seconds).
Default value for Connection Reserve Timeout: 10 seconds
If Connection Reserve Timeout is set to -1, then it will timeout immediately if no new connections available
If Connection Reserve Timeout is set to 0, then connection request(s) will wait for indefinite timeout till they are allocated with new available connection

9) Shrink Frequency: To increase the availability of connections in the pool Web Logic Server periodically shrinks the connection to its initial capacity based on usage. The Parameter Shrink Frequency value is to let web logic server know the number of seconds to wait before shrinking a connection pool. To Disable the Shrink Frequency, set the value to 0.

10) Database Listener Timeout: In case of applications having huge loads and consuming High CPU Utilization, it is observed that database listener get timeout while creating a new connection and throws ResourceDeadException and Socket read timed out connection errors.
To overcome such issues, it is advisable to increase the timeout value of the database server
sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=180

11) Recovering Leaked Connections: The connections which are not properly returned back to the Data Source Connection Pool can be considered as Leaked Connections. The parameter Inactive Connection Timeout can be configured (in seconds) whenever data source maintenance thread runs it will check for the connections which are Inactive more than the set value in the parameter and will returned them back forcibly to the connection pool.
12) Pinned-To-Thread: Normally the connection request is allocated with the new data source connections and once the thread is executed the connection is returned back. In case of enablement of the PinnedToThread Property WebLogic Server pins a database connection from the data source to a connection requested thread for the first time when an application uses the thread to reserve a connection. After finishing the assigned task, instead of returning the connection back to the connection pool, Web Logic server keeps the connection requested thread and does not return it to the data source and keep it reserved for the next connection requested thread to allocate when request for new connection again.
With the enablement of PinnedToThread property, the connection pooling behaviour will notice the below changes
• Maximum Capacity is ignored
• Shrinking does not apply to connection pools with PinnedToThread enabled as connections are reserved and not returned back to the connection pool
• When web logic reset the connection pool, it marks the connections as Test Needed and for that Test Connections on Reserve need to be enabled as PinnedToThread keeps the connections on reserve.
• The PinnedToThread feature does not work with an IdentityPool, an error is thrown and the data source will not deploy
• With PinnedToThread enabled, does not support connection labelling, exception is thrown when trying to get a connection with label properties
• When PinnedToThread is enabled, the maximum capacity of the connection pool becomes the number of execute threads used to request a connection multiplied by the number of concurrent connections each thread reserves and exceeds the maximum capacity