Oracle 19c : SQL Quarantine Feature

Starting Oracle 19c, SQL Quarantine features helps to prevent reuse of same execution plan which was terminated by resource managers due to resource limits. This will help not to run the rouge queries again on the database if the resource manager is active.

SQL Quarantine will need to setup configuration and define thresholds for specific execution plan for given sql_id or for all plans for sql_id. The thresholds are similar like resource manager threshold ex: cpu limit, elapsed_time limit etc. To define the thresholds you can use DBMS_SQLQ package.quarantine configuration for an execution plan for a SQL statement.

Please note, the quarantine feature does not kill the session itself, it's just flush out the plan and quarantine that SQL and its plan for ever until it configuration is in enabled state.

This feature is only available in the Cloud and EE-ES database only, not on on-prem standard/enterprise editions as per documentation, however I could create , enable and create some plan on it.

In order to create a SQL Quarantine let's first configure it.

# For SQL_ID and one of its execution plan

DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '&sql_id',
PLAN_HASH_VALUE => '&PLAN_HASH_VALUE');
END;
/

# For SQL_ID and all of its executions plans

DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '&sql_id')
END;
/

# For SQL_TEXT Only

DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXt(SQL_TEXT => to_clob('select count(*) from emp'));
END;
/

Secondly, add threshold to it. Note the values are in seconds, even for cpu its cpu_time not percentage

BEGIN
DBMS_SQLQ.ALTER_QUARANTINE(
QUARANTINE_NAME => '&SQL_QUARANTINE_ID,
PARAMETER_NAME  => 'CPU_TIME',
PARAMETER_VALUE => '20');

DBMS_SQLQ.ALTER_QUARANTINE(
QUARANTINE_NAME => '&SQL_QUARANTINE_ID,
PARAMETER_NAME  => 'ELAPSED_TIME',
PARAMETER_VALUE => '10');
END;
/

And finally, enable it

BEGIN
DBMS_SQLQ.ALTER_QUARANTINE(
QUARANTINE_NAME => '&SQL_QUARANTINE_ID,
PARAMETER_NAME  => 'ENABLED',
PARAMETER_VALUE => 'YES');
END;
/

To test it, enable the Resource manager plan and

conn hr/hrpw@ORCLPDB

@create_resourcemanagerplan.sql (check oracle document for example)

Execute the statement.

select count(*) from emp

To view which plans got quarantined etc, v$SQL view has new columns as below

select sql_text, plan_hash_value, avoided_executions, sql_quarantine
from v$sql
where sql_quarantine is not null;

select sql_text, name, plan_hash_value, last_executed, enabled
from dba_sql_quarantine;

Thanks

Suresh