Lesson 11

The web paradigm is structurally equivalent to the three-tiered client-server architecture. A browser replaces the desktop client, Oracle REST Data Services (ORDS) replaces the application server, and Oracle Database 23ai remains the database tier. The delivery mechanism changed; the Oracle engine did not. This module covered the fundamental concepts behind web-based Oracle 23ai applications and demonstrated practical techniques for maintaining Oracle performance across all three tiers.

1. Web server performance and tuning applies only from the moment a URL request is received by ORDS until the completed response is returned to the client. The DBA's tuning window is bounded by the ORDS connection pool on one side and the Oracle Net connection on the other.
2. Most web applications use alternative concurrency locking schemes because HTTP connections are stateless and database sessions cannot be held open between page views. Traditional pessimistic locking — holding SELECT FOR UPDATE locks while a user reads a page — is structurally impossible in a web application.
3. Alternative locking schemes involve re-reading rows and comparing current row contents to prior row contents at update time, using UPDATE WHERE clauses, dedicated timestamp columns (date_last_updated), ORA_ROWSCN, or Oracle 23ai lock-free reservations for high-contention numeric columns such as inventory counts and account balances.
4. Web applications should deploy ORDS on separate compute instances from Oracle Database 23ai. High CPU load on the ORDS tier must not starve the database server of resources. In OCI, ORDS instances belong in a separate subnet from the database tier, behind a load balancer.
5. Oracle 23ai provides modern methods for web connectivity: Oracle REST Data Services (ORDS) as the primary web listener and REST gateway, Oracle APEX for low-code web application development, and OCI API Gateway for enterprise API lifecycle management. The legacy Web Request Broker (WRB) and CGI have been superseded by ORDS.
6. Oracle 23ai web applications should retrieve only the data the end-user requires on a single page. Paginate results at the SQL level using FETCH NEXT or ORDS ?limit= parameters. Declaring and holding cursors open across HTTP requests is unreliable — the ORDS connection pool may assign a different database session to the next request.

This module progressed from Oracle web architecture fundamentals through request handling, tuning, application design, and concurrency management:

Lesson 1 — Tuning Oracle for Web Applications: established the foundational principle that a web-based Oracle application is still an Oracle application — the Internet serves as the delivery mechanism. Traced Oracle web technology from Oracle7.3 and CGI through Oracle 23ai, APEX, and ORDS. Documented the Oracle 23ai-specific features: JSON Relational Duality, AI Vector Search, True Cache, Automatic Indexing, and sessionless transactions.

Lesson 2 — Oracle Web Applications: detailed the 8-step web data request flow from browser HTTPS request through ORDS, Oracle Net, Oracle Listener, SQL execution, result formatting, and HTTPS response delivery. Catalogued the browser-based tools that connect to Oracle 23ai — Database Actions, APEX, ORDS, Data Studio, OML Notebooks, OCI Console, and Oracle Enterprise Manager.

Lesson 3 — Web Request Broker: mapped the legacy Oracle 8i/9i web architecture components (CGI, WRB Dispatcher, Executable Engines, PL/SQL Agent, Java Cartridge, LiveHTML Cartridge) to their Oracle 23ai equivalents (ORDS mid-tier, ORDS PL/SQL Gateway, WebLogic microservices, APEX dynamic pages). Documented the ORDS JDBC connection pool as the architectural descendant of the WRB cartridge pool — solving the same concurrency problem at the JDBC layer rather than the OS process layer.

Lesson 4 — Web Listener: described the function of the Oracle Web Listener and its evolution through Oracle 11g, 19c, and 23ai. Documented the five-step ORDS web request lifecycle (receive → route → process → format → deliver) and the three web listener options: ORDS (the only recommended option for Oracle 23ai), OHS (reverse proxy only), and EPG (deprecated).

Lesson 5 — Modern Oracle Web Integration: documented the deprecation of WRB Cartridges in WebLogic Server 12c Release 2 (12.2.1.4) and the three-layer modern replacement architecture — Request Entry Layer (ORDS, WebLogic), Routing and Mediation Layer (ORDS, API Gateway, OSB, Oracle Integration), and Execution and Extension Targets (Oracle Database 23ai, APEX, REST APIs, microservices, OCI Functions, Event-Driven Services). Provided migration paths for cloud-first, on-premises/hybrid, and modern application scenarios.

Lesson 6 — Web-Based Tuning Tips: established the three-tier tuning boundary framework — browser/client, ORDS/load balancer, Oracle Net, and Oracle Database 23ai are tuned independently with different tools and parameters. Documented ORDS JDBC pool parameters (jdbc.MaxLimit, jdbc.MinLimit, jdbc.InitialLimit), Oracle Net SDU sizing, SQLNET.EXPIRE_TIME for dead session cleanup, and True Cache for read-heavy web workloads. Included the three-tier architecture diagram comparing classic client/server with Oracle 23ai web model.

