ORDS Performance Tuning  for APEX & REST Services

 Introduction

Oracle REST Data Services (ORDS) is the middleware layer that powers Oracle APEX, REST API s, and database-backed web services.
While ORDS works out of the box, default settings are not optimized for production workloads.

As application usage grows, DBAs may observe:

  • Slow APEX page loads
  • High response times after login
  • ORDS CPU spikes
  • Connection pool exhaustion
  • 503 errors during peak hours

This article provides a structured, real-world ORDS performance tuning guide, covering:

  • ORDS configuration
  • Database connection pooling
  • JVM tuning
  • OS-level optimization
  • APEX-specific best practices

Common Performance Symptoms

  • APEX login page loads slowly
  • Pages hang intermittently
  • ORDS responds slowly after restart
  • Frequent 503 Service Unavailable
  • High active sessions in DB from ORDS
  • CPU spikes on ORDS server

Performance Tuning Layers

ORDS performance tuning must be done across five layers:

  • ORDS configuration
  • Database connection pool
  • JVM tuning
  • Database-side tuning
  • OS & infrastructure tuning

ORDS CONFIGURATION TUNING

Key ORDS Parameters

Check current settings:

ords –config /path/to/config config list

Recommended Production Settings

# Connection pool

db.connectionPool.initialLimit=10

db.connectionPool.minLimit=10

db.connectionPool.maxLimit=50

# Statement caching

jdbc.MaxStatements=50

# Request handling

standalone.http.timeout=300

standalone.idle.timeout=300

# REST performance

restEnabledSql.active=true

Explanation

initialLimit / minLimit: Avoids connection creation delays

maxLimit: Prevents DB overload

jdbc.MaxStatements: Reduces parse overhead

DATABASE CONNECTION POOL TUNING (MOST CRITICAL)

Identify ORDS DB Sessions

SELECT COUNT(*) FROM v$session WHERE program LIKE '%ORDS%';

Common Problems

  • Pool too small → request waits
  • Pool too large → DB resource exhaustion

Best Practice

Environment
 
maxLimit
 
DEV
 
10–20
 
TEST
 
20–30
 
PROD
 
40–100 (based on load)

JVM PERFORMANCE TUNING

ORDS runs inside JVM, so heap tuning is essential.

Check current Java

java -version

ORDS 23.x / 24.x → Java 17

JVM Heap Settings (Recommended)

If using standalone ORDS:

export JAVA_OPTS="-Xms2G -Xmx4G -XX:+UseG1GC"

For systemd service:

Environment="JAVA_OPTS=-Xms2G -Xmx4G -XX:+UseG1GC"

JVM Best Practices

-Xms = Initial heap

-Xmx = Maximum heap

Use G1GC for consistent latency

ENABLE HTTP COMPRESSION

Reduces response payload size for APEX pages.

standalone.http.compression=true

standalone.http.compression.minSize=1024

✔ Faster page loads
✔ Lower bandwidth usage 

ORDS LOGGING LEVEL (IMPORTANT)

Excessive logging kills performance.

Check logging level

logging.level=INFO

Best Practice

PROD: INFO

DEBUG only for short troubleshooting

DATABASE-SIDE PERFORMANCE TUNING

APEX Parsing Schema Stats

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('APEX_230100');

Identify Slow Queries from ORDS

SELECT sql_text, elapsed_timeFROM v$sqlWHERE module LIKE '%ORDS%'ORDER BY elapsed_time DESC;

APEX-SPECIFIC PERFORMANCE TUNING

Check Workspace Activity

SELECT workspace, COUNT(*)FROM apex_workspace_activity_logGROUP BY workspace;

Common APEX Issues

  • Unindexed report querie
  • Large interactive reports
  • Excessive dynamic actions

ORDS tuning cannot fix inefficient APEX SQL.

OS-LEVEL TUNING

Increase Open Files Limit

ulimit -n

Recommended:

ulimit -n 65536

Check CPU & Memory

top

free -g

ORDS server should have:

  • Dedicated CPU
  • Sufficient RAM
  • No swap thrashin

LOAD BALANCING & SCALING (ADVANCED)

For high traffic:

  • Run multiple ORDS instances
  • Place behind load balancer
  • Use sticky sessions for APEX

Benefits:

  • Horizontal scalability
  • Zero-downtime restarts

RESTART ORDS AFTER TUNING

systemctl restart ords# or

pkill -f ords

ords –config /path/to/config serve –port <PORT>

Performance Validation Checklist

✔ Faster APEX login
✔ Reduced response time
✔ Stable DB sessions
✔ No ORDS pool exhaustion
✔ CPU & memory within limits

 Common Tuning Mistakes

  • Setting connection pool too high
  • Ignoring JVM heap size
  • Leaving DEBUG logging enabled
  • Assuming ORDS tuning fixes bad SQL
  • Running ORDS on under-sized VM

 Conclusion

ORDS performance tuning is not a single setting change—it is a layered optimization process.

By tuning:

  • ORDS configuration
  • Database connection pools
  • JVM memory
  • Database SQL and statistics

 

Recent Posts