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
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.
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;

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
?????

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.