8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 19c » Here

SQL Quarantine in Oracle Database 19c

Oracle Database 19c introduced an extension of Runaway Query Management called SQL Quarantine.

Cancelling a runaway query is helpful to prevent wasting system resources, but if that problem query is run repeatedly, it could still result in a considerable amount of wasted resources. SQL Quarantine solves this problem by quarantining cancelled SQL statements, so they can't be run multiple times.

This feature is only available on Engineered Systems such as Exadata and Exadata Cloud Service.

Related articles.

Prerequisites

This feature is currently restricted to Enterprise Edition on Engineered Systems, like Exadata and Exadata Cloud Service, as described here. There is a workaround for testing by enabling the "_exadata_feature_on" initialisation parameter. Don't use this on a real instance or you will be breaking your license agreement.

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

exit;
EOF

Remember to reset this parameter once you have finished testing SQL Quarantine.

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

alter system reset "_exadata_feature_on" scope=spfile;
shutdown immediate;
startup;

exit;
EOF

Automatic SQL Quarantine

SQL Quarantine is available by default, so we don't have to do anything special to start using it. A normal resource plan for Runaway Query Management will trigger it. We will demonstrate this using the example we used in the 12c Runaway Query Management article here.

We create a test user.

CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA

--DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE PROCEDURE TO testuser1;

In the following example we have a resource plan with a consumer group called NORMAL_CG for normal sessions. Any sessions with the NORMAL_CG consumer group that have a single call using CPU for more than 60 seconds will have that SQL cancelled.

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  -- Create plan
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'long_running_query_plan',
    comment => 'Plan to handle long running queries.');

  -- Create consumer groups
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'normal_cg',
    comment        => 'Consumer group for normal sessions.');

  -- Assign consumer groups to plan and define priorities
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                 => 'long_running_query_plan',
    group_or_subplan     => 'normal_cg',
    comment              => 'Normal Priority',
    mgmt_p1              => 100,
    switch_group         => 'CANCEL_SQL',
    switch_time          => 60,
    switch_for_call      => TRUE);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'long_running_query_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'Default',
    mgmt_p2          => 100);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

We allow the TESTUSER1 user to switch to the NORMAL_CG consumer group, then we set the initial consumer group for the TESTUSER1 user to NORMAL_CG.

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'testuser1',
    consumer_group => 'normal_cg',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('testuser1', 'normal_cg');
END;
/

Finally we activate the plan.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'long_running_query_plan';

We can monitor the current consumer group assignment with the following query.

COLUMN username FORMAT A30
COLUMN resource_consumer_group FORMAT A30

SELECT username, resource_consumer_group
FROM   v$session
WHERE  username = 'TESTUSER1';

Leave this session open so we can monitor the progress.

In a separate session we connect to the test user, create a function that just sits on CPU for the specified number of minutes, then query the function.

CONN testuser1/testuser1@//localhost:1521/pdb1

CREATE OR REPLACE FUNCTION burn_cpu (p_mins IN NUMBER)
  RETURN NUMBER
AS
  l_start_time DATE;
  l_number     NUMBER := 1;
BEGIN
  l_start_time := SYSDATE;
  LOOP
    EXIT WHEN SYSDATE - l_start_time > (p_mins/24/60);
    l_number := l_number + 1;
  END LOOP;
  RETURN 0;
END;
/

SELECT burn_cpu (5) FROM dual;

After approximately 60 seconds the query calling the BURN_CPU function has been cancelled.

SQL> SELECT burn_cpu (5) FROM dual;

Error starting at line : 1 in command -
SELECT burn_cpu (5) FROM dual
Error report -
ORA-00040: active time limit exceeded - call aborted

SQL>

The documentation would have you believe the cancelled call is instantly quarantined, but in reality it can take a long time for it to be noticed. In some cases I waited for over 15 minutes before the cancelled statement was quarantined. Clearly some people have been confused by this and tried to log it as a bug. The response was just wait.

If you wait long enough, the execution plan that resulted in the cancelled call will be quarantined and subsequent attempts to run a statement with the same execution plan will result in a quarantine message.

SQL> SELECT burn_cpu (5) FROM dual;

Error starting at line : 1 in command -
SELECT burn_cpu (5) FROM dual
Error report -
ORA-56955: quarantined plan used

