How to set query timeout in DB?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to set query timeout in DB?

Hybrid View

  1. #1
    Join Date
    Feb 2002
    Posts
    48

    Lightbulb

    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?

  2. #2
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282


    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.

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    have you considered user profiles?
    Oracle DBA and Developer

  4. #4
    Join Date
    Aug 2001
    Posts
    75

    Smile

    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

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Feb 2002
    Posts
    48
    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.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2000
    Posts
    33

    use profile to control it

    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]

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: use profile to control it

    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.



    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width