Designing Web Applications for Oracle 23ai

Designing web applications for high performance against Oracle 23ai involves architecture decisions across multiple tiers — network, middleware, and database. The main bottlenecks in a web-to-Oracle 23ai architecture typically lie not in the database engine itself, but in the layers between the client and the cloud infrastructure entry point. The recommended architecture for high-performance Oracle 23ai web applications follows a layered model that allows independent scaling of each tier:

Client → CDN / Edge → OCI Load Balancer / API Gateway → ORDS Cluster → Oracle Database 23ai

The following table ranks the most common bottlenecks by frequency and impact, with the highest-priority fixes that address each:

Rank	Bottleneck Area	Typical Impact	Priority Fix
1	Network latency / round-trips	Very High	Region proximity + API design
2	Connection creation overhead	High	ORDS pooling + DRCP
3	Load Balancer / ORDS scaling	High	Proper sizing + horizontal scale
4	TLS and HTTP overhead	Medium-High	HTTP/2 + keep-alive + TLS resumption
5	Query / payload efficiency	Medium	Indexing, pagination, caching

Network latency between the client and the OCI region hosting Oracle 23ai is frequently the primary bottleneck. Contributing factors include geographic distance, internet congestion, chatty APIs that generate many small HTTP requests, and TLS/SSL handshake overhead on short-lived connections that do not reuse TLS sessions.

Mitigations for network latency:

• Deploy ORDS in the OCI region closest to the primary user base.
• Use a CDN or edge cache for static content and frequently-read reference data.
• Use HTTP/2 or HTTP/3, keep-alive connections, and TLS session resumption to amortize handshake cost across multiple requests.
• Design REST APIs for fewer round-trips — paginated responses, bulk operations, and composite endpoints rather than multiple single-resource calls.

Repeated creation of TCP, TLS, and Oracle Net connections is the second most common bottleneck. Each new ORDS-to-database connection requires Oracle Net handshake, authentication, and session establishment — significant overhead for short-duration web requests.

ORDS JDBC Connection Pool

The ORDS JDBC connection pool is the most impactful tuning lever for web-facing Oracle applications. Key parameters:

• jdbc.MaxLimit — maximum pooled connections. Too low causes request queuing; too high exhausts database sessions. Use the Oracle database SESSIONS init parameter as the practical upper bound.
• jdbc.MinLimit — minimum connections kept open at idle. Prevents cold-start latency after idle periods.
• jdbc.InitialLimit — connections created at ORDS startup. Set equal to jdbc.MinLimit for consistent startup behavior.

Database Resident Connection Pooling (DRCP)

DRCP is Oracle's server-side connection pooling mechanism, ideal for deployments with multiple ORDS instances each maintaining their own JDBC pool. DRCP shares a pool of database server processes across all ORDS instances rather than each instance holding dedicated sessions. For Oracle 23ai with multiple ORDS nodes behind a load balancer, DRCP significantly reduces the total session count at the database tier — a common bottleneck in horizontally-scaled ORDS deployments.

Universal Connection Pool (UCP)

For Java applications connecting directly to Oracle 23ai without ORDS, Oracle's Universal Connection Pool provides JDBC connection pooling with Fast Application Notification (FAN), Application Continuity, and Oracle RAC transaction affinity support.

The OCI Load Balancer and API Gateway become bottlenecks if under-provisioned or misconfigured. DNS resolution delays and WAF policy processing also add measurable latency to every request.

• Use regional OCI Load Balancers with proper health checks against ORDS instances.
• Enable HTTP/2 on the load balancer for multiplexed request handling.
• Scale the load balancer shape or use flexible shapes that auto-scale with traffic volume.
• Deploy ORDS instances in private subnets behind the load balancer — never expose ORDS directly to the public internet.
• Use OCI Network Security Groups (NSGs) rather than Security Lists for more granular and faster-evaluated network policies.

Schema and index design decisions made at development time have the largest long-term impact on Oracle 23ai web application performance:

• Normalization: use a normalized schema to avoid redundancy and maintain integrity. Over-normalized schemas requiring many joins for common web queries benefit from materialized views or aggregate tables for summary data.
• Indexes: create indexes on frequently queried columns. Consider function-based indexes for REST API filter predicates that apply functions to column values. Oracle 23ai Autonomous Database Automatic Indexing creates, rebuilds, and drops indexes based on observed workload without manual intervention.
• Partitioning: partition large tables using range, list, hash, or interval partitioning. Partition pruning eliminates unnecessary block reads for date-filtered web queries.
• JSON Relational Duality Views: expose tables as both relational and JSON documents simultaneously — web applications consume JSON natively via ORDS REST endpoints while the database maintains relational integrity and ACID guarantees.
• True Cache: a read-only in-memory cache tier that serves frequently-read reference data without Oracle Net round-trips to the primary database — transparent to the application.
• AI Vector Search: for web applications incorporating semantic search or recommendation features, AI Vector Search allows similarity queries against embedding vectors stored directly in Oracle 23ai.
• Data Compression: Oracle 23ai offers basic table compression, OLTP compression, and Hybrid Columnar Compression (HCC) — compression improves both storage efficiency and I/O performance for web-facing tables with repetitive data.

• SQL Tuning Advisor and Explain Plan: identify bottlenecks in SQL generated by ORDS REST handlers and APEX page processes.
• Result Cache: use the RESULT_CACHE hint or CREATE RESULT CACHE for PL/SQL functions returning reference data consumed by multiple concurrent ORDS requests.
• Batch Operations: use array DML or FORALL for INSERT/UPDATE/DELETE operations from web form submissions — reduces round-trips between ORDS and the database.
• Bulk Collect: use BULK COLLECT to fetch multiple rows into a PL/SQL collection for faster retrieval when processing result sets in stored procedures.
• Stored Procedures: encapsulate complex business logic in PL/SQL to reduce data transferred between ORDS and the database and eliminate redundant SQL parsing.
• Pagination: always paginate large result sets using FETCH NEXT or the ORDS built-in ?offset= and ?limit= parameters for AutoREST endpoints. Unbounded result sets are the most common web application performance anti-pattern.

One of the real problems that emerged from using the web as a front-end is that a web page is a scrolling document rather than a fixed-height screen. Unlike a traditional client-server application that maps database output to a non-scrolling terminal window, a web application can generate an HTML page spanning hundreds of rows of Oracle output — all of which must be fetched, serialized, transmitted, and rendered before the user sees any data.

The solution is pagination implemented at the SQL level — not at the application layer after a full result set fetch. Fetching all rows and then discarding most of them in the application wastes Oracle I/O, ORDS serialization time, and network bandwidth. Implement pagination in the SQL itself using OFFSET n ROWS FETCH NEXT n ROWS ONLY, or use ORDS AutoREST pagination via the ?offset= and ?limit= query parameters.

The following table summarizes the most important design tips for Oracle 23ai web applications, updated for modern ORDS and cloud-native deployments: