Low Performing / High Impact / Long Running Queries (Teradata)

[Raj Aryan]

Hi Folks,

First off, let me start by saying that this question has probably been around for long. However, since I am new to Teradata I am requesting some help from you to achieve my goal.

That been said, I am trying to build a query that will show the top 10 worst performing /long-running queries for a given time-frame (usually in the past). I already did some reading online and came up with few metrics that might help identify such queries :-

SELECT    
UserName,
LogDate,
QueryID,
StartTime,
FirstRespTime,
((FirstRespTime - StartTime) HOUR(4) TO SECOND(2)) AS ElapsedTime,
((FirstRespTime - FirstStepTime) HOUR(4) TO SECOND(2)) AS EexecutionTime,
(FirstRespTime - StartTime) HOUR to SECOND(4) AS FirstRespElapsedTime,
ParserCPUTime,
AMPCPUTime,
AMPCPUTime + ParserCPUTime AS TotalCPUTime,
SpoolUsage/(1024*1024*1024) AS Spool_GB,
(MaxAMPCPUTime) * (HASHAMP() + 1) AS ImpactCPU
CAST(100-(nullifzero(AMPCPUTime/HASHAMP() + 1) * 100 /nullifzero(MaxAMPCPUTime)) AS INTEGER ) AS "CPUSkew%",
TotalIOCount,
MaxAMPIO * (HASHAMP() + 1) AS ImpactIO,
CAST(100-((TotalIOCount/HASHAMP() + 1) * 100 /nullifzero(MaxAMPIO)) AS INTEGER ) AS "IOSkew%",
QueryText
FROM pdcrinfo.<tables>
.....
.....
WHERE
logdate BETWEEN <input start-date> AND <input end-date>
AND
AMPCPUTime > 0

However, I am still struggling with the following outstanding questions -

[ul]
[li]Did I get the calculations shown above accurate ?[/li]
[/ul]
[ul]
[li]Does the above list of metrics suffice ? Or, are there any additional metric(s) that need to be included in the above list as well ?[/li]
[/ul]
[ul]
[li]I am bit confused with the ImpactCPU metric calculation logic. Apart from the one mentioned above, I found another logic as[/li]

ImpactCPU = (max_vproc_CPU * number of vprocs)

Please indicate the correct one.
[/ul]
[ul]
[li]Kindly let me know the history tables to use (in dbql_hst/pdcrinfo etc)[/li]
[/ul]
[ul]
[li]Finally, what logic should I apply to combine all these metrics into one to identify the top 10 ?[/li]
[/ul]

We are using Teradata v14.1

Any help is highly appreciated. Please let me know if additional information is necessary.

Cheers !