Re: Stopping run-away Oracle queries

peterjoh_at_my-deja.com wrote in message <813ufr$srl$1_at_nnrp1.deja.com>...
>Hi all.
>Is it possible to stop 'run-away' queries on Oracle 8.0.5?
>
>We are running netscape enterprise server, on Solaris, talking to
>Oracle, and sometimes we create stupid queries that take forever
>(almost) to complete. We need to abort these queries and free up the
>oracle server for a new request.

If you are accessing the Oracle via a web server then you have two "server" processes running. The CGI server process (or equivalent) that connects to Oracle as a client. Then you have the Oracle server or "shadow" process that services that connection.

Killing the CGI process will not cause the immediate termination of the shadow process. If Oracle is busy processing and the client dies, there usually is no "close socket" call from the client to close the TCP connection to Oracle. Oracle will only find out that the client is gone after it has completed the query and tries to send data down that TCP connection. The listener can be configured to poll for dead connections (dead connection detection) in which case it will check at regular intervals for a dead Oracle client process and then terminate the corresponding shadow process.

The best solution thus is to kill the process at its root. Which means killing the Oracle shadow process (ALTER SYSTEM KILL SESSION command). This will then terminate the shadow process, which will gracefully close the connection to the GCI process with an ORA error (saying something like process has been killed). The GCI in turn can then gracefully exit and return an error html document to the webserver which in turn can pass it on to the browser.

If life was only that simple... How do you implement this? First off, the ALTER SYSTEM KILL SESSION command needs DBA privileges as it is a dangerous command when incorrectly used or simply abused. Secondly, how do you allow the user to cancel the query (and thus kill the Oracle shadow process) from within the browser?

A method that may work (am thinking out loud/typing here ;-), is to set a cookie on the browser that uniquely identifies that request (i.e. CGI process with corresponding Oracle shadow process) to the database. While the user is waiting for the CGI to return data, you allow him to click a CANCEL button (maybe in a frame). This CANCEL button submits the unique request id cookie that you send him. This request id is used as a parameter to a cancel-process CGI. This CGI on the server then uses the request id to find the associated Oracle session and (using DBA privs), kills that session.

OK, now how do you create a unique request id? One way will be to use a sequential number generator in Oracle. This can be called via Perl for example to obtain a unique number for the browser transaction/request. You can even use the set module application info call in Oracle to set the client info column in V$SESSION to that unique number before executing the query. It should be a fairly simply matter then to find the Oracle shadow process for a specific browser request.

If this were to be done in a standard client-server architecture, it will be very simple and straight forward. Unfortunately, running a web server as the gateway or interface into Oracle adds a lot of complexity to the scenario.

regards,
Billy Received on Mon Nov 22 1999 - 05:47:38 CST