Can anyone format the DelayTime from DBC.QryLogTDWMV?

Database
Enthusiast

Can anyone format the DelayTime from DBC.QryLogTDWMV?

Anyone help in converting the DelayTime into hours:minutes:seconds where hours could be greater than 24?

I cant seem to get my head around this "Teradata" way of recording times!

Thanks in advance
6 REPLIES
Junior Contributor

Re: Can anyone format the DelayTime from DBC.QryLogTDWMV?

The dataype of DelayTime is an integer, but no time.

If you want to convert it to time:
DelayTime * INTERVAL '0000:00:01' hour to second

Dieter
Enthusiast

Re: Can anyone format the DelayTime from DBC.QryLogTDWMV?

@Dieter, Thanks for prompt response.

Putting into a query and running

SELECT Starttime,Firstresptime,DelayTime ,
DelayTime * INTERVAL '0000:00:01' hour to second
FROM DBC.DBQLogtbl
where StartTime > timestamp '2009-07-07 00:00:00'
and WDID=131
and Delaytime > 0
order by starttime

I get some odd results

2009-07-07 09:00:10.03 2009-07-07 09:09:11.71 50129 13:55:29
2009-07-07 09:38:23.21 2009-07-07 09:50:51.20 60240 16:44:00
2009-07-07 15:59:21.61 2009-07-07 16:06:53.02 44621 12:23:41
2009-07-07 16:16:51.18 2009-07-07 17:22:48.43 59433 16:30:33
2009-07-08 08:20:02.58 2009-07-08 09:42:18.45 13261 3:41:01
2009-07-08 08:30:53.64 2009-07-08 10:29:09.53 13366 3:42:46
2009-07-08 08:31:28.17 2009-07-08 11:16:27.44 133538 37:05:38
2009-07-08 08:43:49.39 2009-07-08 12:01:41.84 672438 186:47:18
2009-07-08 09:01:14.54 2009-07-08 12:07:42.59 688769 191:19:29
2009-07-08 10:04:08.17 2009-07-08 12:11:22.75 724846 201:20:46
2009-07-08 10:12:15.98 2009-07-08 12:16:14.44 714422 198:27:02

I thought 50129 was 50 minutes 12.9 seconds (I think, having viewed through viewpoint

Richard
Junior Contributor

Re: Can anyone format the DelayTime from DBC.QryLogTDWMV?

Hi Richard,
up to 72/201 hours of delay? This is very strange, almost unbelievable.

Are you on TD12? There's a Tech Alert NTA 2025, according to that DelayTime is in hundreths of a second :-)

Adjusting the calculation based on that cuts 50129 down to 8:21.29, this is much better now.
The max of your example date is still 2 hours, but not 200 anymore.

So better use that new calculation (until it's fixed):
DelayTime / 100.00 * INTERVAL '0000:00:01.00' HOUR TO SECOND

Dieter
Enthusiast

Re: Can anyone format the DelayTime from DBC.QryLogTDWMV?

Dieter,

I used viewpoint to view transactions in the Delay queue, I could rewind and see the query above being submitted 50 minutes 12 seconds before. I seem to have come to the conclusion that the Delaytime column is in essense the same as the "TheTime" columns in the ResUsage tables, but with tenths of seconds added as well.

I have run this query and got the following results

SELECT Starttime,Firstresptime,Delaytime,Delaytime/10 as Time02,

((Delaytime / 100000) * 3600 +
(Delaytime / 1000 MOD 1000) * 60 +
(delaytime MOD 1000)) as seconds,

cast(cast(cast(DelayTime/10 as format '999:99:99')as char(9)) as time(6)),
DelayTime / 100.00 * INTERVAL '0000:00:01.00' HOUR TO SECOND as Dieter
FROM DBC.DBQLogtbl
where StartTime > timestamp '2009-07-07 00:00:00'
and WDID=131
and delaytime>0

2009-07-07 09:00:10.03 2009-07-07 09:09:11.71 50129 5012 3129 00:50:12 0:08:21.29
2009-07-07 09:38:23.21 2009-07-07 09:50:51.20 60240 6024 3840 00:60:24 0:10:02.40
2009-07-07 15:59:21.61 2009-07-07 16:06:53.02 44621 4462 3261 00:44:62 0:07:26.21
2009-07-07 16:16:51.18 2009-07-07 17:22:48.43 59433 5943 3973 00:59:43 0:09:54.33
2009-07-08 08:20:02.58 2009-07-08 09:42:18.45 13261 1326 1041 00:13:26 0:02:12.61
2009-07-08 08:30:53.64 2009-07-08 10:29:09.53 13366 1336 1146 00:13:36 0:02:13.66
2009-07-08 08:31:28.17 2009-07-08 11:16:27.44 133538 13353 12118 01:33:53 0:22:15.38
2009-07-08 08:43:49.39 2009-07-08 12:01:41.84 672438 67243 62358 06:72:43 1:52:04.38
2009-07-08 09:01:14.54 2009-07-08 12:07:42.59 688769 68876 63649 06:88:76 1:54:47.69
2009-07-08 10:04:08.17 2009-07-08 12:11:22.75 724846 72484 69486 07:24:84 2:00:48.46
2009-07-08 10:12:15.98 2009-07-08 12:16:14.44 714422 71442 68462 07:14:42 1:59:04.22
2009-07-08 10:32:53.30 2009-07-08 12:18:26.12 620030 62003 58830 06:20:03 1:43:20.30
2009-07-08 10:46:25.61 2009-07-08 12:33:08.48 551712 55171 51772 05:51:71 1:31:57.12

The key seems to be dividing Delaytime by 10 (I wonder if this is the NTA you referred to ?)then using either the CAST(CAST(CAST ... to get a time value out, or doing the arithmatic to get the number of seconds out (this turned out what I needed anyway for downstream processing)

Now a very big assumption has been made by myself that Viewpoint actually understands the DelayTime field, if it uses it.

I still would like to know though: Why if you store a delay time as an integer(40) would you store the time in this way? Why not store milliseconds, or CentiSeconds, or even seconds?

Could anyone from Teradata comment?

Regards

Random_thought
Junior Contributor

Re: Can anyone format the DelayTime from DBC.QryLogTDWMV?

Hi Richard,
your calculation can't be the correct one.

Events in chronological order:
1. query arrives at the PE, optimization starts = StartTime
2. query actually starts = FirstStepTime
3. query is finished, answer set is fully created, but not yet sent back to client = FirstRespTime

Now you can calculate:
FirstRespTime - StartTime = overall run time = ElapsedTime in dbc.qrylog
FirstStepTime - StartTime = Time needed to optimize the query plus time query was delayed by TDWM
FirstStepTime - StartTime - DelayTime = Time needed to optimize the query

Thus DelayTime can't be larger than ElapsedTime as your calculation suggests.

In most cases my calculation should be up to a few seconds less than (FirstStepTime - StartTime), because plans are created quite fast (unless there are some data dictionary locks).

You should check with Teradata support, if Viewpoint calculates the right delay :-)

Dieter
Enthusiast

Re: Can anyone format the DelayTime from DBC.QryLogTDWMV?

Dieter,

I think you are right.

The manual certainly says that

StartTime The timestamp when the query is submitted.
FirstStepTime The timestamp when the first step is dispatched.
FirstRespTime The timestamp when the first response packet is sent to host.
DelayTime The time a query was delayed by Teradata DWM.

Looking at our viewpoint server I can see these figures do sort of match up, but not for every instance, which I think is what has thrown me, I'll do some more investigation.

many thanks for your assistance Dieter

Regards
Random.