SQL Quarantine Not Taking Effect Immediately in Oracle 19c

SQL Quarantine helps prevent inefficient queries from consuming resources repeatedly. When a query exceeds resource limits (set by Oracle Resource Manager), the query is terminated, and its execution plan is quarantined, preventing it from running again.
However, there’s a common issue where SQL Quarantine doesn’t take effect immediately after a query is terminated.
Why It Happens?
SQL Quarantine has a delay between the query termination by Resource Manager and the quarantine of the execution plan. During this delay,query can still be re-executed, leading to the same error (e.g., ORA-00040). Once the quarantine is applied, future executions of the query are blocked, and ORA-56955 is returned.
Run the Query:  
Execute a resource-intensive query that exceeds the resource limits set by the Oracle Resource Manager.
SELECT COUNT(*)
FROM product p1, product p2, product p3, product p4, product p5, product p6,
     product p7, product p8, product p9, product p10
WHERE p1.product_id = p2.product_id
AND p2.product_id = p3.product_id
AND p3.product_id = p4.product_id
AND p4.product_id = p5.product_id
AND p5.product_id = p6.product_id
AND p6.product_id = p7.product_id
AND p7.product_id = p8.product_id
AND p8.product_id = p9.product_id
AND p9.product_id = p10.product_id;
Resource Manager Terminates the Query: As expected, Resource Manager will automatically terminate the query, producing an error like:
ORA-00040: active time limit exceeded - call aborted
Wait for SQL Quarantine to Take Effect: Wait for a few minutes after the query is terminated. During this time, Oracle will quarantine the execution plan for the query.
Re-run the Query: After waiting, attempt to execute the same query again:
SELECT COUNT(*)
FROM product p1, product p2, product p3, product p4, product p5, product p6,
     product p7, product p8, product p9, product p10
WHERE p1.product_id = p2.product_id
AND p2.product_id = p3.product_id
AND p3.product_id = p4.product_id
AND p4.product_id = p5.product_id
AND p5.product_id = p6.product_id
AND p6.product_id = p7.product_id
AND p7.product_id = p8.product_id
AND p8.product_id = p9.product_id
AND p9.product_id = p10.product_id;
ORA-56955: quarantined plan used
This error confirms that the query’s execution plan has been quarantined and cannot be executed anymore.