161 DBMS_SQLQ

The DBMS_SQLQ package provides the interface for configuring quarantine thresholds for SQL statements and their execution plans. SQL statements that cross the quarantine thresholds are terminated and quarantined. The quarantined SQL statements are not allowed to run again in a database.

This chapter contains the following topics:

161.1 DBMS_SQLQ Overview

The DBMS_SQLQ package provides an interface for configuring quarantine thresholds for SQL statements and their execution plans. SQL statements that cross the quarantine thresholds are terminated and quarantined. The quarantined SQL statements are not allowed to run again in a database.

You can use the DBMS_SQLQ package subprograms to:

  • create quarantine configurations for SQL statements and their execution plans, and specify quarantine thresholds for resource consumption

  • query quarantine configuration settings

  • delete quarantine configurations

  • transfer quarantine configurations from one database to another

161.2 Summary of DBMS_SQLQ Subprograms

This table lists the DBMS_SQLQ subprograms and briefly describes them.

Table 161-1 DBMS_SQLQ Package Subprograms

Procedure Description

ALTER_QUARANTINE Procedure

Configures quarantine settings for a SQL statement and its execution plans

CREATE_QUARANTINE_BY_SQL_ID Function

Creates a quarantine configuration for a SQL statement and its execution plans using SQL ID

CREATE_QUARANTINE_BY_SQL_TEXT Function

Creates a quarantine configuration for a SQL statement and its execution plans using SQL text

CREATE_STGTAB_QUARANTINE Procedure

Creates a staging table to store quarantine configurations

DROP_QUARANTINE Procedure

Deletes a quarantine configuration

GET_PARAM_VALUE_QUARANTINE Function

Returns the value for a quarantine configuration setting for a SQL statement and its execution plans

PACK_STGTAB_QUARANTINE Function

Adds one or more quarantine configurations into a staging table

UNPACK_STGTAB_QUARANTINE Function

Creates quarantine configurations in a database from a staging table

161.2.1 ALTER_QUARANTINE Procedure

This procedure configures quarantine settings for a SQL statement and its execution plans.

Syntax

DBMS_SQLQ.ALTER_QUARANTINE (
   quarantine_name   IN VARCHAR2,
   parameter_name    IN VARCHAR2,
   parameter_value   IN VARCHAR2);

Parameters

Table 161-2 ALTER_QUARANTINE Procedure Parameters

Parameter Description

quarantine_name

Name of the quarantine configuration.

parameter_name

Name of the quarantine configuration setting. You can specify any one of the following values:

  • CPU_TIME: CPU time

  • ELAPSED_TIME: Elapsed time

  • IO_MEGABYTES: I/O in megabytes

  • IO_REQUESTS: Number of physical I/O requests

  • IO_LOGICAL: Number of logical I/O requests

  • ENABLED: Flag to enable or disable the quarantine configuration setting. Specify YES to enable it and NO to disable it. The default value is YES.

  • AUTOPURGE: Flag to enable or disable automatic purging of the specified configuration setting. If it is set to YES, the configuration setting is automatically purged after 53 weeks, if not used. If it is set to NO, the configuration setting is never purged. The default value is YES.

parameter_value

Value of the quarantine configuration setting specified in parameter_name.

Examples

In the following example, quarantine settings are configured for a SQL statement having the quarantine configuration name of SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4, so that if the SQL statement consumes CPU time for 5 seconds or elapsed time for 10 seconds, then the SQL statement gets terminated and quarantined.

BEGIN

  DBMS_SQLQ.ALTER_QUARANTINE(
   QUARANTINE_NAME  =>  'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
   PARAMETER_NAME   =>  'CPU_TIME',
   PARAMETER_VALUE  =>  '5');

  DBMS_SQLQ.ALTER_QUARANTINE(
   QUARANTINE_NAME  =>  'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
   PARAMETER_NAME   =>  'ELAPSED_TIME',
   PARAMETER_VALUE  =>  '10');

  DBMS_SQLQ.ALTER_QUARANTINE(
   QUARANTINE_NAME  =>  'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
   PARAMETER_NAME   =>  'ENABLED',
   PARAMETER_VALUE  =>  'YES');

