Forum OpenACS Q&A: Kill all Oracle queries on AOLserver client exit?

Collapse
1: Kill all Oracle queries on AOLserver client exit?
Posted by Andrew Piskorski on 01/02/03 09:36 PM
If you run a very long running Oracle query from AOLserver (maybe because you made a mistake in your SQL), and then kill AOLserver, the Oracle query will continue to run inside Oracle. The fact that the Oracle client program (AOLserver) no longer exists makes no difference. (At least, that's always the way it's worked on all the Oracle 8i installations I've seen.)

Incidentally, what is PostgreSQL's behavior in this case?

To actually stop the query from running, you can either find the Oracle session with the runaway query and kill it with "alter system kill session 'SID,SERIAL#';", or you can find the Oracle unix process handling the now-defunct client (assuming you are not running Oracle MTS, of course), and kill that process with "kill PID".

Simply killing the Oracle process from the unix command line seems to work, and I suspect it is safe, but does anyone know for sure if it will always be safe? It is certainly simpler, so is there any reason to prefer the more complicated "alter system kill session ..." method?

If killing the Oracle process is always safe, then it might be a good idea to have the restart-aolserver script kill not only the AOLserver process, but any Oracle processes which are children of the AOLserver process. This would automatically kill all runaway queries when AOLserver is restarted. Has anyone tried this?

Alternately, if you're connecting to Oracle over Net8, you can add sqlnet.expire_time = 5 to "$ORACLE_HOME/network/admin/sqlnet.ora", which should tell Oracle to check every 5 minutes that the client is still connected, and kill the session of the client is gone. That sounds useful, although I haven't tried that yet. Also, it definitely won't wory for Bequeath Oracle connections, only Net8. Anyone used this before? How well has it worked for you? E.g., any problems with it wrongly killing a connection because of some transient network snafu?

In general, how have people here handled this "runaway Oracle query" problem? One of these methods above, or something different? I'm interested in you're experiences, for better or worse.

Collapse
2: Re: Kill all Oracle queries on AOLserver client exit? (response to 1)
Posted by mark dalrymple on 01/02/03 09:57 PM
Simply killing the Oracle process from the unix command line seems to work, and I suspect it is safe, but does anyone know for sure if it will always be safe? It is certainly simpler, so is there any reason to prefer the more complicated "alter system kill session ..." method?

One of the background processes (smon?) looks out for abnormally terminated processes and then cleans up the mess left behind (locks, allocated blocks with half-filled transaction data, etc). Doing the "alter system" stuff lets oracle clean that stuff up directly (presumably using information associated in the oracle process that's running the session) They're probably equivalent, with the alter sytem taking less time / fewer resources. Plus you never know if there's some race condition lurking in the oracle software that'll get triggered with the oracle process going away suddently.

personally I prefer to alter system to slaughter first, and then resort to the kill program if that doesn't work / things are in dire straits.

Collapse
3: Re: Kill all Oracle queries on AOLserver client exit? (response to 1)
Posted by Don Baccus on 01/02/03 11:13 PM
PostgreSQL's behavior is the same, the query will continue to run.  KILL -TERM <backend PID> should work fine to kill the query.  It's usually easy to find the runaway backend because it's the backend running away ...
Collapse
4: Re: Kill all Oracle queries on AOLserver client exit? (response to 1)
Posted by jerry arns on 01/03/03 01:00 PM
It is pmon (process monitor) in Oracle that cleanly terminates processes.

Why not let Oracle handle thoses things ?

Collapse
5: Re: Kill all Oracle queries on AOLserver client exit? (response to 1)
Posted by Andrew Piskorski on 01/03/03 04:12 PM
Jerry, what exactly do you mean by "let Oracle handle those things"?  The whole reason I brought this up, is that Oracle does not handle these things in quite the way we would like.