Oracle: Limiting Query Runtime Without Killing the Session
Recently, a customer request came in to limit the runtime of a query in a 10g database for a certain user. But since connection pooling was being used, the session itself should NOT be killed.
Now, why would somebody ask for that?
I think a reason to implement such a restriction is, for example, if a website application requests data from the database and the query takes too long, then the user sitting in front of the web browser cancels the request, and so it would be beneficial to also cancel the query and not have it still running in the background. There might be quite a few other reasons why such restrictions should be implemented.
Anyway, so what would be the best solution for this? The Resource Manager, a feature not too often used in Oracle.
Actually, “overlooked” might be the better word—overlooked when investigating performance problems. I imagine it must be a new DBA’s nightmare to be confronted with end user complaints of “bad performance”, only to find after a long search that the Resource Manager has restricted the CPU for a specific user.
But back to the topic. Resource Manager not only allows one to define how much CPU a certain user or group gets, it also lets one switch into lower priority groups and kill a query while leaving the session running. It is like running a SELECT statement in sqlplus, and pressing ctrl-c. The session is still alive, yet the query is canceled.
Sure, the Resource Manager is documented by Oracle, but when I started to test this feature, I came across a few very interesting things that are not as well documented, and that’s what prompted me to write this post:
