There is a use case for this question. In my sample resource manager plan for PeopleSoft, there are consumer groups with different maximum CPU time limits. 4 hours for scheduled queries, and 2 hours for SQL*Plus/SQL Developer sessions.
The tests in this article are based on the examples in Tim Hall's article about SQL Quarantine in 19c.
N.B. SQL Quarantine is an Exadata-only feature.
I have created a simple plan with two consumer groups that have CPU time-outs of 30 and 60 seconds. There are no group mappings; I will switch groups manually.
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.CREATE_PLAN('GFC_TEST_PLAN');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('LOW_LIMITED30_GROUP');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('LOW_LIMITED60_GROUP');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
'GFC_TEST_PLAN', 'LOW_LIMITED30_GROUP',
mgmt_p8 => 1,
switch_group => 'CANCEL_SQL',
switch_time => 30,
switch_for_call => TRUE);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
'GFC_TEST_PLAN', 'LOW_LIMITED60_GROUP',
mgmt_p8 => 1,
switch_group => 'CANCEL_SQL',
switch_time => 60,
switch_for_call => TRUE);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
'GFC_TEST_PLAN', 'OTHER_GROUPS');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => user,
consumer_group => 'LOW_LIMITED30_GROUP',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => user,
consumer_group => 'LOW_LIMITED60_GROUP',
grant_option => FALSE);
END;
/
ALTER SYSTEM SET resource_manager_plan = 'GFC_TEST_PLAN';set serveroutput on timi on
CREATE OR REPLACE FUNCTION burn_cpu (p_secs 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_secs/86400);
l_number := l_number + 1;
END LOOP;
RETURN l_number;
END;
/DECLARE
l_session v$session%ROWTYPE;
l_consumer_group VARCHAR2(30):= 'LOW_LIMITED30_GROUP';
--l_consumer_group VARCHAR2(30):= 'LOW_LIMITED60_GROUP';
BEGIN
SELECT * INTO l_session FROM v$session WHERE sid = Sys_Context('USERENV', 'SID');
dbms_output.put_line(l_session.sid||'.'||l_session.serial#||':'||l_session.module||':'||l_session.action||':'||l_session.resource_Consumer_Group);
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(l_session.sid,l_session.serial#,consumer_group=>l_consumer_group);
SELECT * INTO l_session FROM v$session WHERE sid = Sys_Context('USERENV', 'SID');
dbms_output.put_line(l_session.sid||'.'||l_session.serial#||':'||l_session.module||':'||l_session.action||':'||l_session.resource_Consumer_Group);
END;
/ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_QUARANTINE = TRUE;
ALTER SESSION SET OPTIMIZER_USE_SQL_QUARANTINE = TRUE;SELECT burn_cpu (45) FROM dual
Error report -
ORA-12777: A non-continuable error encountered. Check the error stack for additional information [40], [pfrruncae_check_async_events], [], [].
ORA-00040: active time limit exceeded - call aborted
Elapsed: 00:00:33.574
SELECT burn_cpu (75) FROM dual
Error report -
ORA-12777: A non-continuable error encountered. Check the error stack for additional information [40], [pfrruncae_check_async_events], [], [].
ORA-00040: active time limit exceeded - call aborted
Elapsed: 00:00:34.023select signature, name, sql_text, plan_hash_value, cpu_time, origin from dba_sql_quarantine where sql_text like '%burn_cpu%' SIGNATURE NAME SQL_TEXT PLAN_HASH_VALUE CPU_TIME ORIGIN ---------------------- ---------------------------------------- ------------------------------ --------------- ---------- ---------------- 12281544607895693562 SQL_QUARANTINE_anw6gs0gbta7u125daea2 SELECT burn_cpu (75) FROM dual 308129442 30 RESOURCE-MANAGER 3516266497211383477 SQL_QUARANTINE_31m29rz6y8gpp125daea2 SELECT burn_cpu (45) FROM dual 308129442 30 RESOURCE-MANAGER
SELECT burn_cpu (45) FROM dual
Error report -
ORA-56955: quarantined plan used
Elapsed: 00:00:00.414
SELECT burn_cpu (75) FROM dual
Error report -
ORA-56955: quarantined plan used
Elapsed: 00:00:00.416
Next, I will switch the consumer group to LOW_LIMITED60_GROUP. The CPU limit is now 60 seconds. Now, neither command returns ORA-56955.
The 45-second process runs to a successful conclusion.
SELECT burn_cpu (45) FROM dual;
BURN_CPU(45)
------------
63264226
Elapsed: 00:00:45.846
The 75-second process runs for 65 seconds and is then terminated with ORA-00040 because it has reached the time limit, but not because the execution plan was quarantined.
SELECT burn_cpu (75) FROM dual
Error report -
ORA-12777: A non-continuable error encountered. Check the error stack for additional information [40], [pfrruncae_check_async_events], [], [].
ORA-00040: active time limit exceeded - call aborted
Elapsed: 00:01:05.952
A quarantine directive with a lower CPU_TIME limit than that of the current consumer group is not applied because the statement may run to completion in a time between the lower limit and the higher limit. Oracle allows the query to execute; it will be aborted when it reaches the higher CPU time.
After a while, as the documentation indicates, quarantine capture will update the CPU time limit on the existing quarantine definition limit to the higher limit in the current consumer group.
The query in our example runs for 20 minutes only once, and then never again—unless the resource limit increases or the plan changes. If the limit is increased to 25 minutes, then the Resource Manager permits the statement to run again with the quarantined plan. If the statement runs for 23 minutes, which is below the new threshold, then the Resource Manager removes the plan from quarantine. If the statement runs for 26 minutes, which is above the new threshold, the plan remains in quarantine unless the limit is increased.
- See SQL Tuning Guide, Query Concepts, 4.7 About Quarantined SQL Plans
However, for the statement that runs successfully (for 45 seconds) in the consumer group with the higher limit (60 seconds), I have not seen the database remove the quarantine directive, even if I remove LOW_LIMITED30_GROUP consumer group from all resource plans and drop it from the database.
SIGNATURE NAME SQL_TEXT PLAN_HASH_VALUE CPU_TIME ORIGIN
------------------- ---------------------------------------- ------------------------------ --------------- ---------- ----------------
12281544607895693562 SQL_QUARANTINE_anw6gs0gbta7u125daea2 SELECT burn_cpu (75) FROM dual 308129442 60 RESOURCE-MANAGER
3516266497211383477 SQL_QUARANTINE_31m29rz6y8gpp125daea2 SELECT burn_cpu (45) FROM dual 308129442 30 RESOURCE-MANAGER
The next time an attempt is made to execute the 75-second statement in either consumer group, it is quarantined as before.
SELECT burn_cpu (75) FROM dual
Error report -
ORA-56955: quarantined plan usedConclusion
- SQL Quarantines are not tied to any particular resource plan or consumer group.
- If the current consumer group doesn't have any timeout set, then any matching SQL quarantine directive is not applied
- If a statement has a quarantine directive with a higher CPU time limit than the current consumer group, then it is applied immediately.
- If a statement has a quarantine directive with a lower CPU time limit than the current consumer group, then it will be allowed to execute. If the runtime then exceeds the consumer group CPU limit, then it will be timed out with ORA-00040. The CPU time limit on the quarantine directive will be increased to the limit in the current consumer group. In future, the statement will immediately be prevented from executing with ORA-56955 in both consumer groups.
Therefore, it is safe to allow different consumer groups in which the same long-running SQL statements may be executed to have different timeouts. The lower quarantine timeout will not apply to executions in consumer groups with higher timeouts. The SQL quarantine directives will evolve to have higher timeouts as required.
@go-faster.co.uk on BlueSky
Posts
