Thanks for the quick and helpful response.
But i have a follow up question:
You referred to a 'non-interruptible phase' of the database.
As i searched for examples of such phase, i came across these examples: 'rolling back a transaction or being blocked by a network operation.'
Can only the database be in an non-interruptible phase or queries as well and if queries can be 'non-interruptible' what kind of queries would that be? Write / delete operations?
I don't quite get this, and as far as I have checked in my application, we send a explicit cancel request which seems to not be evaluated.

Thanks again for the quick response.
Can you provide further readings / links on this kind of topic?
I would like to be able to better understand such handling of/by the process.

Thanks in advance!

Hi : another possibility to consider could be to use Resource Manager : as https://blog.pythian.com/oracle-limiting-query-runtime-without-killing-the-session/ says, you can limit the query runtime via RM, and (in some cases) you can switch on-the-fly the consumer group for a given session using the DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP routine (see the item "5) Switching consumer groups for running sessions in-flight" in the Support note 'Database Resource Manager samples (Doc ID 106948.1)' ...
Joining the two points, maybe you cold interrput the long running query switching its sessions´s consumer group, instead of pressing CTRL+C or using the interrupt/cancel resources in your environment/tool/programming language : I´m unsure if this will work in your scenary, but try and see...

Regards,

Chiappa

So, I have a variation on this problem. I have an Open API, and we find that users sometimes submit queries and just go away. Obviously this causes great issues if these are truly bad queries. I love the plan explanation above, but I am thinking this will cancel a query that might be a legitimate long-running query. So, my variation to this is, can we only cancel the query if it has not started to send any records back to the user?

The problem here is that if a user is trying to get a lot of data, a long-running query is OK, it's just going through a cursor and taking a long time to spool a ton of data back to the end-user. But, if the query never returned any data back to the user for say 5 minutes, the user has probably given up.

Hi Tom

That was a useful answer, but as a non technical user i was curious about a process (or two) that i occasionally run. After i submit the query the only way out of the screen is to click on cancel. Now I don't want to cancel so i open a new tab and monitor the process and once the status hits "succeeded" i go back to the first tab and cancel to exit and then run a new process, etc.

I know it works but wondered why does oracle not give you an exit button so that the process can just run.

Hope this isn't too confusing or too many run on questions.

Thanks

Mike

Hello Connor/Chris,

Just wondering whether there is any reason why dbms_resource_manager.switch_consumer_group_for_sess works for CANCEL_SQL but not for KILL_SESSION.
When I pass CANCEL_SQL as value to CONSUMER_GROUP parameter, the running SQL is successfully cancelled but when I pass KILL_SESSION, the call to dbms_resource_manager.switch_consumer_group_for_sess fails with below error

begin
 dbms_resource_manager.switch_consumer_group_for_sess(
   session_id => 1650,
   session_serial => 61426,
   consumer_group => 'KILL_SESSION');
end;
Error report -
ORA-29366: invalid CONSUMER_GROUP argument specified
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4524
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4547
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 763
ORA-06512: at line 2
29366. 00000 -  "invalid CONSUMER_GROUP argument specified"
*Cause:    An invalid consumer group name was specified.
*Action:   Specify a non-NULL, valid consumer group name.

Hello Connor/Chris,

So some additional interesting observation about CANCEL_SQL and KILL_SESSION.
In 19c, CANCEL_SQL works but KILL_SESSION fails and in 11.2.0.4, KILL_SESSION fails with same error (as 19c), the CANCEL_SQL does not make any difference. The call to dbms_resource_manager.switch_consumer_group_for_sess just completes successfully with CANCEL_SQL but the sql does not get cancelled.

Hello Connor,

Thank you for your help with this.
While I understand the theory behind your below comment
A kill session is something I don't a session could do to itself, it needs an external party to initiate that.

I am wondering how the database is able to achieve it when configured like mentioned in MOS note Managing and Monitoring Runaway Query Using Resource Manager (Doc ID 1600965.1)
Should the above theory not apply to any invocation of switching to KILL_SESSION?

Example 3

The following PL/SQL block creates a resource plan directive for the OLTP group that kills (terminates) any session that exceeds 60 seconds of CPU time. This example prevents runaway queries from consuming too many resources.

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

PLAN             => 'DAYTIME',

