Toad World® Forums

Why does cancelling a query take a long time?

There have been a number of instances where Toad can take over ten minutes to cancel a query. I’m currently waiting on a query to cancel that just went over fifteen minutes, and it’s still going. Out of curiosity, can someone give me some insight into what goes on behind the scenes such that cancelling a query takes as long as it does?

Thanks,

Cory

UPDATE: After waiting 35 minutes for the query to cancel, I killed Toad via the Task Manager. I have no idea why the query sat in the “cancel pending” status for that long.

When you click “Cancel”, we send a cancel command to Oracle right away and wait for Oracle to tell us “ok, it’s cancelled”. I don’t know why sometimes it comes back right away and sometimes it takes a while.

Is there a functional requirement for waiting until Oracle confirms the query is cancelled? Is it possible to cancel the query from the application and move on to executing another query without waiting for a cancellation confirmation? Locking the application down while waiting for Oracle to confirm that the query has been cancelled means that all of my work is brought to a halt. When this happened earlier today, I had to kill the instance of Toad in the Task Manager and open a new instance of the application to keep working.

With that in mind, if I can kill Toad from the Task Manager and open a new instance and I can continue my work, does Toad really need to wait for the Oracle notification?

As with most things in Oracle, the answer is “it depends.” We can’t do anything else in that session until the query is cancelled. If it’s a threaded query, then we could abandon the session and just move on. But then if you had any pending transaction in that session, it would likely be rolled back. Maybe we can add a “kill session” button to the cancel dialog. That would be better than you having to kill Toad.

That would definitely be a handy option to have! It would be great if that could be added to a future release of Toad.

Thanks!

Cory

As far as I know for term semaphores, which Oracle use in some kind of form still as well…this is the the way it operates.

There are interruptible and non interruptible processes monitored by semaphores.
This mean that some processes will not ask for cpu instruction until they finish and come to interrupt part. Then they ask in a way “is there something for me”? If not continue and if there it is …they execute that statement, in this way kill instruction.

Typical non interruptible process is when Oracle starts to undo (rollback) segments … then nothing will stop that because it has to be done. So that process will not be able to stopped in any way but until it finished.

Typical interruptible process is select statement, which is very easy cancelled.

More can be read on many articles on that subject as

docs.oracle.com/…/p-online-2.html

I’m just now getting back to this, Cory - do you have an example of a query that doesn’t like to cancel? If you could send me one, along with some table DDL, that would make testing easier. I’m trying a few queries here but they all cancel when I tell them to.

Email me directly if you’d prefer: john.dorlon@quest.com

Thanks.

You can ALT+F4 out of the dialogue which gives you control again. My session then refused to process further statements and hung when i tried to disconnect but it will at least allow you to salvage the statements you were running.

I find it happens more commonly on linked tables if that helps you narrow it down (assuming you are still looking at this!)

You can ALT+F4 out of the dialogue which gives you control again.
This is far from the true.

In many situations, even closing SQL*Plus leave running session on server. Even when you kill such a session, you get status killed, but session is still using resources.
So, it is not straight forward and depend of the type of commands and how action was executed.

You can ALT+F4 out of the dialogue which gives you control again.

This is far from the true.

Clearly it isn’t or why would I waste my time signing up to share this one piece of information? If you want to step us through what you are doing perhaps someone will explain what you are doing wrong.

In many situations, even closing SQL*Plus leave running session on server. Even when you kill such a session, you get status killed, but session is still using resources.

So, it is not straight forward and depend of the type of commands and how action was executed.

Thanks for the additional information, I thought this was fairly obvious from the rest of the post where I explained that this just closes the dialogue, that further commands don’t work but that it is still useful so you can salvage your SQL, but I guess some people might not realise this is why.

I can wait for hours, so would be good to get this issue resolved. Maybe there is an oracle defect that needs investigating??

There's info online for cancellation of a query and the time it takes, etc. AskTom has some info, but it's more or less an explanation of what goes on when cancelling. There are some brute force methods you can do as well I've seen in StackOverflow and elsewhere.