SQL>

Views

We can see the result of the quarantine action in the V$SQL and DBA_SQL_QUARANTINE views.

The V$SQL view includes the SQL_QUARANTINE and AVOIDED_EXECUTIONS columns. We see two entries for the SQL_ID, one showing the number of AVOIDED_EXECUTIONS as 0, and one showing it as 1.

COLUMN sql_text FORMAT A30
COLUMN sql_quarantine FORMAT A40

SELECT sql_text, sql_id, plan_hash_value, child_number, sql_quarantine, avoided_executions 
FROM   v$sql
WHERE  sql_quarantine IS NOT NULL;

SQL_TEXT                       SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER SQL_QUARANTINE                           AVOIDED_EXECUTIONS
------------------------------ ------------- --------------- ------------ ---------------------------------------- ------------------
SELECT burn_cpu (5) FROM dual  gs59hr0xtjrf8      1388734953            0 SQL_QUARANTINE_8zpc9pwdmb8vr125daea2                      1
SELECT burn_cpu (5) FROM dual  gs59hr0xtjrf8      1388734953            1 SQL_QUARANTINE_8zpc9pwdmb8vr125daea2                      0

SQL>

The DBA_SQL_QUARANTINE view shows us details of the SQL quarantine definition.

COLUMN sql_text FORMAT A30
COLUMN name FORMAT A40

SELECT sql_text, name, plan_hash_value, enabled
FROM   dba_sql_quarantine;

SQL_TEXT                       NAME                                     PLAN_HASH_VALUE ENA
------------------------------ ---------------------------------------- --------------- ---
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr125daea2           308129442 YES

SQL>


COLUMN sql_text FORMAT A30
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 sql_text, cpu_time, io_megabytes, io_requests, elapsed_time, io_logical
FROM   dba_sql_quarantine;

SQL_TEXT                       CPU_TIME   IO_MEGABYT IO_REQUEST ELAPSED_TI IO_LOGICAL
------------------------------ ---------- ---------- ---------- ---------- ----------
SELECT burn_cpu (5) FROM dual  60

SQL>

Notice the last query shows the threshold that was broken to cause the query to be cancelled and therefore quarantined in the first place. In this case it was 60 seconds on CPU time.

DBMS_SQLQ : SQL Quarantine Management

The DBMS_SQLQ package provides and API to manage SQL Quarantine.

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.

Here are some examples of manually quarantining a statement.

SET SERVEROUTPUT ON

-- Quarantine all execution plans for a SQL statement.
DECLARE
  l_sql_quarantine  VARCHAR2(100);
BEGIN
  l_sql_quarantine := sys.DBMS_SQLQ.create_quarantine_by_sql_text(
                        sql_text => TO_CLOB('SELECT burn_cpu (5) FROM dual')
                      );
  DBMS_OUTPUT.put_line('l_sql_quarantine=' || l_sql_quarantine);
END;
/

-- Quarantine a specific execution plan for a SQL statement.
DECLARE
  l_sql_quarantine  VARCHAR2(100);
BEGIN
  l_sql_quarantine := sys.DBMS_SQLQ.create_quarantine_by_sql_text(
                        sql_text        => TO_CLOB('SELECT burn_cpu (5) FROM dual'),
                        plan_hash_value => '1388734953'
                      );
  DBMS_OUTPUT.put_line('l_sql_quarantine=' || l_sql_quarantine);
END;
/

-- 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;
/

-- Quarantine a specific execution plan 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',
                        plan_hash_value => '1388734953'
                      );
  DBMS_OUTPUT.put_line('l_sql_quarantine=' || l_sql_quarantine);
END;
/

Manually created quarantines have all thresholds set to ALWAYS.

SELECT sql_text, name, plan_hash_value, cpu_time, io_megabytes, io_requests, elapsed_time, io_logical
FROM   dba_sql_quarantine;

SQL_TEXT                       NAME                                     PLAN_HASH_VALUE CPU_TIME   IO_MEGABYT IO_REQUEST ELAPSED_TI IO_LOGICAL
------------------------------ ---------------------------------------- --------------- ---------- ---------- ---------- ---------- ----------
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr                             ALWAYS     ALWAYS     ALWAYS     ALWAYS     ALWAYS
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr125daea2           308129442 60                            
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr52c669e9          1388734953 ALWAYS     ALWAYS     ALWAYS     ALWAYS     ALWAYS