GROUP_OR_SUBPLAN => 'OLTP',

COMMENT          => 'OLTP group',

MGMT_P1          => 75,

SWITCH_GROUP     => 'KILL_SESSION',

SWITCH_TIME      => 60);

END;

/

 

In this example, the reserved consumer group name KILL_SESSION is specified for SWITCH_GROUP. Therefore, the session is terminated when the switch criteria is met. Other reserved consumer group names are CANCEL_SQL and LOG_ONLY. When CANCEL_SQL is specified, the current call is canceled when switch criteria are met, but the session is not terminated. When LOG_ONLY is specified, information about the session is recorded in real-time SQL monitoring, but no specific action is taken for the session.

Hello Connor/Chris,

I am witnessing an interesting (at least to me) observation while using DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS with CANCEL_SQL, in 2 environments with same oracle database version but different setup.
In my sandbox setup with oracle 19.15, when I run something like below

exec DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(session_id => 24, session_serial => 38715, consumer_group => 'CANCEL_SQL') ;

Nothing happens...neither error nor the current sql in the session is cancelled.
However, at work (an Oracle Cloud @ Customer setup), the same code above works and cancels the current sql execution.

Can you please help me to figure out how to explain this different behaviour?

Thanks in advance

SQL> show parameter resource_manager_plan 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
resource_manager_plan                string      DEFAULT_PLAN

SQL> select group_or_subplan from DBA_RSRC_PLAN_DIRECTIVES
  2   where plan = 'DEFAULT_PLAN'
  3  /

GROUP_OR_SUBPLAN
----------------------------------------------------------
OTHER_GROUPS
SYS_GROUP
ORA$AUTOTASK

SQL> select RESOURCE_CONSUMER_GROUP
  2  from v$session
  3  where sid = 1102;

RESOURCE_CONSUMER_GROUP
--------------------------------
OTHER_GROUPS

SQL> exec DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(session_id => 1102, session_serial => 39069, consumer_group => 'BLAH') ;
BEGIN DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(session_id => 1102, session_serial => 39069, consumer_group => 'BLAH') ; END;

*
ERROR at line 1:
ORA-29368: consumer group BLAH does not exist
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4529
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4552
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 763
ORA-06512: at line 1

SQL> select CONSUMER_GROUP from
  2  DBA_RSRC_CONSUMER_GROUPS;

CONSUMER_GROUP
---------------------------------
BATCH_GROUP
ORA$AUTOTASK
INTERACTIVE_GROUP
OTHER_GROUPS
DEFAULT_CONSUMER_GROUP
SYS_GROUP
LOW_GROUP
ETL_GROUP
DSS_GROUP
DSS_CRITICAL_GROUP
ORA$APPQOS_0
ORA$APPQOS_1
ORA$APPQOS_2
ORA$APPQOS_3
ORA$APPQOS_4
ORA$APPQOS_5
ORA$APPQOS_6
ORA$APPQOS_7

SQL> exec DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(session_id => 1102, session_serial => 39069, consumer_group => 'LOW_GROUP') ;

PL/SQL procedure successfully completed.

SQL> select RESOURCE_CONSUMER_GROUP
  2  from v$session
  3  where sid = 1102;

RESOURCE_CONSUMER_GROUP
--------------------------------
OTHER_GROUPS

SQL> exec DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(session_id => 1102, session_serial => 39069, consumer_group => 'SYS_GROUP') ;

PL/SQL procedure successfully completed.

SQL> select RESOURCE_CONSUMER_GROUP
  2  from v$session
  3  where sid = 1102;

RESOURCE_CONSUMER_GROUP
--------------------------------
SYS_GROUP

Hello Connor,

Thank you for your response.
Reading your response made me realise I need to improve on ability to ask question properly... :)
I guess I missed out on one of the most important information to share in my question. So apologies for that.
I am not using any resource manager plan in either of the environments.
The DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS API call with CANCEL_SQL consumer group, appears to work at work (in Oracle Cloud At Customer setup) but not on my sandbox, despite having same database patch (19.15).
So in Oracle Cloud At Customer, I get below (with DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS API call carried out from second session):

