Topic
5 replies Latest Post - ‏2014-05-27T06:10:17Z by mbrianto
jmueller
jmueller
26 Posts
ACCEPTED ANSWER

Pinned topic SQL Statements Dashboard: Execution Elapsed Time

‏2012-05-07T10:57:25Z |
Hello OPM-Team.

Which calculation refers to the Execution Elapsed Time. I believe in the last edition(3.1)
the Execution Elapsed Time is the difference between the Stop Time Stamp and Start Time Stamp.

See the attached screenshot from the SQL Statements Dashboard.

Thanks and best regards
Joachim
Updated on 2012-05-07T16:08:37Z at 2012-05-07T16:08:37Z by UteBaumbach
  • UteBaumbach
    UteBaumbach
    55 Posts
    ACCEPTED ANSWER

    Re: SQL Statements Dashboard: Execution Elapsed Time

    ‏2012-05-07T12:48:02Z  in response to jmueller
    Hi Joachim,
    with last edition you mean 5.1 ?

    Ideally the elapsed execution time should be the difference between stop and start time. But we know that the calculation is sometimes incorrect (also already in 5.1), especially for tricky cases where for example the statement stopped between 2 OPM data collections and therefore the final stop timestamp and final elapsed execution time is not available in the collected data. We already did a fix for OPM 5.1.1 to improve the formulas, but it turned out that we unfortunately did not catch all cases. We are working on further improvements on this calculation and plan to provide an APAR for OPM 511.

    Regards
    Ute Baumbach
    • jmueller
      jmueller
      26 Posts
      ACCEPTED ANSWER

      Re: SQL Statements Dashboard: Execution Elapsed Time

      ‏2012-05-07T13:35:28Z  in response to UteBaumbach
      Hi Ute,

      Thanks for the explanation. Sure, I mean 5.1.

      It's irritating to see another number in the stop and start coloumns as the calculated time.
      So I will wait for the next improvements...

      Thanks and best regards
      Joachim

      P.S.: Do you have some samples for the user defined alert types(specifically for long running transactions and failed backups)
  • UteBaumbach
    UteBaumbach
    55 Posts
    ACCEPTED ANSWER

    Re: SQL Statements Dashboard: Execution Elapsed Time

    ‏2012-05-07T16:08:37Z  in response to jmueller
    Hi Joachim,
    for tracking failed backups using the user-defined alerts you could for example use the last backup timestamp. E.g. when it is too old then generate an alert.
    This is an example for such an SQL statement:
    select case last_backup when '2012-01-06-22.47.42.000000' then 2 else 0 end as returnvalue from sysibmadm.snapdb;

    Regards
    Ute
  • ruhido
    ruhido
    6 Posts
    ACCEPTED ANSWER

    Re: SQL Statements Dashboard: Execution Elapsed Time

    ‏2013-08-07T19:00:31Z  in response to jmueller

    Hi Joachim,

    After seeing your screenshot it got my attention your insert is showing an elapsed time of 1:21:59... is this a single-row insert or something like an IMPORT or buffered insert operation?. We're getting complaints from our Application Dev Team that inserts are taking +1 second (sometimes +1 minute) from time to time in our database but we haven't managed to catch any of these inserts in Top Individual Executions on OPM... Execution Summary will always average elapsed time between total executions and that doesn't work to identify this problem.

    Can anybody share their comments if seeing anything similiar ?.

    Thanks

  • mbrianto
    mbrianto
    1 Post
    ACCEPTED ANSWER

    Re: SQL Statements Dashboard: Execution Elapsed Time

    ‏2014-05-27T06:10:17Z  in response to jmueller

    Hi everyone.

    I'm checking information on an OPM report but I've actually never used/configured it myself. I have another question regarding the Execution Elapsed Time. In a partitioned environment, what is this time referring to? To the Execution Elapsed Time of the query as a unique engine processing the query, or does it consider the time that the query runs in each partition and add it on at to present the results?.

    So, let's say, if in a TopSQL Report for a DB with 10 partitions I have an entry with Execution Elapsed Time of 6 hours. Does it mean the response time for the user was 6h or aprox 36min (6h/10). Which would be the right interpretation of this value??

    Thanks!