Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Hauke.

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

Last updated: October 26, 2021 - 2:36 am UTC

Version: Oracle Database 11g Release - 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


    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
    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
    COMMENT=>'This is the consumer group that has no limits (default)'

  -- and we need a resource plan:
    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
    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
    COMMENT=>'Kill statement after exceeding total execution time',

    COMMENT=>'leave others alone'
    -- ,CPU_P1=>100

    COMMENT=>'leave others alone'
    -- ,CPU_P1=>100




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


  (5 ratings)

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


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



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.


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?

More to Explore


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