10-OCT-2022 08:16:31 APPDBA@PVT_CLOUD > show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TRUE
resource_manage_goldengate           boolean     FALSE
resource_manager_cpu_allocation      integer     0
resource_manager_plan                string
10-OCT-2022 08:16:38 APPDBA@PVT_CLOUD > begin loop null; end loop; end;
  2  /
begin loop null; end loop; end;
*
ERROR at line 1:
ORA-12777: A non-continuable error encountered.  Check the error stack for
additional information [], [], [], [].
ORA-00040: active time limit exceeded - call aborted


10-OCT-2022 08:16:59 APPDBA@PVT_CLOUD >

But same scenario does nothing on my sandbox, even though the DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS API call reports success.

Hello Connor,

Thank you for your response.
I can confirm that I am not using any manual SQL quarantine and the auto sql quarantine comes into picture only while using resource manager.
The above observation is inside a PDB. There is no resource manager plan defined at CDB level, at least on my sandbox VMs.

I am hoping that the ability to cancel running sql using DBMS_RESOURCE_MANAGER.SWITH_CONSUMER_GROUP_FOR_SESS API with CANCEL_SQL consumer group, without any explicit resource manager plan in place, should work in all environments.

I am just struggling to understand why it always seems to work with Oracle Cloud @ Customer setup but not in my sandbox VMs.

p.s. On a related note, on one of my sandbox VMs, the DBMS_RESOURCE_MANAGER.SWITH_CONSUMER_GROUP_FOR_SESS API with CANCEL_SQL consumer group started cancelling active sql yesterday....I am sure it was not doing that before and I have not made any changes to the VM so bit baffled as to what is going on.

Is there a way to trace the DBMS_RESOURCE_MANAGER APIs to find out what works and what does not, under the covers?

Thanks in advance

Hello Connor,

Apologies for late response.
Your response to my question on 10th October was spot on....as always...and I was too blinded to understand it properly.
The CANCEL_SQL with SWITCH_CONSUMER_GROUP_FOR_SESS API works (inside PDB) as long as there is a resource manager plan in place, either at CDB or PDB level.
The reason for the behaviour that I observed before and believed that it was inconsistent was because behind the scenes my sandbox 19c database had an active resource manager plan, either at CDB level or at PDB level. I have different scheduler timezone settings for CDB and PDB, which meant the daily maintenance windows got activated at different times in CDB and in PDB.
When I thought that CANCEL_SQL was not working, neither CDB nor PDB had any active resource manager plan but there were also times when PDB did not have any active resource manager plan and yet CANCEL_SQL was working, which mislead me.
Thankfully, common sense prevailed, and I realised that an active resource manager plan is needed for CANCEL_SQL to work when used in SWITCH_CONSUMER_GROUP_FOR_SESS API.

Once again, thank you for your help and sorry about me getting it wrong.

Hello Connor/Chris,

Hope you are doing well.
I am reading 2 MOS notes and trying to figure out which one is correct (or if my understanding of those notes is flawed).
Database Resource Manager samples (Doc ID 106948.1), last updated in Feb, 2022, has a section called "Switching consumer groups for running sessions in-flight", which appears to suggest that when DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS is invoked then v$session reflects the new consumer group against the session immediately.
However, dbms_resource_manager.switch_consumer_group_for_sess Does Not Change Resource Consumer Group (Doc ID 808387.1), last updated in March, 2019, appears to suggest that when DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS is invoked then v$session does not reflect the new consumer group against the session straight away and only reflects the change AFTER the affected session carries out any activity.
My testing on 19.15 matches the observation in Doc ID 808387.1 but not the one from Doc ID 106948.1. What am I missing?
I am trying to use SWITCH_CONSUMER_GROUP_FOR_SESS for a use case whereby a session in OTHER_GROUPS (with no directive) locks a table and remains idle/inactive. From another session, I am changing its consumer group to another one (with plan directives to end session). However, neither the idle/inactive session reflects the consumer group change upon execution of SWITCH_CONSUMER_GROUP_FOR_SESS nor the corresponding locks are released. Only after, I attempt to run some sql from the affected session then the consumer group change gets reflected.
Is this expected behaviour of Resource Manager or is there a way to force the change of consumer group for an idle/inactive session that is not originally part of a plan directive involving MAX_IDLE_TIME?
Below is the setup that I am using

Resource Manager Plan Setup

