ORDS Connection Pooling and Performance Tuning Essentials

ORDS Performance Under Load

ORDS handles every REST request by acquiring a database connection from its internal pool, executing the SQL or PL/SQL, and returning the connection to the pool. Under heavy load, connection pool configuration directly determines whether your APIs respond quickly or queue up. Understanding and tuning the pool is essential for production ORDS deployments.

Key Pool Settings

ORDS connection pool settings are configured in the pool configuration file or through the ORDS command line. The most important settings:

# pool.xml or ords config
db.connectionType=customurl
db.customURL=jdbc:oracle:thin:@//dbhost:1521/ORCL

# Pool sizing
jdbc.InitialLimit=10        # Connections created at startup
jdbc.MinLimit=10            # Minimum connections kept alive
jdbc.MaxLimit=50            # Maximum concurrent connections

# Timeout settings
jdbc.MaxConnectionReuseTime=900    # Recycle connections after 15 min
jdbc.InactivityTimeout=300         # Close idle connections after 5 min
jdbc.MaxStatementsLimit=20         # Statement cache per connection

Sizing the Pool

The MaxLimit should match your expected peak concurrent API requests. Each active request holds one connection for the duration of the SQL execution. If your average request takes 50ms and you expect 200 requests per second at peak, you need at least 10 connections (200 * 0.05). Add headroom for spikes, so 20 to 30 would be reasonable. Setting MaxLimit too high wastes database resources; setting it too low causes requests to queue or timeout.

Monitoring Pool Health

ORDS exposes pool metrics through its management interface. Monitor the number of active connections, the number of requests waiting for a connection, and the average wait time. If the wait queue grows during peak hours, increase MaxLimit. If many connections are idle, reduce MinLimit to save database resources.

SQL Performance in ORDS Handlers

Every millisecond your SQL takes is a millisecond a pool connection is held. Optimize handler queries just as you would any performance-critical SQL: use bind variables (ORDS uses them automatically for template parameters), ensure indexes cover common filter patterns, avoid unnecessary joins, and use pagination to limit result set sizes. For complex queries, consider materializing the results in a table or view that the handler queries directly.

Caching

ORDS supports HTTP caching headers. For read-heavy endpoints with data that does not change frequently, set the ETag and Cache-Control headers in your handler to let clients and CDNs cache responses. This reduces database load dramatically for popular endpoints:

-- In your handler, set cache headers
OWA_UTIL.MIME_HEADER('application/json', FALSE);
HTP.P('Cache-Control: max-age=300');  -- Cache for 5 minutes
HTP.P('ETag: "' || l_data_hash || '"');
OWA_UTIL.HTTP_HEADER_CLOSE;