DBA Hub

📋Steps in this guide1/5

SQL Quarantine in Oracle Database 19c

Oracle Database 19c introduced an extension of Runaway Query Management called SQL Quarantine.

oracle 19cconfigurationintermediate
by OracleDba
21 views
1

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 " " initialisation parameter. Don't use this on a real instance or you will be breaking your license agreement. Remember to reset this parameter once you have finished testing SQL Quarantine.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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

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
2

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. In the following example we have a resource plan with a consumer group called for normal sessions. Any sessions with the consumer group that have a single call using CPU for more than 60 seconds will have that SQL cancelled. We allow the user to switch to the NORMAL_CG consumer group, then we set the initial consumer group for the user to . Finally we activate the plan. We can monitor the current consumer group assignment with the following query. 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. After approximately 60 seconds the query calling the function has been cancelled. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
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;

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;
/

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;
/

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'long_running_query_plan';

COLUMN username FORMAT A30
COLUMN resource_consumer_group FORMAT A30

SELECT username, resource_consumer_group
FROM   v$session
WHERE  username = 'TESTUSER1';

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;

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>

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>
3

Views

We can see the result of the quarantine action in the and views. The view includes the and columns. We see two entries for the , one showing the number of as 0, and one showing it as 1. The view shows us details of the SQL quarantine definition. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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>

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>
4

DBMS_SQLQ : SQL Quarantine Management

The package provides and API to manage SQL Quarantine. We can manually quarantine a statement based on or . Both methods accept a 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. Manually created quarantines have all thresholds set to . The 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 threshold for the manually created quarantines. 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 procedure.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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;
/

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>

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>

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;
/
5

Transfer Quarantine Definitions

The package allows us to transfer SQL quarantine definitions between databases. We create a staging table using the procedure. The and parameters default to NULL, which means the table will be created in the current schema, and the default tablespace for that schema. We transfer the SQL quarantine definitions to the staging table using the function. By default it captures all the definitions. We could limit this using the , and parameters. In the example below we use the default values. We could just omit these three parameters. 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 procedure. The parameters and their actions are similar to those of the function. 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
BEGIN
  sys.DBMS_SQLQ.create_stgtab_quarantine(staging_table_name  => 'my_quarantine_defs',
                                         staging_table_owner => 'testuser1',
                                         tablespace_name     => NULL);
END;
/

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!