Skip to main content

Why some queries exceed time cap set?

  • August 26, 2013
  • 4 replies
  • 7 views
Greg_Starosciak
We set parameter to limit execution time for queries to 30 seconds. In error_messages we can observe, that it works - message "Execution time exceeded run time cap of 00:00:30". But measuring execution time for queries shows that some of them exceed this given time. Some queries can last even few minutes. Why it is like this? How to limit it permanently?

    4 replies

    Shobhit_Garg
    Forum|alt.badge.img
    • Shobhit_Garg
    • Participating Frequently
    • August 27, 2013
    Hi,

    Did you mean that error_messages shows "Execution time exceeded run time cap of 00:00:30" but actually that query is running for more than 30 seconds cap?.
    How the execution time of the queries being measured.

    Also RUNTIMECAP parameter can be set at session level or user level or the resource pool. At which level you have set this parameter.
    If it is at session level, then as soon as you exit/end the session,     the RUNTIMECAP parameter value will be reset to the actual value for that user set by the super user.

    Thanks
    colin_loghin
    Forum|alt.badge.img+2
    • colin_loghin
    • Participating Frequently
    • September 7, 2013
    There are situations in which query run time cap is not effective. (1) if the server has already started returning results and the client is an application that manually scrolls or otherwise does not consume the result set, then the query would continue to run in blocking mode until client consumes the entire result set or cancels. (2) Unexplained cases in which the runtime cap is not honored. For that matter,we have put a regular crontab script in place that monitors sessions table statement_start parameter and performs an interrupt_statement/close_session operation to release resources and force the interval. For a 30 seconds cutoff this would be too much, but when used over something greater than 5 min, it is practical.
    jmussom
    • jmussom
    • New Participant
    • November 23, 2018

    Hello,

    Taking into consideration the scenario (1) "if the server has already started returning results and the client is an application that manually scrolls or otherwise does not consume the result set, then the query would continue to run in blocking mode until client consumes the entire result set or cancels." It is still happening in version 9.1.x

    is there any way to cancel automatically this queries taking so long? In spite of I defined the runtimecap for the resource pool to 30minutes, we are having few queries running for many days, for the moment I am canceling them manually...

    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • November 29, 2018

    Does the resource pool where you are running the query has a cascade to option set? Is the query cascading to secondary pool and running there? The reason I ask this is because when runtime cap exceeds and secondary pool is set using cascade option, then the query executes on that pool

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement