What is method or strategy to calculate the CPU utilized in running a Query?

Database
Enthusiast

What is method or strategy to calculate the CPU utilized in running a Query?

Hi All,

Suppose, i am running a query on teradata database. I want to know how the various components interact in calculating CPU requested/utilized .

Could someone explain in detail?

Thanks in Advance!

Chakri
6 REPLIES 6
Enthusiast

Re: What is method or strategy to calculate the CPU utilized in running a Query?

You can query "dbc.DBQLogTbl" table to get CPU time (column name: totalcputime) taken by a query. This table also provides I/O count. You can limit on sessionid if you know. If you only know time range in which you ran query, then limit on collecttimestamp. Hope this answers your question.
Highlighted
Enthusiast

Re: What is method or strategy to calculate the CPU utilized in running a Query?

Step-1 Run the query in the database for which you need to calculate the CPU time elapsed, skew factor etc.
Step-2 Then run the below query to get the desired statistics.

SELECT /**tag 1**/ distinct
lg.querytext,
lg.starttime,
lg.lastresptime,
lg.COLLECTTIMESTAMP,
lg.UserID,
lg.SessionID,
lg.TotalIOCount,
lg.TotalCPUTime,
(lg.lastresptime - lg.starttime) second(4),
lg.SpoolUsage/(1024*1024*1024),
(lg.HotAMP1CPU)/nullifzero((lg.TotalCPUTime/HASHAMP())) as CPUSkew,
100-(nullifzero(lg.TotalCPUTime/300)/(lg.HotAMP1CPU)*HASHAMP()) "Skew Factor",
lg.UserName,
lg.DefaultDatabase,
lg.errorcode,
lg.ErrorText,
lg.delaytime
FROM dbc.DBQLogTbl AS lg
WHERE cast(starttime as date) = current_date and username='[YOUR USER NAME HERE]' and querytext like '%[PART OF YOUR QUERY HERE]%' order by starttime;
Not applicable

Re: What is method or strategy to calculate the CPU utilized in running a Query?

Can you please let me know how I can do this in V12.
lastresptime, HotAMP1CPU, TotalCPUTime are not availble in V12
Enthusiast

Re: What is method or strategy to calculate the CPU utilized in running a Query?

TotalCPUTime is broken down into AMPCPUTime and ParserCPUTime for TD12, if u want to report the total CPU Time then just add these two.

You don't have LastRespTime, If you want to compute the processing time of the query then go with:
FirstRespTime - StartTime.

Use MaxAMPCPUTime in place of HotAMP1CPU
Enthusiast

Re: What is method or strategy to calculate the CPU utilized in running a Query?

can you elaborate :
100-(nullifzero(lg.TotalCPUTime/300)/(lg.HotAMP1CPU) *HASHAMP()) "Skew Factor",

why divide 300
?????
Not applicable

Re: What is method or strategy to calculate the CPU utilized in running a Query?

Great information you've ever shared! I'm looking for this information.