Lesson 7 — Designing Web Applications: ranked the five bottleneck areas by impact (network latency, connection creation overhead, load balancer/ORDS scaling, TLS/HTTP overhead, query/payload efficiency) and provided mitigations for each. Documented DRCP for multi-ORDS deployments, OCI Load Balancer configuration, Oracle 23ai-specific database design features (JSON Relational Duality Views, True Cache, Automatic Indexing, AI Vector Search, HCC compression), and the six design tips for Oracle 23ai web applications updated for ORDS and cloud-native deployments.

Lesson 8 — Web Application Concurrency: explained why traditional pessimistic locking fails in stateless web applications. Documented the four concurrency management strategies — no long-lived locks, optimistic locking, lock-free reservations, and request-scoped transactions. Covered the terminated connection lock problem, nonescalating row-level locking, optimistic locking via checksum/VERSION_ID/ORA_ROWSCN, Oracle 23ai RESERVABLE columns, ORDS autocommit behavior, and CLIENT_IDENTIFIER for auditing through shared connection pools.

Lesson 9 — Alternative Concurrency Mechanisms: provided a comprehensive framework for choosing among Oracle 23ai concurrency mechanisms by workload pattern. Documented MVCC read consistency as the alternative to shared locks, optimistic locking as the alternative to exclusive locks, Oracle 23ai lock-free reservations for high-contention numeric columns, Priority Transactions for workload tiering, the UPDATE WHERE optimistic update pattern, and SELECT FOR UPDATE SKIP LOCKED for non-blocking queue processing.

Lesson 10 — Date/Time Stamp: documented the timestamp-based variant of optimistic locking using a date_last_updated column. Provided a production-ready PL/SQL procedure with four status codes (SUCCESS, CONFLICT, NOT_FOUND, ERROR), PRAGMA EXCEPTION_INIT, SQL%ROWCOUNT conflict detection, and COMMIT/ROLLBACK transaction management. Compared the timestamp approach to ORA_ROWSCN, JSON Relational Duality View ETAGs, and RESERVABLE columns.

Upon completion of this module, you should be able to:

1. Describe how web applications connect to an Oracle 23ai database — the 8-step request flow from browser through ORDS, Oracle Net, Oracle Listener, SQL execution, and HTTPS response delivery.
2. Describe the function of the Oracle web architecture including ORDS, APEX, the Web Request Broker, and their modern Oracle 23ai equivalents.
3. Explain the Oracle Web Request Broker architecture and how ORDS replaced CGI, WRB, PL/SQL Agent, and all cartridge types in a single mid-tier gateway.
4. Describe how Oracle 23ai manages incoming web requests through ORDS connection pooling, request routing, sessionless transactions, and Oracle Net Services.
5. Design web applications for high performance against Oracle 23ai — addressing network latency, ORDS connection pooling, DRCP, load balancing, query optimization, and the unbounded output problem.
6. Identify and resolve locking problems with web applications — terminated connection locks, SELECT FOR UPDATE misuse in stateless environments, and nonescalating row-level lock behavior.
7. Describe and implement alternative concurrency mechanisms — optimistic locking with VERSION_ID columns and checksums, timestamp-based locking with date_last_updated, ORA_ROWSCN, Oracle 23ai lock-free reservations, Priority Transactions, and SELECT FOR UPDATE SKIP LOCKED for queue processing.

Key terms introduced or applied in this module:

1. Hypertext Markup Language (HTML) — the document format used to deliver web page content from ORDS or APEX to the browser.
2. Concurrency management — the method by which a database ensures that concurrent update transactions do not overwrite each other's changes.
3. Oracle REST Data Services (ORDS) — the Java mid-tier gateway that replaces CGI, WRB, and all legacy cartridge types; the mandatory web listener for Oracle 23ai APEX deployments.
4. Optimistic locking — a concurrency strategy that defers conflict detection to the moment of update rather than the moment of display; the standard approach for web application concurrency management.
5. Lock-free reservations — Oracle 23ai feature allowing concurrent transactions to reserve values on RESERVABLE columns without blocking each other; eliminates row-lock serialization for high-contention numeric updates.
6. Multi-Version Concurrency Control (MVCC) — Oracle's foundational read-consistency mechanism; readers see a consistent snapshot via undo without blocking writers.
7. ORA_ROWSCN — Oracle's internal System Change Number maintained at the row level; used for optimistic locking without an application-managed version column.

To complete this module, click the Quiz link below to check your knowledge of advanced execution plan techniques.
Execution Plan Techniques - Quiz