How to set query timeout in DB?

[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?

[Mnemonical]

There's a way of doing it if you are using Oracle Advanced Queuing. There's an exception numbered -25228.

declare
eTimeOut Exception;
pragma exception_init(eTimeOut, -25228);

begin
....

exception
when eTimeOut then
null;
end;


I'm not sure but I believe this exception error, that occurs when time is out, is only used for job queue processes ...

I hope I have helped.
F.

[kmesser]

have you considered user profiles?

[sanjay92]

If you are planning to use Oracle 9i, then use Resource Manager. From the Resource Manager, you can control how much max. time you want to give a query to execute. Resource Manager does also allow to control other resources like CPU, Memory etc. Setting up profile will not help.
If you are not using Oracle 9i, does this mean you are out of luck ? Well no, if you are using unix then you can use Unix Signal processing, if you are not using Unix, you can also use CYGWIN on Windows. If you are not sure how to do Signal processing, let me know, I have written a query automated tool which allows to users to run their query which uses the same feature. In my query tool, users drops their query in network in some particular folder, cronjobs looks for any new files in that folder and execute them and send user the result using email. I have also specified maximum time to execute query , so if query takes more than the my TIMEOUT limit, query are terminated and a email is sent to user saying that our query has been terminated.


Thanks
Sanjay

[julian]

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.

[cchiara]

Thank you all for your inputs!

Is MAX_ESTIMATED_EXEC_TIME resource plan directive parameter a 9i feature? We are currently on 8i. I was told by an Oracle rep yesterday that CPU_PER_CALL set in the PROFILE level will do the job.

We tested a query yesterday. Before running it, we SET TIMING ON to get the runtime of query. The time it took for the query to finish is 24 seconds. Initially we set the CPU_PER_CALL to 500 which is 5 seconds (just for testing purpose). Also, set RESOURCE_LIMIT = TRUE in session level. The query did NOT terminate at all after 5 secs. Why? Am I missing sth here? Or that parameter, CPG_PER_CALL is not the right one to use for my case.

[jmodic]

Originally posted by cchiara
The time it took for the query to finish is 24 seconds. Initially we set the CPU_PER_CALL to 500 which is 5 seconds (just for testing purpose). Also, set RESOURCE_LIMIT = TRUE in session level. The query did NOT terminate at all after 5 secs. Why?

Because your query might have spend only 0.5 seconds of CPU time and lost the remaining 23.5 seconds for I/O and other operations. Elapsed time of the query tels you nothing about the actual CPU time it used! You should set tracing on for that query and then analyzing the trace file with tkprof - this will tell you ther actual CPU cost.

Honestly I dont belive that by using CPU_PER_CALL limit you can actualy filter out queries that are longer than 2 minutes. At least not with any acceptable precision or reliability. There could be queries that last 5 minutes, eating only 5 CPU seconds, and there could be queries that lasts for 20 seconds that will consume 5 CPU seconds. So if you set the limit of 5 CPU seconds per call you'll terminate the querie that would only need couple of seconds more to finish, but you will not terminate the query that will take 5 minutes to finish.

[anujpathak]

use profile as mentioned below and set parameters to control the I/O like :-

LOGICAL_READS_PER_SESSION unlimited LOGICAL_READS_PER_CALL 100

example statement to create profile
read more in doc on profile

CREATE PROFILE clerk LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION unlimited
CPU_PER_CALL 6000
LOGICAL_READS_PER_SESSION unlimited
LOGICAL_READS_PER_CALL 100
IDLE_TIME 30
CONNECT_TIME 480;



[Edited by anujpathak on 04-11-2002 at 07:04 AM]

[julian]

Originally posted by anujpathak
use profile as mentioned below and set parameters to control the I/O like :-

LOGICAL_READS_PER_SESSION unlimited LOGICAL_READS_PER_CALL 100

example statement to create profile
read more in doc on profile

CREATE PROFILE clerk LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION unlimited
CPU_PER_CALL 6000
LOGICAL_READS_PER_SESSION unlimited
LOGICAL_READS_PER_CALL 100
IDLE_TIME 30
CONNECT_TIME 480;



[Edited by anujpathak on 04-11-2002 at 07:04 AM]

I don't think that solves his problem. He wants queries longer than 2 minutes to be terminated. MAX_ESTIMATED_EXEC_TIME is the best candidate for that. He will not even bother with spending 2 minutes of CPU resources with using that parameter for Oracle will not start it if Oracle estimates that the query will take more than 2 minutes. But he needs to upgrade to 9i.

[tamilselvan]

Query running more than 120 seconds can be killed (terminated) by having a dbms_job that executes another procedure to kill a session.

The view v$session_longops has SID, SERIAL#, start_time, elapsed_seconds, time_remaining columns.
If elapsed_time > 120, call the killing_session procedure.

I have not done it so far. But I got the idea how to do it.

Very soon you will see the complete procedure here.