begin
  dbms_resource_manager.clear_pending_area ;
  dbms_resource_manager.create_pending_area();
  dbms_resource_manager.create_plan(/*full sig:*/plan=>'END_SESS_PLAN'/*varchar2*/,comment=>'Plan to end session'/*varchar2*/);
  dbms_resource_manager.create_consumer_group(/*full sig:*/consumer_group=>'END_SESS_CG'/*varchar2*/,comment=>'Consumer Group that will end the session'/*varchar2*/);
  dbms_resource_manager.create_plan_directive(/*full sig:*/plan=>'END_SESS_PLAN'/*varchar2*/,group_or_subplan=>'END_SESS_CG'/*varchar2*/,comment=>'Directive to end session'/*varchar2*/,switch_group=>'KILL_SESSION'/*varchar2*/,switch_elapsed_time => 1, undo_pool=>1/*number*/,max_idle_time=>1/*number*/,max_idle_blocker_time=>1/*number*/,switch_for_call=>TRUE/*pl/sql boolean*/,session_pga_limit=>0.1/*number*/);
  dbms_resource_manager.create_plan_directive(/*full sig:*/plan=>'END_SESS_PLAN'/*varchar2*/,group_or_subplan=>'OTHER_GROUPS'/*varchar2*/,comment=>'Directive for Others'/*varchar2*/);
  dbms_resource_manager.validate_pending_area();
  dbms_resource_manager.submit_pending_area();
end;
/

alter system set resource_manager_plan = END_SESS_PLAN ;

Session 1:

SQL> select sid, serial#, status, last_call_et, resource_consumer_group from v$session where sid = sys_context('USERENV','SID') ;

       SID    SERIAL# STATUS   LAST_CALL_ET RESOURCE_CONSUMER_GROUP
---------- ---------- -------- ------------ --------------------------------
 20 45957 ACTIVE    0 OTHER_GROUPS

Elapsed: 00:00:00.00
SQL> lock table t in exclusive mode ;

Table(s) Locked.

Elapsed: 00:00:00.01

Session 2:

exec dbms_resource_manager.switch_consumer_group_for_sess(session_id=> 20/*number*/,session_serial=> 45957/*number*/, consumer_group => 'END_SESS_CG');

If I use ALTER SYSTEM DISCONNECT SESSION in place of SWITCH_CONSUMER_GROUP_FOR_SESS on the idle/inactive session then the session goes away immediately and releases any locks immediately

Thanks in advance

Hello Connor,

Thank you for taking time to respond to my question.
Apologies as I should have mentioned this in my original post but I am looking for a way to achieve "on-demand-kill-session" functionality using resource manager and that has lead to my question above.
I guess I did managed to get the "on-demand-kill-session" working for all non-idle sessions using fairly straightforward resource manager plan setup. However, I got stuck with implementing it for idle sessions that might have locked resources.
I get the idea to set the initial consumer group with MAX_IDLE_BLOCKER_TIME directive but I would have to set that to fairly high value for application sessions, given the nature of our application. The ideal option that I was looking for would work like below
1. As you suggested, define consumer group for all application sessions with a long-enough MAX_IDLE_BLOCKER_TIME directive, say 1 hour, in order not to "break" any of the existing application journey. The MAX_IDLE_TIME directive might have to be set to even longer duration.
2. Implement the "on-demand-kill-session" feature using resource manager plan directive, combined with ability to use SWITCH_CONSUMER_GROUP_FOR_SESS API
3. Allow manual change of consumer group for an idle session using SWITCH_CONSUMER_GROUP_FOR_SESS to another group that has small enough MAX_IDLE_BLOCKER_TIME/MAX_IDLE_TIME directives so that the session can be killed (and more importantly any holding locks are released) immediately

Point (3) above is something that I am struggling to achieve and lead to my above question. The idle session never recognises the consumer group until it becomes active at least once. The idea is to be able to get rid of sessions, but only on-demand, if the corresponding "client" simply "abandons" the database session and there is no chance of the "client" coming back. I am hoping this would prove to be an extremely rare occurrence but was wondering whether resource manager would be able to help with this situation.
Hope this makes sense.

p.s. Before you say, why not simply use ALTER SYSTEM...DISCONNECT/KILL SESSION, I am operating in a oracle private cloud setup which does not allow any ALTER SYSTEM privileges. :)