Thursday, November 24, 2011

How To Cancel A Query Running In Another Session

This is not really anything new - in fact Tanel Poder has already blogged about it a while ago. Tanel has specifically covered the handling of "urgent" TCP packets and how this could be used to signal a "cancel" to another process, however this only works on Unix environments and not with Windows SQL*Plus clients. In Tanel's article it is also mentioned that there is an officially documented way of doing this via the Resource Manager if you happen to have an Enterprise Edition license.

In my quick tests however the call to DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS using "CANCEL_SQL" as consumer group only errors out with ORA-29366 saying that the specified consumer group is invalid.

So ideally there should be an approach that is independent from client or server O/S or license details, and indeed there is one, however it is using an undocumented event and therefore is unsupported and can only be used at your own risk.

If you set event 10237 ("ORA-10237: simulate ^C (for testing purposes)") in a session to any level greater 0 then any currently running and future execution will be "cancelled", so once the cancellation was successful the event needs to be unset otherwise the session will be in an unusable state cancelling any further attempts (applies even if the "lifetime 1" clause is used instead of "forever" when using ORADEBUG to set the event).

So a simple script like the following should be sufficient to cancel a current execution in another session without the need to kill the session.

------------------------------------------------------------ simulate_control_c.sql---- Purpose:---- Sets event 10237 in a session to simulate-- pressing CONTROL+C for that session---- Allows to cancel a running SQL statement from-- a remote session without killing the session---- If the session is stuck on the server side-- which means that it can't be killed this-- probably won't help either---- Requirements:---- EXECUTE privilege on SYS.DBMS_SYSTEM-- SELECT privilege on V$SESSION---- Usage:---- @simulate_control_c <SID>---- Note:---- The usage of that event is undocumented-- Therefore use at your own risk!-- Provided for free, without any warranties --- test this before using it on anything important---- Other implementation ideas:---- The following code is supposed to achieve the same on Enterprise Edition-- and enabled Resource Manager in a documented way-- In all versions tested (10.2.0.4, 11.1.0.7, 11.2.0.1, 11.2.0.2) I get however-- ORA-29366 and it doesn't work as described-- Note that the official documentation doesn't explicitly mention CANCEL_SQL as -- valid consumer group for this call

-- Back off after 5 seconds -- Check only every second from then on -- Avoids burning CPU and potential contention by this loop -- However this means that more than a single statement potentially -- gets cancelled during this second if dt_start + (5 / 86400) < sysdate then dbms_lock.sleep(1); end if; end loop; end if;end;/

It is particularly useful in Windows environments where the SQL*Plus executable by default doesn't allow cancelling a current execution by pressing Control+C - it works only while fetching or pressing it a second time, terminating the whole SQL*Plus client.

Note that Tanel's method is probably able to cancel queries that this approach cannot cancel because the URGENT signal handler under Unix effectively causes an interrupt to the running process executing the corresponding handler code whereas the event set here has to be actively checked by the code of the running process.

FWIW I've just re-published the source code, because I've again forgot to check for greater or less characters screwing up the formatting of the code, although it looks like only the comment section was affected - the code itself should have been fine in the initial version.