×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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 :-

CODE --> SQL

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 -

  • Did I get the calculations shown above accurate ?
  • 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 ?
  • I am bit confused with the ImpactCPU metric calculation logic. Apart from the one mentioned above, I found another logic as

  • CODE -->

    ImpactCPU = (max_vproc_CPU * number of vprocs) 

    Please indicate the correct one.
  • Kindly let me know the history tables to use (in dbql_hst/pdcrinfo etc)
  • Finally, what logic should I apply to combine all these metrics into one to identify the top 10 ?
We are using Teradata v14.1

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

Cheers !

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close