END;
/

Note that you must set PARAMETER_NAME to 'ENABLED' and PARAMETER_VALUE to 'YES' to enable the quarantine configuration.

161.2.2 CREATE_QUARANTINE_BY_SQL_ID Function

This function creates a quarantine configuration for a SQL statement and its execution plans based on SQL ID.

Syntax

DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID (
   sql_id           IN VARCHAR2,
   plan_hash_value  IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Table 161-3 CREATE_QUARANTINE_BY_SQL_ID Function Parameters

Parameter Description

sql_id

SQL ID of the SQL statement.

plan_hash_value

Execution plan hash value of the SQL statement. Default value is NULL.

When it is NULL, the quarantine configuration applies to all the execution plans of the SQL statement.

Return Value

Name of the quarantine configuration for a SQL statement and its execution plans.

Examples

The following example creates a quarantine configuration for the SQL statement having the SQL ID of 8vu7s907prbgr. The quarantine configuration applies to all the execution plans of the SQL statement.

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

The following example creates a quarantine configuration for the execution plan having the hash value of 3488063716 for the SQL statement having the SQL ID of 8vu7s907prbgr.

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

161.2.3 CREATE_QUARANTINE_BY_SQL_TEXT Function

This function creates a quarantine configuration for a SQL statement and its execution plans based on SQL text.

Syntax

DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT (
   sql_text         IN CLOB,
   plan_hash_value  IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Table 161-4 CREATE_QUARANTINE_BY_SQL_TEXT Function Parameters

Parameter Description

sql_text

SQL statement.

plan_hash_value

Execution plan hash value of the SQL statement. Default value is NULL.

When it is NULL, the quarantine configuration applies to all the execution plans of the SQL statement.

Return Value

Name of the quarantine configuration for a SQL statement and its execution plans.

Examples

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

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

The following example creates a quarantine configuration for the execution plan having the hash value of 3488063716 for the SQL statement having the SQL text of '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'), PLAN_HASH_VALUE => '3488063716');
END;
/

161.2.4 CREATE_STGTAB_QUARANTINE Procedure

This procedure creates a staging table to store the quarantine configurations, so that the staging table can be exported from the current database and imported into another database, thus enabling the quarantine configurations to be used in another database.

Syntax

DBMS_SQLQ.CREATE_STGTAB_QUARANTINE (
   staging_table_name   IN VARCHAR2,
   staging_table_owner  IN VARCHAR2 DEFAULT NULL,
   tablespace_name      IN VARCHAR2 DEFAULT NULL);

Parameters

Table 161-5 CREATE_STGTAB_QUARANTINE Procedure Parameters

Parameter Description

staging_table_name

Name of the staging table.

staging_table_owner

Name of the schema owner of the staging table. Default value is NULL, which means the database user executing this procedure is the staging table owner.

tablespace_name

Name of the tablespace in which the staging table needs to be created. Default value is NULL, which means the default tablespace of the database is the tablespace name for the staging table.

Examples

The following example creates the staging table TBL_STG_QUARANTINE in the default tablespace of the database and sets its table owner to the database user executing this procedure.

BEGIN
  DBMS_SQLQ.CREATE_STGTAB_QUARANTINE(STAGING_TABLE_NAME => 'TBL_STG_QUARANTINE');
END;
/

161.2.5 DROP_QUARANTINE Procedure

This procedure deletes a quarantine configuration.

Syntax

DBMS_SQLQ.DROP_QUARANTINE(quarantine_name IN VARCHAR2);

Parameters

Table 161-6 DROP_QUARANTINE Procedure Parameters

Parameter Description

quarantine_name

Name of the quarantine configuration to delete.

Examples

The following example deletes the quarantine configuration having the name SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4.

BEGIN
  DBMS_SQLQ.DROP_QUARANTINE('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4');
END;
/

