SQL Quarantine in Oracle Database 19c

SQL Quarantine in Oracle Database 19c

 

 

Introduction

Oracle Database 19c, you can use the SQL Quarantine infrastructure (SQL Quarantine) to quarantine execution plans for SQL statements that are terminated by the Resource Manager for consuming excessive system resources in an Oracle database. An individual SQL statement may have multiple execution plans, and if it attempts to use the execution plan that is quarantined, then that SQL statement is not allowed to run, thus preventing database performance degradation.

You can use the SQL Quarantine infrastructure (SQL Quarantine) to quarantine execution plans for SQL statements that are terminated by the Resource Manager for consuming excessive system resources in an Oracle database. The quarantined execution plans for such SQL statements are not allowed to run again, thus preventing database performance degradation.

Using the Resource Manager, you can configure limits for SQL statements for consuming system resources (Resource Manager thresholds). The Resource Manager terminates SQL statements that exceed the Resource Manager thresholds. In the earlier Oracle Database releases, if a SQL statement that is terminated by the Resource Manager runs again, the Resource Manager allows it to run again and terminates it again when it exceeds the Resource Manager thresholds. Thus, it is a waste of system resources to allow such SQL statements to run again.

Starting with Oracle Database 19c, you can use SQL Quarantine to automatically quarantine execution plans of SQL statements terminated by the Resource Manager, so that they are not allowed to run again. SQL Quarantine information is periodically persisted to the data dictionary. When resource manager terminates a SQL statement, it may be several minutes before the statement is quarantined.

 

 

Purpose

 

SQL Quarantine can also be used to create quarantine configurations for execution plans of SQL statements by specifying thresholds for consuming various system resources (similar to the Resource Manager thresholds) using the DBMS_SQLQ package subprograms.

These thresholds are known as quarantine thresholds. If any of the Resource Manager thresholds is equal to or less than a quarantine threshold specified in a SQL statement's quarantine configuration, then the SQL statement is not allowed to run, if it uses the execution plan specified in its quarantine configuration.

 

The following are the steps to manually set quarantine thresholds for an execution plan for a SQL statement using the DBMS_SQLQ package subprograms:

 

v  Create a quarantine configuration for an execution plan for a SQL statement

v  Specify quarantine thresholds in the quarantine configuration

You can also perform the following operations related to quarantine configurations using the DBMS_SQLQ package subprograms:

ü  Enable or disable a quarantine configuration

ü  Delete a quarantine configuration

ü  Transfer quarantine configurations from one database to another

 

Technical Usage

 

 

Note

 

This feature is currently restricted to Enterprise Edition on Engineered Systems, like Exadata and Exadata Cloud Service, to test in non engineering environment there is a workaround for testing by enabling the "_exadata_feature_on" initialization parameter

 

SQL> alter system set "_exadata_feature_on"=true scope=spfile;

 

System altered.

 

SQL> shut immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 1191181696 bytes

Fixed Size                  8895872 bytes

Variable Size             436207616 bytes

Database Buffers          738197504 bytes

Redo Buffers                7880704 bytes

Database mounted.

Database opened.

 

SQL> l

  1  select

  2  ksppinm,

  3  ksppdesc

  4  from

  5  x$ksppi where ksppinm='_exadata_feature_on'

SQL> /

 

KSPPINM               KSPPDESC

--------------------- ----------------------------------

_exadata_feature_on   Exadata Feature On

 

 

Creating a Quarantine Configuration for an Execution Plan of a SQL Statement

 

You can create a quarantine configuration for an execution plan of a SQL statement using any of these DBMS_SQLQ package functions – 

 

CREATE_QUARANTINE_BY_SQL_ID 

or 

CREATE_QUARANTINE_BY_SQL_TEXT.

 

The following example creates a quarantine configuration for an execution plan having the hash value of 730326022 for a SQL statement having the SQL ID of 90j5b3kuq6byz:

 

DECLARE

    quarantine_config VARCHAR2(30);

BEGIN

    quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(

                            SQL_ID => '90j5b3kuq6byz',

                            PLAN_HASH_VALUE => '730326022');

END;

/

If you do not specify an execution plan or specify it as NULL, then the quarantine configuration is applied to all the execution plans of a SQL statement, except for those execution plans for which the execution plan-specific quarantine configurations are already created.

The following example creates a quarantine configuration for all the execution plans for a SQL statement having the SQL ID of 90j5b3kuq6byz:

