How to cancel a long running query (Part 2)

Earlier this year I wrote about a possible way to cancel a long running query from the UI

       see How to cancel a long running query from the UI

 I finally got around to  experimenting with this more and have created a sample app that can be downloaded from here 

A screenshot is below.   The sample was created with jdev 11.1.1.4 but It should work with 11.1.1.6 without any problems. This demonstrates the creation of a dynamic view displayed in a dynamic table and includes the cancel button.    You can type any valid sql into the input box and the table will display all of the columns returned.   If you create a particularly slow query you can test the cancel query button and you should get the error message displayed below.  Obviously you wouldn't want to give the user the ability to provide the SQL for the dynamic query but it makes for a good example of how a dynamic query can be created and displayed.

Since ADF blocks all requests going through the faces servlet while the server is processing the query,  I implemented the cancel button using a client listener that cancels the event then creates it's own ajax call to a plain old servlet.    That servlet retrieves the view object (if it exists) from a transient map object stored on the session by the CustomViewObjectImpl class and calls cancelQuery() on it.  If the cancel is successful the executeQuery method will throw an exception that is displayed in the UI.    

When you set a query timeout on the view object, ADF will spawn a monitor thread that sleeps for the timeout period.  When the thread wakes up it calls cancelQuery() on the ViewObject.    This is the very same thing the servlet is doing except it is initiated by the user instead of the monitor thread.
  
The view object is stored in a transient map so that Serialization will not be attempted however the view object is only stored on the Session for the duration of the execute method and then removed so it should never attempt serialization anyway.


Note this will not work in a clustered environment not configured for sticky load balancing.   Obviously the request must make it to the same server where the query is running in order to cancel it.  I don't know of an easy way around that.  Ideally it would be nice if you could send a command to the database containing a key to the session needing it's query canceled then it wouldn't matter which server the cancel request arrived on. That feature is not available apart from the Linux command line.


This sample app will cancel all queries running on the users session if for example the user had multiple windows/tabs open, each running their own queries (not too likely) but you can pass up a key on the cancel request if you want to limit the cancel to a specific query.  

Note:  I initially could not get the cancel to work when running on Windows with the integrated development environment.  It ran fine however when I deployed it to the server running Linux.  
The query timeout setting was also not being honored when running under Windows.   I know this used to work for me so I'm thinking there must have been a network configuration change or upgrade that broke it.  I then discovered that I could get the cancel to run under Windows by adding the following JVM startup parameter to the "Launch Settings"

 -Doracle.net.disableOob=true 

This disables "Out of Band" breaks and instead uses "In Band" Breaks.  The sample app is configured this way but you may or may not need it depending on your network drivers and configuration.

Feel free to comment if you find this useful.

To progress this concept further I would like to add a page to our monitoring application that will iterate over the sessions and display a list of all long running queries and the users that are running them.