Tuning ORDS Connection Pool and Properties

 

                                     

Modifying Oracle REST Data Services Properties (ORDS)

Errors Related :

SEVERE: *** Error: Could not obtain a database connection because all connections in the pool: |apex|| were in use. Borrowed Connections: nn Available Connections: 0 Maximum Connections: nn***
Feb 06, 2019 9:43:46 AM
SEVERE: java.sql.SQLException: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use
ServiceUnavailableException [statusCode=503, reasons=[]]

OR

All Connections In The Universal Connection Pool Are In Use

ORDS uses default configuration values which at times aren't enough for applications running with heavy transactions.  By default only 10 connections can be made to APEX, this is not the number of concurrent users but current connections.  If the application is running long queries the connections are kept open by a user this left an idle session and the connection is not released for 15 mins by default. 

The number of sessions in jdbc.MaxLimit are in use. For example, if a report with a slow SQL query is executed, and the end user grows impatient and clicks reload in their browser 9 more times -all 10 sessions will be busy.

Solution : -

Backup the defaults.xml file & Update the file to change the default values matching to your requirement.

1. The initial size of the pool 

<entry key="jdbc.InitialLimit">5</entry>

2. The minlimit is how low the pool is allowed.

<entry key="jdbc.MinLimit">10</entry>

3. The maxlimit is what it sounds like the most connections that will be allowed.

<entry key="jdbc.MaxLimit">30</entry>

4. InactivityTimeout will idle the connection pool back down to the minLimit over time as the connections are idle for the value provided.

<entry key="jdbc.InactivityTimeout">1800</entry>

5. This one covers in case the connection gets lost due to anything at all.  If the connection pool doesn't get the connection back for some reason for this amount of time and is idle for this time, it will be reclaimed automatically.

<entry key="jdbc.AbandonedConnectionTimeout">900</entry>

6. Lastly for jdbc, this is how many request a connection will service before it is closed and new one opened.

<entry key="jdbc.MaxConnectionReuseCount">50000</entry>

Restart the ORDS services to take the effect.

Thank you !!