Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Hauke.

Asked: June 20, 2018 - 10:33 am UTC

Last updated: May 21, 2024 - 6:54 am UTC

Version: Oracle Database 11g Release 11.2.0.4.0 - 64bit

Viewed 10K+ times! This question is

You Asked

Dear Tom,

I'm wondering about how a 'cancel' in Oracle works.
For example, I have a long running query and I define a timeout on application level.
Once the timeout is reached, the application sends a 'cancel'.
What i observed is, that the cancel does not seem to be working.
I talked with an DBA, and he said that Oracle will first finish the query and then checks if it should be committed or rolled back, and the cancel will tell Oracle to rollback.
Is that correct?
And if that's the case, a cancel would not be able to actual cancel a statement / transaction which is in deadlock like situation, is that correct?

If so, how should an application 'terminate' a long running session?
I imagine something like a business constraint saying that query XYZ is only supposed to take X seconds and if it takes longer than that, it should be aborted.
So based on the description above, cancel would be not sufficient, am i right?


Thanks in advance!

and Connor said...

There is a few "depends" in here.

- Some applications simply abandon the connection, in which case the database simply doesn't know you've "gone away". It will run the query and when it comes to present the results to the client, it will find the client is no longer there. So in that sense, your DBA is right.

- Alternatively, the app may send an explicit cancel request to the database. Most of the time, the database will catch this and stop the query right there. I say "most of the time" because if the database is in a non-interruptible phase, it will still complete that part before your query will be terminated.

You could look at using the resource manager to kill off sessions when they exceed a certain threshold. Here is a sample script to do that

begin
  dbms_resource_manager.create_pending_area();
  --

  dbms_resource_manager.create_consumer_group(
    CONSUMER_GROUP=>'CG_EXEC_SHORT_LIMIT_CANCEL',
    COMMENT=>'This is the consumer group that has smallest execution time per statement'
    );

  -- this group will be the one we switch into before running a possibly
  -- long running SQL statement
  dbms_resource_manager.create_consumer_group(
    CONSUMER_GROUP=>'CG_EXEC_TIME_LIMIT_CANCEL',
    COMMENT=>'This is the consumer group that has limited execution time per statement'
    );

  -- this group will be the one we switch back to after running the long statement
  -- to avoid potentially cancelling a "genuine" long running one
  dbms_resource_manager.create_consumer_group(
    CONSUMER_GROUP=>'CG_EXEC_TIME_LIMIT_DEFAULT',
    COMMENT=>'This is the consumer group that has no limits (default)'
    );

  -- and we need a resource plan:
  dbms_resource_manager.create_plan(
    PLAN=> 'EXEC_TIME_LIMIT',
    COMMENT=>'Kill statement after exceeding total execution time'
  );

  -- now create a plan directive for that special user group
  -- the plan will cancel the current SQL if it runs for more than 'n' sec
  dbms_resource_manager.create_plan_directive(
    PLAN=> 'EXEC_TIME_LIMIT',
    GROUP_OR_SUBPLAN=>'CG_EXEC_SHORT_LIMIT_CANCEL',
    COMMENT=>'Kill statement after exceeding total execution time',
    SWITCH_GROUP=>'CANCEL_SQL',
    SWITCH_TIME=>10,
    SWITCH_ESTIMATE=>false,
    SWITCH_FOR_CALL=> TRUE
  );


  -- now create a plan directive for that special user group
  -- the plan will cancel the current SQL if it runs for more than 'n' sec
  dbms_resource_manager.create_plan_directive(
    PLAN=> 'EXEC_TIME_LIMIT',
    GROUP_OR_SUBPLAN=>'CG_EXEC_TIME_LIMIT_CANCEL',
    COMMENT=>'Kill statement after exceeding total execution time',
    SWITCH_GROUP=>'CANCEL_SQL',
    SWITCH_TIME=>90,
    SWITCH_ESTIMATE=>false,
    SWITCH_FOR_CALL=> TRUE
  );

  dbms_resource_manager.create_plan_directive(
    PLAN=> 'EXEC_TIME_LIMIT',
    GROUP_OR_SUBPLAN=>'CG_EXEC_TIME_LIMIT_DEFAULT',
    COMMENT=>'leave others alone'
    -- ,CPU_P1=>100
  );

  dbms_resource_manager.create_plan_directive(
    PLAN=> 'EXEC_TIME_LIMIT',
    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
    COMMENT=>'leave others alone'
    -- ,CPU_P1=>100
  );

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

