Forum OpenACS Q&A: Re: Monitoring and Controlling Queries

Posted by Andrew Piskorski on
I'm not aware of any easy way to do it in Oracle, either, although it's been a long time since I really dug into it.

In Oracle you definitely can use sqlplus to connect with with DBA priviliges, find all the queries running, and kill sessions to make them stop. But that's nothing like automated, and it definitely gives you no real way to track that back to an OpenACS user_id that requested the page that ran the query.

Generally you never need to do this, not on most database backed websites or other OLTP style application anyway. However, this sort of "kill the runaway query" functionality can definitely be useful in a large data warehouse, say. Especially when you're developing a new query, and, oops, just did a cartesian join of several tables each with millions of rows...

But anyway, easier ways to kill runaway queries would be nice, but it's very unusual that you'd ever need to do so or even really care enough to want to. Developing a new query on a Dev server or in sqlplus is the only time I've ever really seen it. (If on the other hand you have a data warehouse type system that accepts arbitrary SQL queries from end users... Ok, then you'd better worry about being able to kill runaway queries.)

And in Oracle I think if you kill the client process (AOLserver or sqlplus), the query should be aborted, so for development that's usually the quickiest and easiest way by far to stop hosing your server. Oh wait. That could be completely wrong, I don't remember. A least sometimes, the query keeps going even after the AOLserver or Sqlplus client process is gone, but I don't remember if that's the normal behavior or only happens when something goes wrong. Regardless, in practice, even during development, it's fairly unusual to need kill a query manually (using your Oracle DBA Sqlplus foo).