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

No, that statement is incorrect.

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

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).

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