ORMs, SQL Visibility, N+1, Pagination, Schema Migrations, and Deployment Coordination

Learning outcomes

ServiceHub adopts an Object-Relational Mapper (ORM). Unit tests pass, but production executes one technician query per returned work order, deep pagination gets slower, and an automatic migration waits on a DDL lock while old application instances still expect the previous schema. An ORM is an application abstraction; Oracle still executes SQL, holds locks, uses plans, and enforces schema contracts.

1. Make generated SQL observable

BEGIN  DBMS_APPLICATION_INFO.SET_MODULE(    'SH27_ORM_API','LIST_WORK_ORDERS'  );  DBMS_SESSION.SET_IDENTIFIER('req-orm-001');END;/

SELECT  sql_id, executions, parse_calls, rows_processed,  buffer_gets, elapsed_time,  substr(sql_text,1,160) AS sql_textFROM v$sqlWHERE module='SH27_ORM_API'ORDER BY last_active_time DESC;

Capture generated SQL with bind types/values as permitted by security policy. The optimizer sees SQL and statistics, not entity classes.

2. Build an N+1/pagination dataset

BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh27_orm_order PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh27_orm_tech PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE sh27_orm_tech (  technician_id NUMBER PRIMARY KEY,  technician_name VARCHAR2(80) NOT NULL);CREATE TABLE sh27_orm_order (  work_order_id NUMBER PRIMARY KEY,  technician_id NUMBER NOT NULL    REFERENCES sh27_orm_tech(technician_id),  status_code VARCHAR2(12) NOT NULL,  created_at TIMESTAMP NOT NULL);INSERT INTO sh27_orm_techSELECT LEVEL,'Tech '||LEVELFROM dual CONNECT BY LEVEL <= 100;INSERT INTO sh27_orm_orderSELECT  LEVEL,  MOD(LEVEL-1,100)+1,  CASE MOD(LEVEL,3) WHEN 0 THEN 'CLOSED' ELSE 'OPEN' END,  TIMESTAMP '2026-01-01 00:00:00'    + NUMTODSINTERVAL(LEVEL,'MINUTE')FROM dual CONNECT BY LEVEL <= 500;CREATE INDEX sh27_orm_order_page_ixON sh27_orm_order(created_at,work_order_id);COMMIT;BEGIN  DBMS_STATS.GATHER_TABLE_STATS(USER,'SH27_ORM_ORDER',cascade=>TRUE);  DBMS_STATS.GATHER_TABLE_STATS(USER,'SH27_ORM_TECH');END;/

3. Deliberate N+1

orders = orm.query(    "select work_order_id, technician_id "    "from sh27_orm_order where status_code = :status",    status="OPEN")for order in orders:    technician = orm.query_one(        "select technician_name from sh27_orm_tech "        "where technician_id = :id",        id=order.technician_id    )

One parent query plus N child queries creates N+1 database executions/round trips. Statement caching may reduce parsing but does not remove those executions.

4. Set-oriented repair

SELECT  o.work_order_id,  o.created_at,  t.technician_id,  t.technician_nameFROM sh27_orm_order oJOIN sh27_orm_tech t  ON t.technician_id=o.technician_idWHERE o.status_code='OPEN'ORDER BY o.created_at,o.work_order_id;

Use the ORM's eager-load/fetch-join or a purposeful query. For one-to-many collections, one huge join can duplicate parent columns heavily, so two batched set queries may be better. Measure.

5. OFFSET/FETCH needs deterministic total order

SELECT  work_order_id, technician_id, created_atFROM sh27_orm_orderWHERE status_code='OPEN'ORDER BY created_at,work_order_idOFFSET :offset_rows ROWSFETCH NEXT :page_size ROWS ONLY;

The PK tiebreaker makes the order deterministic. Deep OFFSET still requires finding and discarding earlier ordered rows, so work can grow with page depth.

6. Keyset pagination seeks after the last key

SELECT  work_order_id, technician_id, created_atFROM sh27_orm_orderWHERE status_code='OPEN'  AND (       created_at > :last_created_at       OR (            created_at = :last_created_at            AND work_order_id > :last_work_order_id          )      )ORDER BY created_at,work_order_idFETCH FIRST :page_size ROWS ONLY;

Keyset pagination fits next/previous feeds and continuation tokens; it does not naturally provide arbitrary “jump to page 847.” The matching ordered index can let Oracle seek rather than skip a huge prefix.

7. Prove actual row-source work