161.2.6 GET_PARAM_VALUE_QUARANTINE Function

This function returns the value for a quarantine configuration setting for a SQL statement and its execution plans.

Syntax

DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE (
   quarantine_name   IN VARCHAR2,
   parameter_name    IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 161-7 GET_PARAM_VALUE_QUARANTINE Function Parameters

Parameter Description

quarantine_name

Name of the quarantine configuration.

parameter_name

Quarantine configuration setting for which the value needs to be retrieved.

Return Value

Returns the value for a quarantine configuration setting for a SQL statement.

Examples

The following example returns the value of the quarantine configuration setting CPU_TIME for the quarantine configuration having the name SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4.

DECLARE
  quarantine_config_setting_value VARCHAR2(30);
BEGIN
  quarantine_config_setting_value := DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE(
                                        QUARANTINE_NAME  =>  'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
                                        PARAMETER_NAME   =>  'CPU_TIME');
END;
/

161.2.7 PACK_STGTAB_QUARANTINE Function

This function adds one or more quarantine configurations into the staging table. The quarantine configurations can be then transferred to another database by exporting the staging table from the current database and importing it into another database.

Syntax

DBMS_SQLQ.PACK_STGTAB_QUARANTINE (
   staging_table_name    IN VARCHAR2,
   staging_table_owner   IN VARCHAR2 DEFAULT NULL,
   name                  IN VARCHAR2 DEFAULT '%',
   sql_text              IN VARCHAR2 DEFAULT '%',
   enabled               IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;

Parameters

Table 161-8 PACK_STGTAB_QUARANTINE Function Parameters

Parameter Description

staging_table_name

Name of the staging table in which the quarantine configurations need to be added.

staging_table_owner

Name of the schema owner of the staging table. Default value is NULL, which means the database user executing this procedure is the staging table owner.

name

Name of the quarantine configuration. Its value is case-sensitive and it accepts wildcard characters.

sql_text

SQL statement text. Its value is case-sensitive and it accepts wildcard characters.

enabled

Flag indicating whether the quarantine configuration should be enabled or disabled. If it is set to YES, then the quarantine configuration is enabled, else it is disabled. Default value is NULL, which means the quarantine configuration is disabled by default.

Return Value

Number of quarantine configurations added to the staging table.

Examples

The following example adds all the quarantine configurations having the names starting with SQL_QUARANTINE_ 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 => 'SQL_QUARANTINE_%');
END;
/

161.2.8 UNPACK_STGTAB_QUARANTINE Function

This function creates quarantine configurations in a database from the staging table containing the quarantined configurations exported from another database.

Syntax

DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE (
   staging_table_name    IN VARCHAR2,
   staging_table_owner   IN VARCHAR2 DEFAULT NULL,
   name                  IN VARCHAR2 DEFAULT '%',
   sql_text              IN VARCHAR2 DEFAULT '%',
   enabled               IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;

Parameters

Table 161-9 UNPACK_STGTAB_QUARANTINE Function Parameters

Parameter Description

staging_table_name

Name of the staging table from which the quarantine configurations need to be created in the database.

staging_table_owner

Name of the schema owner of the staging table. Default value is NULL, which means the database user executing this procedure is the staging table owner.

name

Name of the quarantine configuration. Its value is case-sensitive and it accepts wildcard characters.

sql_text

SQL statement text. Its value is case-sensitive and it accepts wildcard characters.

enabled

Flag indicating whether the quarantine configuration should be enabled or disabled. If its value is YES, then the quarantine configuration is enabled, else it is disabled. Default value is NULL, which means the quarantine configuration is disabled by default.

Return Value

Number of quarantine configurations created in the database from the staging table.

Examples

The following example creates the quarantine configurations in the database from all the quarantine configurations stored in the staging table TBL_STG_QUARANTINE.

DECLARE
  quarantine_configs NUMBER;
BEGIN
  quarantine_configs := DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE(
                                      STAGING_TABLE_NAME => 'TBL_STG_QUARANTINE');
END;
/