SQL>

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.

Here's an example of setting the CPU_TIME threshold for the manually created quarantines.

BEGIN
  sys.DBMS_SQLQ.alter_quarantine(
    quarantine_name  =>  'SQL_QUARANTINE_8zpc9pwdmb8vr',
    parameter_name   =>  'CPU_TIME',
    parameter_value  =>  '60');

  sys.DBMS_SQLQ.alter_quarantine(
    quarantine_name  =>  'SQL_QUARANTINE_8zpc9pwdmb8vr52c669e9',
    parameter_name   =>  'CPU_TIME',
    parameter_value  =>  '60');
END;
/

SELECT sql_text, plan_hash_value, cpu_time, io_megabytes, io_requests, elapsed_time, io_logical
FROM   dba_sql_quarantine;

SQL_TEXT                       NAME                                     PLAN_HASH_VALUE CPU_TIME   IO_MEGABYT IO_REQUEST ELAPSED_TI IO_LOGICAL
------------------------------ ---------------------------------------- --------------- ---------- ---------- ---------- ---------- ----------
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr                             60            
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr125daea2           308129442 60            
SELECT burn_cpu (5) FROM dual  SQL_QUARANTINE_8zpc9pwdmb8vr52c669e9          1388734953 60            

SQL>

Before you drop the quarantine definitions you may want to take a copy of them, as discussed in the following section.

We drop quarantines using the DROP_QUARANTINE procedure.

BEGIN
  sys.DBMS_SQLQ.drop_quarantine('SQL_QUARANTINE_8zpc9pwdmb8vr');
  sys.DBMS_SQLQ.drop_quarantine('SQL_QUARANTINE_8zpc9pwdmb8vr125daea2');
  sys.DBMS_SQLQ.drop_quarantine('SQL_QUARANTINE_8zpc9pwdmb8vr52c669e9');
END;
/

Transfer Quarantine Definitions

The DBMS_SQLQ package allows us to transfer SQL quarantine definitions between databases.

We create a staging table using the CREATE_STGTAB_QUARANTINE procedure. The STAGING_TABLE_OWNER and TABLESPACE_NAME parameters default to NULL, which means the table will be created in the current schema, and the default tablespace for that schema.

BEGIN
  sys.DBMS_SQLQ.create_stgtab_quarantine(staging_table_name  => 'my_quarantine_defs',
                                         staging_table_owner => 'testuser1',
                                         tablespace_name     => NULL);
END;
/

We transfer the SQL quarantine definitions to the staging table using the PACK_STGTAB_QUARANTINE function. By default it captures all the definitions. We could limit this using the NAME, SQL_TEST and ENABLED parameters. In the example below we use the default values. We could just omit these three parameters.

SET SERVEROUTPUT ON
DECLARE
  l_number  NUMBER;
BEGIN
  l_number  := sys.DBMS_SQLQ.pack_stgtab_quarantine (
    staging_table_name  => 'my_quarantine_defs',
    staging_table_owner => 'testuser1',
    name                => '%',
    sql_text            => '%',
    enabled             => NULL);
  DBMS_OUTPUT.put_line('l_number=' || l_number);
END;
/
l_number=3


PL/SQL procedure successfully completed.

SQL>

The table can be transferred to another database using a table-level export and import.

Once the table is transferred to the new database, the definitions can be loaded using the UNPACK_STGTAB_QUARANTINE procedure. The parameters and their actions are similar to those of the PACK_STGTAB_QUARANTINE function.

SET SERVEROUTPUT ON
DECLARE
  l_number  NUMBER;
BEGIN
  l_number  := sys.DBMS_SQLQ.unpack_stgtab_quarantine (
    staging_table_name  => 'my_quarantine_defs',
    staging_table_owner => 'testuser1',
    name                => '%',
    sql_text            => '%',
    enabled             => NULL);
  DBMS_OUTPUT.put_line('l_number=' || l_number);
END;
/
l_number=3


PL/SQL procedure successfully completed.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.