Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    66

    Unanswered: executiontime on snapshot for dynamic sql includes lockwait??

    Hi all,

    Execution time shown for a query in a dynamic sql snapshot does not include the time it waits on locks.Is this statement correct??

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    No, that statement is incorrect.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2007
    Posts
    66
    Thanks for the reply.Thats what I initially thought.But when I saw all the query response times below one second and the total database lockwait time of 3000 seconds,I was confused.The application people are complaining about slow performance but we can not see any query executing for more than a second.


    Does that mean all the individual queries are ok and as a transaction it is slow?Could you help me identify the bottleneck?

    Thanks a ton for your help

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One second is a long time for an SQL statement in an OLTP application. Most SQL statements should complete within .06 seconds or less on average. Transaction response time depends on how many SQL statements are in each transaction, as there is often a lot of time spent going from the application server to the database server, and back to the application (which is not included in SQL total elapsed time).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2007
    Posts
    66
    Thanks for the information.There is uow_execution_elapsed_time_s in snapshot_appl table function.Can we use that monitoring element to know the average transaction execution time?There are others like Uow_start_time and Uow_stop_time also.

    I want to know the top 10 transactions and know the queries that are part of the transactions.Is there anyway to do so.Thanks a bunch

Posting Permissions

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