8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Prerequisites
- Automatic SQL Quarantine
- Views
- DBMS_SQLQ : SQL Quarantine Management
- Transfer Quarantine Definitions
Related articles.
- Resource Manager : SQL Quarantine
- Runaway Query Management - Automatic Consumer Group Switching
- Resource Manager : All 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.
- CPU_TIME
- ELAPSED_TIME
- IO_MEGABYTES
- IO_REQUESTS
- IO_LOGICAL
- ENABLED
- AUTOPURGE
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:
- Quarantine for Execution Plans for SQL Statements Consuming Excessive System Resources
- DBA_SQL_QUARANTINE
- DBMS_SQLQ
- Resource Manager : SQL Quarantine
- Runaway Query Management - Automatic Consumer Group Switching
- Resource Manager : All Articles
Hope this helps. Regards Tim...