Archives of the TeradataForum

Message Posted: Fri, 09 Apr 2010 @ 08:54:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi guys,

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.

Regards

David Clough
Database Developer
Database Design Group

	<Prev	Next>		<<First	<Prev	Next>	Last>>