SELECT /*+ gather_plan_statistics */  work_order_id, created_atFROM sh27_orm_orderWHERE status_code='OPEN'  AND (       created_at > :last_created_at       OR (            created_at = :last_created_at            AND work_order_id > :last_work_order_id          )      )ORDER BY created_at,work_order_idFETCH FIRST 25 ROWS ONLY;SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE'  ));

Compare actual rows, buffers, starts, and access path at representative continuation points before declaring the ORM or pagination style fast/slow.

8. DDL is lock-taking production work

Schema DDL needs object/metadata locks. An ORM migration framework can generate valid ALTER TABLE and still fail because a production transaction owns a conflicting DML/DDL lock.

9. Reproduce ORA-00054

UPDATE sh27_orm_orderSET status_code='OPEN'WHERE work_order_id=1;-- Hold the transaction open.

ALTER SESSION SET ddl_lock_timeout=3;ALTER TABLE sh27_orm_order  ADD (priority_label VARCHAR2(20));-- Typical result after the bounded wait:-- ORA-00054: resource busy and acquire with NOWAIT specified-- or timeout expired

The exact current message can include blocker/session details. The mechanism is lock conflict; simply increasing DDL_LOCK_TIMEOUT can turn a fast failure into a longer deployment stall.

10. Diagnose the owner of the lock

SELECT  sid, serial#, username, module,  status, event, blocking_session, sql_idFROM v$sessionWHERE username IN ('SERVICEHUB_OWNER','SERVICEHUB_APP')ORDER BY sid;SELECT  session_id, owner, name, type,  mode_held, mode_requestedFROM dba_ddl_locksWHERE name='SH27_ORM_ORDER';

Use module/action/request identity to find the owner before killing anything. Killing a large transaction can trigger a long rollback and worsen the deployment.

11. Repair and perform the expand step

ROLLBACK;

ALTER SESSION SET ddl_lock_timeout=10;ALTER TABLE sh27_orm_order  ADD (priority_label VARCHAR2(20));

The nullable column expands the schema while old code that ignores it remains compatible.

12. Backfill in bounded batches

UPDATE sh27_orm_orderSET priority_label =  CASE    WHEN MOD(work_order_id,5)=0 THEN 'HIGH'    ELSE 'NORMAL'  ENDWHERE priority_label IS NULL  AND work_order_id BETWEEN :low_id AND :high_id;COMMIT;

Choose batch size from measured undo/redo/lock/replication latency, not from an ORM migration generator's convenience.

13. Enforce/validate after writers conform

ALTER TABLE sh27_orm_order  ADD CONSTRAINT sh27_priority_ck  CHECK (priority_label IN ('NORMAL','HIGH'))  ENABLE NOVALIDATE;-- After backfill and application writers comply:ALTER TABLE sh27_orm_order  ENABLE VALIDATE CONSTRAINT sh27_priority_ck;

ENABLE NOVALIDATE can enforce compliant new/changed rows before historical validation. Validation is an operational step with its own scan/resource/lock behavior and must be tested.

14. Contract only after old binaries and pools are gone

1. Expand the schema compatibly.
2. Deploy code able to tolerate both schema states and write the new shape.
3. Backfill and validate.
4. Prove old SQL/application versions no longer depend on the old contract.
5. Drain/recycle old pods/processes and their connection pools.
6. Contract/drop old columns/indexes/views in a separate deployment.

A pool can keep old application code/session state alive after DDL finishes. Schema compatibility is a fleet property, not merely “the migration table says success.”

15. Deliberately wrong: rename/drop in the same release as replacement

Old instances still serving traffic get invalid-identifier failures and application rollback cannot restore compatibility because the old schema contract vanished. Expand/contract preserves rolling-deploy and rollback windows.

16. Cleanup and production judgment

DROP TABLE sh27_orm_order PURGE;DROP TABLE sh27_orm_tech PURGE;BEGIN  DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);  DBMS_SESSION.CLEAR_IDENTIFIER;END;/

Log/trace ORM-generated SQL safely, bind values, measure executions/round trips/plans before tuning, and treat schema migration as live database work with lock, undo/redo, compatibility, and rollback consequences. Separate expand from contract and drain old connection pools before destructive DDL.

The mandatory lesson uses core Free SQL. DDL_LOCK_TIMEOUT is changed only at session scope; no pack, restart, or COMPATIBLE change is required. Chapter 28 can now apply the same compatibility/rollback discipline to database patching and upgrades.

Authoritative references

• SQL Tuning Guide — runtime SQL/plan evidence
• SELECT — row limiting OFFSET/FETCH
• DDL_LOCK_TIMEOUT — bounded DDL lock wait
• DBA_DDL_LOCKS — DDL lock evidence
• ODP.NET New Features — current ODP.NET/EF provider context