DECLARE

    quarantine_config VARCHAR2(30);

BEGIN

    quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(

                            SQL_ID => '90j5b3kuq6byz');

END;

/

The following example creates a quarantine configuration for all the execution plans for a SQL statement 'select count(*) from emp':

DECLARE

    quarantine_config VARCHAR2(30);

BEGIN

    quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT(

                            SQL_TEXT => to_clob('select count(*) from emp'));

END;

/

 

Specifying Quarantine Thresholds in a Quarantine Configuration

DBMS_SQLQ.ALTER_QUARANTINE procedure

After creating a quarantine configuration for SQL statement, you can specify quarantine thresholds for it using the . When any of the Resource Manager thresholds is equal to or less than a quarantine threshold specified in a SQL statement's quarantine configuration, then the SQL statement is not allowed to run, if it uses the execution plan specified in its quarantine configuration.

By using DBMS_SQLQ.ALTER_QUARANTINE procedure we can create threshold based on below:

·         CPU time

·         Elapsed time

·         I/O in megabytes

·         Number of physical I/O requests

·         Number of logical I/O requests

 

In the following example, the quarantine threshold specified for CPU time is 5 seconds and elapsed time is 10 seconds for the quarantine configuration SQL_QUARANTINE_3u0anzbpmfkmd2b87e406.

BEGIN

    DBMS_SQLQ.ALTER_QUARANTINE(

       QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmd2b87e406',

       PARAMETER_NAME  => 'CPU_TIME',

       PARAMETER_VALUE => '5');

 

    DBMS_SQLQ.ALTER_QUARANTINE(

       QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmd2b87e406',

       PARAMETER_NAME  => 'ELAPSED_TIME',

       PARAMETER_VALUE => '10');

END;

/

After setting the threshold configuration, execution plan specified in this quarantine configuration, if the Resource Manager threshold for CPU time is 5 seconds or less, or elapsed time is 10 seconds or less, then the SQL statement is not allowed to run.

 

Querying quarantine thresholds for a quarantine configuration

DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE function helps to get the existing configuration

 

DECLARE

    quarantine_config_setting_value VARCHAR2(30);

BEGIN

    quarantine_config_setting_value :=

        DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE(

              QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmd2b87e406',

              PARAMETER_NAME  => 'CPU_TIME');

END;

/

 

Deleting quarantine thresholds from a quarantine configuration

You can delete a quarantine threshold from a quarantine configuration by specifying DBMS_SQLQ.DROP_THRESHOLD as the value for PARAMETER_VALUE.

 

BEGIN

    DBMS_SQLQ.ALTER_QUARANTINE(

       QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmd2b87e406',

       PARAMETER_NAME  => 'CPU_TIME',

       PARAMETER_VALUE => DBMS_SQLQ.DROP_THRESHOLD);

END;

/

Enabling and Disabling a Quarantine Configuration

You can enable or disable a quarantine configuration using the DBMS_SQLQ.ALTER_QUARANTINE procedure. A quarantine configuration is enabled by default when it is created.

 

Disable the quarantine configuration

 

BEGIN

    DBMS_SQLQ.ALTER_QUARANTINE(

       QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmd2b87e406',

       PARAMETER_NAME  => 'ENABLED',

       PARAMETER_VALUE => 'NO');

END;

/

Enables the quarantine configuration

BEGIN

    DBMS_SQLQ.ALTER_QUARANTINE(

       QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmd2b87e406',

       PARAMETER_NAME  => 'ENABLED',

       PARAMETER_VALUE => 'YES');

END;

/

Deleting a Quarantine Configuration

The unused quarantine configurations are automatically purged or deleted after 53 weeks. You can also delete a quarantine configuration using the DBMS_SQLQ.DROP_QUARANTINE procedure. You can disable automatic deletion of a quarantine configuration using the DBMS_SQLQ.ALTER_QUARANTINE procedure.

The following example disables automatic deletion of the quarantine configuration SQL_QUARANTINE_3u0anzbpmfkmd2b87e406:

BEGIN

    DBMS_SQLQ.ALTER_QUARANTINE(

       QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmd2b87e406',

       PARAMETER_NAME  => 'AUTOPURGE',

       PARAMETER_VALUE => 'NO');

END;

/

The following example enables automatic deletion of the quarantine configuration SQL_QUARANTINE_3u0anzbpmfkmd2b87e406:

