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: March 15, 2022 - 2:28 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

  (8 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.

More to Explore

Performance

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