Oracle SQL Quarantine: Automatic Guardrails for Runaway Queries in 19c+

The Problem: Resource Manager Limits Without Per-Statement Enforcement

You've set MAX_ELAPSED_TIME and MAX_CPU_TIME in your Resource Manager plan, but a poorly written report still manages to consume 90% of CPU for 45 minutes. The directive fires, the session gets switched to a low-priority consumer group, and the query keeps running — just slower. Meanwhile, other sessions starve. Traditional Resource Manager directives control consumer groups, not individual SQL statements. If a single SQL_ID slips through because it's executed by a high-priority user, there's no automatic per-statement brake.

Oracle 19c (19.7+) introduced SQL Quarantine to close this gap. Integrated with Resource Manager, it automatically creates guardrails for specific SQL IDs that breach configured thresholds, then enforces throttle, terminate, or log-only actions on subsequent executions. This post walks through how it works, a practical setup, and where the feature falls short.

How SQL Quarantine Fits Into Resource Manager

SQL Quarantine isn't a standalone feature — it's an extension of Resource Manager. You need an active plan (RESOURCE_MANAGER_PLAN set) with directives that specify quarantine-eligible limits: MAX_ELAPSED_TIME, MAX_CPU_TIME, MAX_IO_REQUESTS, or MAX_LOGICAL_READS. When a statement exceeds one of these limits, Resource Manager can automatically create a quarantine entry for that SQL_ID in the data dictionary (DBA_SQL_QUARANTINE, exposed via V$SQL_QUARANTINE).

Three enforcement actions exist:

• THROTTLE — reduces degree of parallelism (DOP) and PGA limit for the statement.
• TERMINATE — kills the execution with ORA-56955.
• LOG_ONLY — records the breach but lets the statement run (useful for observation).

Quarantines persist across restarts and are PDB-aware in multitenant: each PDB maintains its own catalog, though a CDB-level plan can propagate defaults. Management requires the QUARANTINE privilege (granted via DBMS_RESOURCE_MANAGER_PRIVS) or ADMINISTER RESOURCE MANAGER.

Worked Example: Quarantining a Runaway Report Query

Suppose a nightly reporting query (SQL_ID abc123) occasionally runs for hours due to a Cartesian join on skewed data. You want to cap it at 5 minutes without rewriting the SQL immediately.

Step 1: Verify Prerequisites

Run as a user with ADMINISTER RESOURCE MANAGER (typically SYS or a DBA role) in the target PDB:

-- Confirm version supports SQL Quarantine (19.7+, 21c, 23c)
SELECT * FROM V$VERSION WHERE BANNER LIKE 'Oracle Database 19c%'
   OR BANNER LIKE 'Oracle Database 21c%'
   OR BANNER LIKE 'Oracle Database 23c%';

-- Verify Resource Manager is active
SHOW PARAMETER RESOURCE_MANAGER_PLAN;
-- Should return a plan name, e.g., DAYTIME_PLAN

-- Check you have the QUARANTINE privilege
SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE IN ('ADMINISTER RESOURCE MANAGER', 'QUARANTINE');

Risk: If RESOURCE_MANAGER_PLAN is not set, automatic quarantine creation will never fire. Manual creation still works but defeats the "automatic guardrail" value.

Step 2: Create the Quarantine Manually (Immediate Control)

While automatic creation is the long-term goal, manual creation lets you act instantly on a known offender:

BEGIN
  DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(
    sql_id          => 'abc123',
    plan_name       => 'DAYTIME_PLAN',  -- your active Resource Manager plan
    action          => 'THROTTLE',      -- or 'TERMINATE' for hard stop
    max_elapsed_time => 300             -- seconds (5 minutes)
  );
END;
/

Where to run: In the PDB where the query executes. In a CDB, connect to the PDB first (ALTER SESSION SET CONTAINER = pdb_name;).

Required permission: EXECUTE on DBMS_SQLQ (granted via QUARANTINE privilege).

Step 3: Verify Enforcement

After the next execution attempt, check the quarantine catalog:

SELECT sql_id,
       quarantine_type,
       action,
       max_elapsed_time,
       enabled,
       quarantine_count,
       quarantine_reason
FROM   v$sql_quarantine
WHERE  sql_id = 'abc123';

Expect to see QUARANTINE_COUNT increment and QUARANTINE_REASON showing "ELAPSED_TIME" (or CPU/IO/LOGICAL_READS). If ACTION = 'THROTTLE', the statement runs with reduced DOP/PGA; if 'TERMINATE', the session receives ORA-56955.

Step 4: Adjust Without Dropping (21c+)

In 21c and later, you can tune thresholds on the fly:

BEGIN
  DBMS_SQLQ.ALTER_QUARANTINE(
    sql_id          => 'abc123',
    plan_name       => 'DAYTIME_PLAN',
    max_elapsed_time => 180  -- tighten to 3 minutes
  );
END;
/

No plan reload required. Dropping is immediate too: DBMS_SQLQ.DROP_QUARANTINE(sql_id => 'abc123', plan_name => 'DAYTIME_PLAN');.

Trade-offs and Limitations You'll Hit

• Per-SQL_ID granularity: A plan change (new bind set, adaptive plan flip, hint) that generates a new SQL_ID bypasses the existing quarantine. You'll need to capture the new ID separately.
• THROTTLE is best-effort: Reducing DOP and PGA doesn't guarantee the statement finishes within the limit — skewed data or serial operations can still exceed it.
• RAC propagation lag: Quarantine metadata syncs via the library cache. A statement executing on a node that hasn't yet loaded the quarantine may run once unchecked.
• No SQL rewriting: Quarantine enforces runtime limits; it doesn't fix missing indexes, stale statistics, or Cartesian joins. Root-cause tuning remains separate work.
• Version floor: Only 19.7+, 21c, 23c. Not backported to 12c/18c.

Actionable Closing: Put It in Production Safely

1. Enable Resource Manager with a plan that includes quarantine-eligible directives (MAX_ELAPSED_TIME, MAX_CPU_TIME, etc.) for your critical consumer groups.
2. Grant QUARANTINE privilege to your DBA team via DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE.
3. Start with LOG_ONLY on a test workload to observe which SQL IDs breach thresholds without impacting users.
4. Promote to THROTTLE for repeat offenders, then TERMINATE for hard limits on known runaway patterns.
5. Monitor V$SQL_QUARANTINE weekly — look for rising QUARANTINE_COUNT as a signal that application SQL needs tuning, not just guarding.

SQL Quarantine won't replace query tuning, but it gives you a safety net that activates automatically when a statement crosses the line — buying you time to fix the root cause without the instance grinding to a halt.