notes/tiddlywiki/Oracle - SQL Quarantine - example.md

Original article

We can manually quarantine a statement based on SQL_ID or SQL_TEXT.
Both methods accept a PLAN_HASH_VALUE parameter, which allows us to quarantine a single execution plan.
If this is not specified, all execution plans for the statement are quarantined.

-- Quarantine all execution plans for a SQL_ID.
DECLARE
  l_sql_quarantine  VARCHAR2(100);
BEGIN
  l_sql_quarantine := sys.DBMS_SQLQ.create_quarantine_by_sql_id(
                        sql_id => 'gs59hr0xtjrf8'
                      );
  DBMS_OUTPUT.put_line('l_sql_quarantine=' || l_sql_quarantine);
END;
/

SQL quarantine display:

set lines 256
COLUMN sql_text FORMAT A50 TRUNC
COLUMN plan_hash_value FORMAT 999999999999
COLUMN name FORMAT A30
COLUMN enabled FORMAT A3 HEAD "Ena"
COLUMN cpu_time FORMAT A10
COLUMN io_megabytes FORMAT A10
COLUMN io_requests FORMAT A10
COLUMN elapsed_time FORMAT A10
COLUMN io_logical FORMAT A10

select 
	name, enabled,cpu_time, io_megabytes, io_requests, elapsed_time, io_logical, plan_hash_value, sql_text
from 
	dba_sql_quarantine;

The ALTER_QUARANTINE procedure allows us to alter the thresholds, to make them look more like automatically generated quarantines.
We can use the procedure to alter the following parameters:

• CPU_TIME
• ELAPSED_TIME
• IO_MEGABYTES
• IO_REQUESTS
• IO_LOGICAL
• ENABLED
• AUTOPURGE

Example of setting the CPU_TIME threshold for the manually created quarantines:

BEGIN
  DBMS_SQLQ.alter_quarantine(
    quarantine_name  =>  'SQL_QUARANTINE_8zpc9pwdmb8vr',
    parameter_name   =>  'CPU_TIME',
    parameter_value  =>  '1');
 END;
/