All Forums Database
15 Feb 2013
Calculating the actual query run time

Hi,
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 ?
Please advice
 
Regards
Rajeev
 
 
 
 

18 Feb 2013

Hi ,
Can any one help me with the above question ?
 
Regards
R.Rajeev
 

CarlosAL 512 posts Joined 04/08
18 Feb 2013

Rajeev:
 
FirstRespTime - StartTime = Total SQL Execution Time.

FirstStepTime - StartTime = Parsing & DD Access Time (locks in DD can be shown here).
HTH.
Cheers.
Carlos.

22 Feb 2013

Hi Carlos/Sachin
Thanks for providing the info
i have few more queries
1.)
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
 

StartTime

FirstStepTime

FirstRespTime

LastStateChange

AMPCPUTime

ParserCPUTime

DelayTime

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

Now
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
Thanks
R.Rajeev
 
 

24 Feb 2013

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

24 Feb 2013

Hi Sachin
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  !
Regards
R.Rajeev

CarlosAL 512 posts Joined 04/08
25 Feb 2013

Rajeev:
 
DelayTime = The time a query was delayed by Teradata DWM.
 
Cheers.
Carlos.

TD_Raj 50 posts Joined 05/10
07 Aug 2013

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

usmans 39 posts Joined 01/13
15 Sep 2013

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,

The difference between the above 2 values will give the time the query was in Delay state:

ElapsedTime - ExecutionTime AS DelayTime,

The same value for Delay time is found in DBQL in column "WDDelayTime". 

You can confirm this value by checking it with the value you have calculated as DelayTime.

 

In TD 13.10, the format for "WDDelayTime" is integer so you might have to do some calculations to convert all time to seconds to confirm the values

--Regards
Usmans

mullasci 9 posts Joined 06/09
16 Jun 2015

In my opinion it would be nice to have back the total non-idle time in DBQL or a way to calculate it: going to the applications' logs is really unfeasible in most cases if not always.
I'm sure that it would be possible to get a reasonable estimate from the viewpoint database assuming a high-enough frequency of polling: anyone can point me to the right datastructure to interrogate?
 
Thanks
Marco

You must sign in to leave a comment.