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.
Hi there, 2023 and this issue still persist? Tried to execute merely a select statement and took to long, when I cancelled it it hang, now at 20 minutes and counting... any updates on some resolution please?
HI @saad.ba.alami ,
I think this is an environmental issue/compatibility of some sort. For most users, SQL cancels quickly. I have tried all sorts of long-running SQL and don't have a way to reproduce this. We hear reports of this "cancel-hang" from time to time, but we don't have any clear info on when it happens. I added a "kill session" feature to the cancel dialog in Toad several years ago in response to this post. The idea being that if cancel didn't work, you could kill the session.
We have found issues with 21c and 19c clients having problems cancelling with Oracle 11gR2 servers. The solution here would be to use a 12cR2 or 18c client. Toad also has a no-client option that may work better regarding query cancellation. I would be interested to know if this helps for you.
I have also heard a report of problems cancelling on Windows Server 2019 based Citrix servers (where it worked on older Citrix servers), but I didn't get much follow up from that user so I don't know if a change of Oracle client would have fixed the problem.
What can you tell me about your environment, and does it help if you use a different Oracle client (or none at all)? And, do you have any luck with the "kill session" button on the cancel dialog?
And this is why i rather use "kill session" than cancel!
For small easy thing Cancel is OK, for anything more complex-kill!
and this depends what kind of connection you have made...but do not go so far...
Just use most appropriate way of stopping (according your db version and env...which really matters a lot).