BEGIN

    DBMS_SQLQ.ALTER_QUARANTINE(

       QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmd2b87e406',

       PARAMETER_NAME  => 'AUTOPURGE',

       PARAMETER_VALUE => 'YES');

END;

/

The following example deletes the quarantine configuration SQL_QUARANTINE_3u0anzbpmfkmd2b87e406:

BEGIN

    DBMS_SQLQ.DROP_QUARANTINE('SQL_QUARANTINE_3u0anzbpmfkmd2b87e406');

END;

/

 

Transferring Quarantine Configurations from One Database to Another Database

 

You can transfer quarantine configurations from one database to another database using the DBMS_SQLQ package subprograms – CREATE_STGTAB_QUARANTINE, PACK_STGTAB_QUARANTINE, and UNPACK_STGTAB_QUARANTINE.

Using SQL*Plus, connect to the source database as a user with the administrative privileges, and create a staging table using the DBMS_SQLQ.CREATE_STGTAB_QUARANTINE procedure.

 

BEGIN

  DBMS_SQLQ.CREATE_STGTAB_QUARANTINE (

    staging_table_name => 'TBL_STG_QUARANTINE');

END;

/

 

Add the quarantine configurations into the staging table, which you want to transfer to the destination database.The following example adds all the quarantine configurations starting with the name QUARANTINE_CONFIG_ into the staging table TBL_STG_QUARANTINE:

 

DECLARE

  quarantine_configs NUMBER;

BEGIN

  quarantine_configs := DBMS_SQLQ.PACK_STGTAB_QUARANTINE(

                            staging_table_name => 'TBL_STG_QUARANTINE',

                            name => 'QUARANTINE_CONFIG_%');

END;

/

The DBMS_SQLQ.PACK_STGTAB_QUARANTINE function returns the number of quarantine configurations added to the staging table.

v  Export the staging table TBL_STG_QUARANTINE to a dump file using the Oracle Data Pump Export utility.

v  Transfer to the destination database system.

v  On the destination import the staging table  and unpack

DECLARE

  quarantine_configs NUMBER;

BEGIN

  quarantine_configs := DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE(

                            staging_table_name => 'TBL_STG_QUARANTINE');

END;

/

v  The DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE function returns the number of quarantine configurations created in the destination database.

 

Creating resource Manager and test the SQL for quarantine.

 

 

[oracle@vm217 ~]$ . oraenv

ORACLE_SID = [+ASM] ? prod

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@vm217 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 22 15:08:18 2020

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> set lin200 pages 2000

SQL> set serveroutput on

SQL>

SQL>

SQL> connect / as sysdba

 

SQL> col sql_text for a69

SQL> set lin200 pages 200

SQL>

SQL> select sql_id, sql_text , plan_hash_value from v$sql where sql_text like 'select count(*)%';

 

SQL_ID        SQL_TEXT                                      AN_HASH_VALUE

------------- --------------------------------------------------------------------- ---------------

7am4w4pp3nwtm select count(*) from undo$                                                 1305802079

8r5sp8cjcpqan select count(*) from vtable$ where obj#=:1                                   92598769

8r5sp8cjcpqan select count(*) from vtable$ where obj#=:1                                   92598769

90j5b3kuq6byz select count(*) from employees emp1, employees emp2,      employees e       730326022

              mp3, employees emp4,      employees emp5, employees emp6,      employ

              ees emp7, employees emp8,      employees emp9, employees emp10 where

              rownum <= 100000000

 

2b9vqmdj1kr01 select count(*) from sys.ilmobj$ o, sys.ilmpolicy$ p where    o.polic      1226838031

              y# = p.policy# and p.action = :1 and o.obj# = :2 and p.scope = :3

 

gjaap3w3qbf8c select count(*) from ilmobj$ where rownum = 1                              4233836756

 

6 rows selected.

 

 

 

Setup Resource Manager

 

 

