Calculating the actual query run time

Database
Enthusiast

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

11 REPLIES
Enthusiast

Re: Calculating the actual query run time

Hi ,

Can any one help me with the above question ?

Regards

R.Rajeev

Junior Supporter

Re: Calculating the actual query run time

Rajeev:

FirstRespTime - StartTime = Total SQL Execution Time.

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

HTH.

Cheers.

Carlos.

Enthusiast

Re: Calculating the actual query run time

Enthusiast

Re: Calculating the actual query run time

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

Enthusiast

Re: Calculating the actual query run time

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

Enthusiast

Re: Calculating the actual query run time

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

Junior Supporter

Re: Calculating the actual query run time

Rajeev:

DelayTime = The time a query was delayed by Teradata DWM.

Cheers.

Carlos.

Enthusiast

Re: Calculating the actual query run time

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

Enthusiast

Re: Calculating the actual query run 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,

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