Many a times when we submit queries in production they go into delayed state and the total execution time displayed in the SQLA doesnt show the real picture, in such cases how can we calculate the actual time the query takes to run i.e how long its been inactive state (no access to viewpoint)
Is it possible to obtain it from the DBC.Qrylog table ? and if so how ?
FirstRespTime - StartTime = Total SQL Execution Time.
FirstStepTime - StartTime = Parsing & DD Access Time (locks in DD can be shown here).
Thanks for providing the info
i have few more queries
I queried the dbc.DBQLogTbl and i could find data only for the queries which i had run today, where can i find the info for the queries which i had run say 1 week ago and whats the limit to the history maintained?
2.) For a certain insert stmtn i got the following stats
|2/22/2013 01:30:15.47||2/22/2013 01:49:56.54||2/22/2013 02:59:09.58||2/22/2013 02:16:30.98||46001.70||0.44||1179|
FirstRespTime - StartTime = 1hr 29 min (approx) so this the total query execution time does it include the time it was in delay state ?
What do columns DelayTime and CPU times infer and how is CPU time different from actual time
Hoping to hear from you people as it is a good learning experience
Rajeev, on Q1: dbc.DBQLogTbl normally logs sqls for all days (not just for the current day). Your DBA might have set a window of only 1 day for queries to be logged. Please check with your DBA
Thanks for getting back i will check on that
How about the second point can any one help me in understanding the numbers ?
Looking forward !
CPU time is the CPU seconds used by the query. So depending on the Capacity at which your TD system is set ; you will have some amount of CPU seconds in a day. So how much CPU seconds are used by the query are reflected by CPU time.
To find the total time the query spent on the system vs the total time the query was actuallly processed can be found via dbql:
( ( a.firstresptime - a.starttime ) HOUR( 4 ) TO SECOND( 2 ) ) AS ElapsedTime,
( ( a.firstresptime - a.firststeptime ) HOUR( 4 ) TO SECOND( 2 ) ) AS ExecutionTime,
ElapsedTime - ExecutionTime AS DelayTime,