SQL> begin

 

  -- Create a pending area

  dbms_resource_manager.create_pending_area();

 

  -- Create a consumer group 'TEST_RUNAWAY_GROUP'

  dbms_resource_manager.create_consumer_group (

    consumer_group => 'TEST_RUNAWAY_GROUP',

    comment        => 'This consumer group limits execution time for SQL statements'

  );

 

  -- Map the sessions of the user 'HR' to the consumer group 'TEST_RUNAWAY_GROUP'

  dbms_resource_manager.set_consumer_group_mapping(

    attribute      => DBMS_RESOURCE_MANAGER.ORACLE_USER,

    value          => 'HR',

    consumer_group => 'TEST_RUNAWAY_GROUP'

  );

 

  -- Create a resource plan 'LIMIT_RESOURCE'

  dbms_resource_manager.create_plan(

    plan    => 'LIMIT_RESOURCE',

    comment => 'Terminate SQL statements after exceeding total execution time'

  );

 

  -- Create a resource plan directive by assigning the 'LIMIT_RESOURCE' plan to

  -- the 'TEST_RUNAWAY_GROUP' consumer group

  -- Specify the execution time limit of 3 seconds for SQL statements belonging to

  -- the 'TEST_RUNAWAY_GROUP' group

  dbms_resource_manager.create_plan_directive(

    plan             => 'LIMIT_RESOURCE',

    group_or_subplan => 'TEST_RUNAWAY_GROUP',

    comment          => 'Terminate SQL statements when they exceed the' ||

                        'execution time of 3 seconds',

    switch_group     => 'CANCEL_SQL',

    switch_time      => 3,

    switch_estimate  => false

  );

 

  -- Allocate resources to the sessions not covered by the currently active plan

  -- according to the OTHER_GROUPS directive

  dbms_resource_Manager.create_plan_directive(

    plan              => 'LIMIT_RESOURCE',

    group_or_subplan  => 'OTHER_GROUPS',

    comment           => 'Ignore'

  );

 

  -- Validate and submit the pending area

  dbms_resource_manager.validate_pending_area();

  dbms_resource_manager.submit_pending_area();

 

  -- Grant switch privilege to the 'HR' user to switch to the 'TEST_RUNAWAY_GROUP'

  -- consumer group

  dbms_resource_manager_privs.grant_switch_consumer_group('HR',

                                                          'TEST_RUNAWAY_GROUP',

                                                          false);

 

  -- Set the initial consumer group of the 'HR' user to 'TEST_RUNAWAY_GROUP'

  dbms_resource_manager.set_initial_consumer_group('HR',

                                                   'TEST_RUNAWAY_GROUP');

 

end;

/

 

PL/SQL procedure successfully completed.

 

 

Enable Resource Manager in test the Quarantine

 

SQL> show parameter resource

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

resource_limit                       boolean     TRUE

resource_manage_goldengate           boolean     FALSE

resource_manager_cpu_allocation      integer     3

resource_manager_plan                string

 

SQL> alter system set RESOURCE_MANAGER_PLAN = 'LIMIT_RESOURCE' ;

 

System altered.

 

SQL> show parameter resource

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

resource_limit                       boolean     TRUE

resource_manage_goldengate           boolean     FALSE

resource_manager_cpu_allocation      integer     3

resource_manager_plan                string      LIMIT_RESOURCE

 

 

[oracle@vm217 ~]$ sqlplus hr/hr

 

SQL>

SQL> select count(*)

from employees emp1, employees emp2,

     employees emp3, employees emp4,

     employees emp5, employees emp6,

     employees emp7, employees emp8,

     employees emp9, employees emp10

where rownum <= 100000000;  2    3    4    5    6    7

from employees emp1, employees emp2,

     *

ERROR at line 2:

ORA-00040: active time limit exceeded - call aborted

 

SQL>  select sql_text, name, plan_hash_value, last_executed, enabled

from dba_sql_quarantine  ;

 

SQL_TEXT                               NAME                                     PLAN_HASH_VALUE LAST_EXECUTED  ENA

-------------------------------------- ---------------------------------------- --------------- ------------- ---

select count(*)                        SQL_QUARANTINE_3u0anzbpmfkmd2b87e406           730326022               YES

from employees emp1, employees emp2,

     employees emp3, employ

 

 

Now Execute again and test

SQL>  select count(*)

from employees emp1, employees emp2,

     employees emp3, employees emp4,

     employees emp5, employees emp6,

     employees emp7, employees emp8,

     employees emp9, employees emp10

where rownum <= 100000000;  2    3    4    5    6    7

from employees emp1, employees emp2,

     *

ERROR at line 2:

ORA-56955: quarantined plan used

 

 

 

 

 

Conclusion

 

For consuming excessive system resources for user query running in an Oracle 19c engineering database, statement may have multiple execution plans, and if it attempts to use the execution plan that is quarantined, then that SQL statement is not allowed to run, thus preventing database performance degradation. This newly feature protects an Oracle Database from performance degradation by preventing execution of SQL statements that excessively consume CPU and I/O resources.