187 DBMS_SQLQ
The DBMS_SQLQ
package provides the interface for configuring quarantine thresholds for execution plans of SQL statements. If any of the Resource Manager thresholds is equal to or less than the 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.
This chapter contains the following topics:
187.1 DBMS_SQLQ Overview
The DBMS_SQLQ
package provides the interface for configuring quarantine thresholds for execution plans of SQL statements. If any of the Resource Manager thresholds is equal to or less than the 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.
You can use the DBMS_SQLQ
package subprograms to:
-
create quarantine configurations for execution plans of SQL statements and specify quarantine thresholds for their resource consumption
-
query quarantine thresholds specified in quarantine configurations
-
delete quarantine configurations
-
transfer quarantine configurations from one database to another
187.2 Summary of DBMS_SQLQ Subprograms
This table lists the DBMS_SQLQ
subprograms and briefly describes them.
Table 187-1 DBMS_SQLQ Package Subprograms
Procedure | Description |
---|---|
Specifies a quarantine threshold in a quarantine configuration for execution plans of a SQL statement |
|
Creates a quarantine configuration for execution plans of a SQL statement using SQL ID |
|
Creates a quarantine configuration for execution plans of a SQL statement using SQL text |
|
Creates a staging table to store quarantine configurations |
|
Deletes a quarantine configuration |
|
Returns the value for a quarantine threshold specified in a quarantine configuration |
|
Adds one or more quarantine configurations to a staging table |
|
Creates quarantine configurations in a database from a staging table |
187.2.1 ALTER_QUARANTINE Procedure
This procedure specifies a quarantine threshold for a resource in a quarantine configuration for execution plans of a SQL statement.
Syntax
DBMS_SQLQ.ALTER_QUARANTINE (
quarantine_name IN VARCHAR2,
parameter_name IN VARCHAR2,
parameter_value IN VARCHAR2);
Parameters
Table 187-2 ALTER_QUARANTINE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the quarantine configuration. |
|
Name of the resource for which quarantine threshold needs to be specified. You can specify any one of the following values:
|
|
Quarantine threshold for the resource specified in |
Examples
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_3z0mwuq3aqsm8cfe7a0e4
.
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');
END;
/
When the SQL statement is executed using the execution plan specified in the quarantine configuration, and 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.
187.2.2 CREATE_QUARANTINE_BY_SQL_ID Function
This function creates a quarantine configuration for execution plans of a SQL statement 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 187-3 CREATE_QUARANTINE_BY_SQL_ID Function Parameters
Parameter | Description |
---|---|
|
SQL ID of the SQL statement. |
|
Hash value of the execution plan of the SQL statement. Default value is When it is |
Return Value
Name of the quarantine configuration.
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; /
187.2.3 CREATE_QUARANTINE_BY_SQL_TEXT Function
This function creates a quarantine configuration for execution plans of a SQL statement 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 187-4 CREATE_QUARANTINE_BY_SQL_TEXT Function Parameters
Parameter | Description |
---|---|
|
SQL statement. |
|
Hash value of the execution plan of the SQL statement. Default value is When it is |
Return Value
Name of the quarantine configuration.
Examples
The following example creates a quarantine configuration that applies to all the execution plans of the 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; /
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; /
187.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 across databases.
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 187-5 CREATE_STGTAB_QUARANTINE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the staging table. |
|
Name of the schema owner of the staging table. Default value is |
|
Name of the tablespace in which the staging table needs to be created. Default value is |
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; /
187.2.5 DROP_QUARANTINE Procedure
This procedure deletes a quarantine configuration.
Syntax
DBMS_SQLQ.DROP_QUARANTINE(quarantine_name IN VARCHAR2);
Parameters
Table 187-6 DROP_QUARANTINE Procedure Parameters
Parameter | Description |
---|---|
|
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; /
187.2.6 GET_PARAM_VALUE_QUARANTINE Function
This function returns the quarantine threshold for a resource specified in a quarantine configuration.
Syntax
DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE (
quarantine_name IN VARCHAR2,
parameter_name IN VARCHAR2)
RETURN VARCHAR2;
Parameters
Table 187-7 GET_PARAM_VALUE_QUARANTINE Function Parameters
Parameter | Description |
---|---|
|
Name of the quarantine configuration. |
|
Resource for which the quarantine threshold needs to be retrieved. |
Return Value
Returns the quarantine threshold for a resource specified in a quarantine configuration.
Examples
The following example returns the quarantine threshold for CPU time specified in 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; /
187.2.7 PACK_STGTAB_QUARANTINE Function
This function adds one or more quarantine configurations into a staging table.
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 187-8 PACK_STGTAB_QUARANTINE Function Parameters
Parameter | Description |
---|---|
|
Name of the staging table in which the quarantine configurations need to be added. |
|
Name of the schema owner of the staging table. Default value is |
|
Name of the quarantine configuration. Its value is case-sensitive and it accepts wildcard characters. |
|
SQL statement text. Its value is case-sensitive and it accepts wildcard characters. |
|
Flag indicating whether the quarantine configuration should be enabled or disabled. If it is set to |
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; /
187.2.8 UNPACK_STGTAB_QUARANTINE Function
This function creates quarantine configurations in a database from a staging table.
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 187-9 UNPACK_STGTAB_QUARANTINE Function Parameters
Parameter | Description |
---|---|
|
Name of the staging table from which the quarantine configurations need to be created in the database. |
|
Name of the schema owner of the staging table. Default value is |
|
Name of the quarantine configuration. Its value is case-sensitive and it accepts wildcard characters. |
|
SQL statement text. Its value is case-sensitive and it accepts wildcard characters. |
|
Flag indicating whether the quarantine configuration should be enabled or disabled. If its value is |
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; /