Archives of the TeradataForum
Message Posted: Fri, 09 Apr 2010 @ 08:54:40 GMT
I've got some performance issues from some Tactical Queries and I'd very much like to get to the bottom of the problem.
Thing is, these performance issues are intermittent, sometimes response times are good sometimes they're not.
We've put the Tactical User is a preferential allocation group, so I don't think there's a problem there.
What I do suspect, however, is that this User is waiting on Amp Worker tasks, but that's just a suspicion (not that I'd know how to check this !)
Now to my question .... I'm using Query Log to provide a bit of clarity on the problem, but do you think this is the right approach ?
What I'm doing is this : subtract the FirstStepTime from the StartTIme to give an amount of time that I'm interpreting as a 'wait time'. Is this a correct interpretation ?
So, specifically, I'm doing this :
select COUNT(*) AS QUERY_CT, AVG( (STARTTIME - FIRSTSTEPTIME) DAY(4) TO SECOND ) AS WAIT_TM, CAST(COLLECTTIMESTAMP AS DATE) AS SUBMIT_DT from DBC.QRYLOG WHERE USERNAME = 'PRODC8_CSSR1' GROUP BY SUBMIT_DT ORDER BY SUBMIT_DT DESC
This gives :
QUERY_CT WAIT_TM SUBMIT_DT 161 -0 00:00:01.394037 08/04/2010 155 -0 00:00:01.429677 07/04/2010 318 -0 00:00:12.191572 06/04/2010 2 -0 00:00:01.475000 05/04/2010 6 -0 00:00:18.523333 02/04/2010 2676 -0 00:00:01.925108 01/04/2010 308 -0 00:00:01.953474 31/03/2010
Clearly, something's going on here.
In terms of the Tactical queries themselves, I've looked at the Explain Plan and they're good; they use the Aggregate Join Indexes that I've set up, so that all looks good.
Am I looking in the right area, or are there other things I should also be looking for ?
Thank you very much for any information that you might have.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|