end;
/



so you would switch people into the group before running a query. If they exceed their time limit, they'll be cancelled. So if your app server timeout was (say) 30 seconds, you would set this to 35 seconds so queries will be cleaned up shortly after your app has lost interest in them

Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hauke Precht, June 25, 2018 - 7:21 am UTC

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.

Connor McDonald
June 25, 2018 - 9:27 am UTC

non-interruptible was perhaps a poor term, a better term would have been "ignore interruptible".

When an application wants to cancel, it sends a signal to the process. It is up to the process to receive that signal and interrupt what it is doing to cancel the execution.

So there's a number of moving parts there that all need to be in alignment.

- the platform has to be able to manage an out of band break. (For example, you can Ctrl-C on sqlplus on unix but not on Windows )
- the process needs to in a state that it can stop what it is doing and act on the signal it has received.
- the sqlnet layer/protocol to support it (for a long time you could only do such things in the thick client and not thin client for example).


Hauke Precht, June 25, 2018 - 10:20 am UTC

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!
Connor McDonald
June 26, 2018 - 2:53 am UTC

There's a few notes in MOS about out of band breaks. That's all I know of.

Another option...

J. Laurindo Chiappa, June 26, 2018 - 1:39 pm UTC

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

Interrupting Long Running Queries - But only if they have not started to return data to the user

Sergio Del Rio, March 23, 2021 - 3:07 pm UTC

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.
Chris Saxon
March 23, 2021 - 4:29 pm UTC

I'm not aware of one.

But even if there is, in an N tier application, just because the client of the database has fetched rows, doesn't mean these have been passed to the end user. The DB client may wait to get the whole result set before passing these rows onto the next tier/end user.

How cancel really works

Michael Thompson, October 22, 2021 - 11:14 am UTC

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
Connor McDonald
October 26, 2021 - 2:36 am UTC

After i submit the query the only way out of the screen is to click on cancel

Sorry - what tool are you referring to here?

CANCEL_SQL vs KILL_SESSION

Narendra, March 14, 2022 - 10:53 am UTC

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.

Connor McDonald
March 15, 2022 - 2:28 am UTC

There is nothing in the docs that says you cannot use this, but I suspect that is just an omission. I'll ask around internally, but my hypothesis that switching for a session is in effect asking the session itself to take action.

"Hey, I've switched you into this group, please take the appropriate action"

A kill session is something I don't a session could do to itself, it needs an external party to initiate that.

Re: CANCEL_SQL vs KILL_SESSION

Narendra, March 14, 2022 - 11:17 am UTC

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.

Re: CANCEL_SQL vs KILL_SESSION

Narendra, March 15, 2022 - 1:43 pm UTC

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.

CANCEL_SQL with SWITCH_CONSUMER_GROUP_FOR_SESS

Narendra, October 09, 2022 - 9:58 pm UTC

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
Connor McDonald
October 10, 2022 - 2:00 am UTC

I think you need to make sure it is available in your current resource plan

For example:

SQL> show parameter resource_manager_plan 

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


Lets see what groups are in that 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


Now my current session has:

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

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


If I try switch into a fictional group, I get an error as I would expect:

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


But lets see what consumers are floating around (not necessarily attached to DEFAULT_PLAN)

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


If I switch into one of those...

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.


