Oracle DBMS_SQLQ
Version 19.2.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose This package provides the API 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.
AUTHID CURRENT_USER
Constants
Name Data Type Value
General
ALWAYS_QUARANTINE VARCHAR2(30) '4294967295'
DROP_THRESHOLD VARCHAR2(30) '0'
Dependencies
dbms_sqlcontrol_lib    
Documented No
Exceptions
Error Code Reason
ORA-06564 Object does not exist
ORA-13825 missing SQL statement text
ORA-31684 Object type SQL Quarantine already exists
ORA-38133 Invalid parameter value specified
ORA-38134 Invalid parameter value specified
First Available 19c
Security Model Owned by SYS with EXECUTE granted to ???
Source {ORACLE_HOME}/rdbms/admin/???

Administer SQL Management Object" privilege
Subprograms
 
ALTER_QUARANTINE (new 19c)
Alters the quarantine configuration dbms_sqlq.alter_quarantine(
quarantine_name IN VARCHAR2,
parameter_name  IN VARCHAR2,
parameter_value IN VARCHAR2);
Parameter Names
'AUTOPURGE'
'CPU_TIME'
'ELAPSED_TIME'
'ENABLED'
'IO_LOGICAL'
'IO_REQUESTS'
'IO_MEGABYTES'
exec dbms_sqlq.alter_quarantine('TOO_MUCH_DATA', 'IO_MEGABYTES', '1000');
 
CREATE_QUARANTINE_BY_SQL_ID (new 19c)
Creates a SQL quarantine configuration for storing quarantine information using SQL id dbms_sqlq.create_quarantine_by_sql_id(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;
exec dbms_sqlq.create_quarantine_by_sql_id('94qn6y14kw01g', NULL);

exec dbms_sqlq.create_quarantine_by_sql_id('d1071nwwx40fg', 970064335)
 
CREATE_QUARANTINE_BY_SQL_TEXT (new 19c)
Creates a SQL quarantine configuration for storing quarantine information using SQL text dbms_sqlq.create_quarantine_by_sql_text(
sql_text        IN CLOB,
plan_hash_value IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;
DECLARE
 sqltxt CLOB := 'TRUNCATE TABLE servers';
BEGIN
  retVal := dbms_sqlq.create_quarantine_by_sql_text(sqltxt);
  dbms_output.put_line(retVal);
END;
/
 
CREATE_STGTAB_QUARANTINE (new 19c)
Creates a staging table to pack SQL quarantine configurations dbms_sqlq.create_stgtab_quarantine(
staging_table_name  IN VARCHAR2,
staging_table_owner IN VARCHAR2 DEFAULT NULL,
tablespace_name     IN VARCHAR2 DEFAULT NULL);
exec dbms_sqlq.create_stgtab_quarantine('UWQUARANTINE', 'UWCLASS', 'UWDATA');
 
DROP_QUARANTINE (new 19c)
Drops a quarantine configuration dbms_sqlq.drop_quarantine(quarantine_name IN VARCHAR2);
exec dbms_sqlq.drop_quarantine('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4');
 
GET_PARAM_VALUE_QUARANTINE (new 19c)
Returns the value of the quarantine parameter for the given SQL quarantine configuration dbms_sqlq.get_param_value_quarantine(
quarantine_name IN VARCHAR2,
parameter_name  IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_sqlq.get_param_value_quarantine('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4', 'IO_MEGABYTES')
FROM dual;
 
PACK_STGTAB_QUARANTINE (new 19c)
Packs SQL quarantine configurations into a staging table 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;
SELECT dbms_sqlq.pack_stgtab_quarantine('UW_QSTAGING', 'UWCLASS')
FROM dual;
 
UNPACK_STGTAB_QUARANTINE (new 19c)
Unpacks SQL quarantine configurations from a staging table 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;
SELECT dbms_sqlq.unpack_stgtab_quarantine('UW_QSTAGING', 'UWCLASS')
FROM dual;

Related Topics
Built-in Functions
Built-in Packages
What's New In 18cR3
What's New In 19cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved