Hi all: I have question on how to kill a long query run in Oracle 10 g, Let's say, I have an web app using Hibernate/Oracle/Spring On the app, i put in some term to search for, if the query run too long, i want to hit a Cancel button and it will kill the background query, not the connection to the database.
On the app, i put in some term to search for, if the query run too long, i want to hit a Cancel button and it will kill the background query, not the connection to the database.
Rather than relying on users to hit a cancel button if the query takes too long, why not add rules in your database to abort long running queries? It gives you a more dependable way of controlling the load on you DB server. Also, given you are using Hibernate, why not also use its build in row limit fuctionality to prevent large result sets?
Hi: I already limit the rownum, but some query take like 20 min to run. therefore, the user want to have that capability. If so, can you tell me how I am going to implement this using Hibernate/Oracle 10 g,
20 minutes to run! That is an enormously slow query - have you done any investigation why it is so slow? If you are already limiting the amount of rows returned this suggests something is seriously wrong with your environment, data model or application logic.
Session has a cancelQuery() method you can use. But I'd find out what is so wrong with the query itself rather than just relying on users to cancel. Runnign a query like that is liable to significantly impact other users of the system.