I do NOT get an error, but LOW_GROUP isn't part of DEFAULT_PLAN ... so what happened?

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

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


I went back to my default. If I nominate a group that IS in the plan

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


So I suspect the plan you are using does not have a cancel_sql entry

Re: CANCEL_SQL with SWITCH_CONSUMER_GROUP_FOR_SESS

Narendra, October 10, 2022 - 7:21 am UTC

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.
Connor McDonald
October 11, 2022 - 3:33 am UTC

A few things to check

- v$sql.sql_quarantine, see if its due to an auto quarantine policy
- dba_sql_quarantine, or manual quarantine policy
- are you looking in pdb or cdb level? There could be cdb resource limit rules being pushed down into the pdb

Re: CANCEL_SQL with SWITCH_CONSUMER_GROUP_FOR_SESS

Narendra, October 11, 2022 - 2:58 pm UTC

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

Re: CANCEL_SQL with SWITCH_CONSUMER_GROUP_FOR_SESS

Narendra, December 09, 2022 - 3:51 pm UTC

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.
Connor McDonald
December 12, 2022 - 3:40 am UTC

No need for apologies and thanks for taking the time to get back to us.

SWITCH_CONSUMER_GROUP_FOR_SESS consumer group change not immediate

Narendra, February 24, 2023 - 11:53 pm UTC

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
Connor McDonald
February 28, 2023 - 3:07 am UTC

I think both are "correct" but the wording could be better.

When the doc refers to "Switching consumer groups for running sessions in-flight" my understanding is that is talking about sessions performing calls to the database.

In the example it gives, it is actively consuming CPU, making IO calls etc. The session is active, and when it makes the next "call" (ie in the process of continuing to run its current query) its resource group will change (to get the lower cpu priority).

In your example above, the "next call" never comes because you're idle. The workaround is to have an *initial* consumer group that people are in (with the appropriate limits you want to set) which can be done with

dbms_resource_manager.set_initial_consumer_group

Re: SWITCH_CONSUMER_GROUP_FOR_SESS consumer group change not immediate

Narendra, February 28, 2023 - 8:44 am UTC

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. :)
Connor McDonald
March 09, 2023 - 4:19 am UTC

Can you not set "max_idle_blocker_time" at system level?

Auditing of changes to Resource Manager Plan Directive

Narendra, May 14, 2024 - 4:27 pm UTC

Hello Chris/Connor,

Is there a way to audit the changes to Resource Manager Plan Directives and Resource Manager Consumer Group definitions?
I am thinking of using Resource Manager plan in PDBs but with a view of those being "flexible".
One potential use case is to implement Resource Manager plan for a PDB in a way that would not put any limit on, say CPU usage, to begin with. However, as this database shares the hosts (Host VM but not CDB) with other databases, I would like to update Resource Manager Plan Directive to limit the CPU usage to prevent this from becoming "noisy neighbour". This change could be for a limited period and could be reverted back when the overall load on the underlying host(s) decreases.
I have tested this and it appears to work as expected and is an "online change" but I would want to have ability to be able to maintain audit of such changes before I propose the same.

Thanks in advance
Connor McDonald
May 20, 2024 - 1:04 am UTC

Not directly to my knowledge. You could however audit any executions to DBMS_RESOURCE_MANAGER, eg


SQL> create audit policy res_mgr_policy
  2  actions execute on sys.dbms_Resource_manager
  3  when    '1=1'
  4  evaluate per statement;

Audit policy created.

Re: Auditing of changes to Resource Manager Plan Directive

Narendra, May 20, 2024 - 6:50 am UTC

Hello Connor,
Thank you for your suggestion.
I was toying with the idea of enabling auditing on the underlying table that stores the details of resource plan directives. Is this possible/supported?

Thanks in advance,
Connor McDonald
May 21, 2024 - 6:54 am UTC

Not really because its a database dictionary view.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.