Originally posted by cchiara
We have set timeout for long running query in the application to 2 minutes. In other words, if a query is running over 2 minutes, the application will terminate the process of the query automatically.

We would like to set the same standard in the database. How and what can we set such that when a particular query is running over 2 minutes, it will be terminated so that the long-running query won't take up so much resource and affect other users in the system?
I would use the MAX_ESTIMATED_EXEC_TIME resource plan directive parameter. Here is why: I wouldn't like to have the query hanging for 2 minutes before being killed. I would rather would like Oracle to estimate if the operation will take more or less than 2'. What is benefit of that you might ask: well, then eliminate the use of system resources by jobs taking more than 2 minutes.