Active Session Pool, Queueing, Undo/Execution Limits, and Runaway Query Control

Learning outcomes

A reporting service launches 40 heavy calls at once. Each call is individually “valid,” but together they evict OLTP cache, saturate TEMP and dominate CPU. Workload governance must sometimes decide when a call may begin, how much uncommitted work/PGA it may hold, and what happens when a statement runs beyond policy. These are admission and containment controls—not replacements for SQL tuning.

1. Active session pool is database-call admission control

ACTIVE_SESS_POOL_P1 limits the number of concurrently active calls in a consumer group. Extra sessions remain connected but calls wait in an inactive-session queue. QUEUEING_P1 sets how long a queued call may wait before Oracle returns ORA-07454.

2. Entitled batch directive with bounded admission

BEGIN  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(    plan                     => 'SH21_DAY_PLAN',    group_or_subplan         => 'SH21_BATCH',    new_active_sess_pool_p1  => 2,    new_queueing_p1          => 30,    new_parallel_degree_limit_p1 => 4,    new_parallel_server_limit    => 30,    new_session_pga_limit        => 512,    new_undo_pool                => 204800);  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();END;/

The lab numbers mean at most two active batch calls, queue timeout 30 seconds, DOP cap 4, up to 30% of the parallel-server target, 512 MB PGA per session and a 200 MB aggregate uncommitted undo pool for the group. They are examples—not safe defaults.

3. Observe queue pressure

SELECT  name,  active_sessions,  execution_waiters,  requests,  requests_limit,  queue_length,  queue_time,  cpu_wait_time,  consumed_cpu_timeFROM v$rsrc_consumer_groupWHERE name IN ('SH21_BATCH','SH21_ANALYTICS')ORDER BY name;

Queued sessions still consume connections and some session memory. A growing queue can mean governance is protecting the database, or that the scheduled workload exceeds capacity/SLO. Measure both queue wait and job completion time.

4. Queue timeout produces ORA-07454

If both batch slots are occupied longer than QUEUEING_P1, a third queued call can fail with:

-- ORA-07454: queue timeout, 30 second(s), exceeded

The safe response is not to blindly raise the pool/timeout. Re-run later, stagger jobs, reduce per-job cost, increase capacity if justified, or revise the SLO after evidence.

5. Estimated execution limit rejects work before it starts

MAX_EST_EXEC_TIME compares the optimizer's estimated CPU seconds to a directive limit. If the estimate exceeds the limit, Oracle rejects the call with ORA-07455. The estimate depends on statistics/model quality and can be imprecise, so this is a coarse guardrail.

BEGIN  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(    plan                 => 'SH21_DAY_PLAN',    group_or_subplan     => 'SH21_ANALYTICS',    new_max_est_exec_time => 300);  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();END;/-- Possible governed rejection:-- ORA-07455: estimated execution time (...) exceeds limit (300 secs)

6. Runtime switching/cancel needs the right metric

SWITCH_TIME, logical-I/O and physical-I/O switch thresholds depend on Real-Time SQL Monitoring availability for enforcement in current Oracle documentation. That can intersect management-pack configuration. SWITCH_ELAPSED_TIME does not require Real-Time SQL Monitoring, making it a clearer core example.

BEGIN  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(    plan                    => 'SH21_DAY_PLAN',    group_or_subplan        => 'SH21_ANALYTICS',    new_switch_group        => 'CANCEL_SQL',    new_switch_elapsed_time => 120,    new_switch_for_call     => TRUE);  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();END;/

CANCEL_SQL cancels the current call but preserves the session. KILL_SESSION terminates the session and is more disruptive. LOG_ONLY relies on SQL monitoring and is not a pack-neutral enforcement example.

7. Undo and PGA limits protect shared capacity

UNDO_POOL is an aggregate consumer-group limit in kilobytes for uncommitted transaction undo. When exceeded, the current DML statement fails and other group members cannot generate more DML undo until space is freed. SESSION_PGA_LIMIT limits PGA per session (megabytes in the Resource Manager API) and protects against a runaway sort/hash/PLSQL allocation.

These limits can fail valid business work. They require application retry/rollback behavior and realistic peak tests.

8. Free admission-control simulation

Free cannot enforce Resource Manager queues, but a batch coordinator can implement a database-visible token model to learn the admission concept. This is deliberately an application control, not a Resource Manager substitute.

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_batch_slots PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_batch_slots (  slot_id NUMBER PRIMARY KEY,  holder  VARCHAR2(64),  acquired_at TIMESTAMP);INSERT INTO servicehub_batch_slots(slot_id) VALUES(1);INSERT INTO servicehub_batch_slots(slot_id) VALUES(2);COMMIT;

SELECT slot_idFROM servicehub_batch_slotsWHERE holder IS NULLORDER BY slot_idFETCH FIRST 1 ROW ONLYFOR UPDATE SKIP LOCKED;-- If one row is returned:UPDATE servicehub_batch_slotsSET holder='MONTH_END_JOB',    acquired_at=SYSTIMESTAMPWHERE slot_id=:slot_id;COMMIT;

A coordinator can refuse/delay a third job when no slot is free. This protects workload concurrency at the application layer but cannot schedule CPU among arbitrary sessions the way Resource Manager does.

9. Deliberately wrong: kill every query that runs longer than 10 seconds

Elapsed time includes I/O, locks, client/network and legitimate large work. A universal threshold can cancel backups, DDL, batch and incident diagnostics. Governance must be workload-specific and measured. Prefer cancel/switch for one class and keep emergency/admin classes separately controlled.

10. Cleanup

DROP TABLE servicehub_batch_slots PURGE;

11. Production judgment

Admission control protects shared progress by limiting concurrent expensive work, but it increases queue latency. Use active-session pools for suitable batch/analytics—not OLTP pooling. Prefer canceling the offending call over killing the session when recovery semantics permit. Treat ORA-07454/07455 as explicit policy signals surfaced to schedulers and operators.

Resource Manager remains unavailable in Free. Some switch metrics depend on Real-Time SQL Monitoring/management-pack state; the chapter deliberately uses SWITCH_ELAPSED_TIME for the pack-independent runtime-control example. Lesson 4 uses services to make these workload classes routable and observable across single-instance, RAC and Data Guard topologies.

Authoritative references

• Resource Manager — Active Session Pool and Runaway Queries — queue/undo/switch behavior
• DBMS_RESOURCE_MANAGER — directive units and reserved switch groups
• ORA-07454 — queue-timeout meaning
• ORA-07455 — estimated execution limit
• V$RSRC_CONSUMER_GROUP — queue/resource metrics