All Forums Database
chakdom 8 posts Joined 07/08
19 Jan 2010
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

ravi.videla 21 posts Joined 09/09
27 Jan 2010

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.

UpaMishra 35 posts Joined 01/08
28 Jan 2010

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**/ distinctlg.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.delaytimeFROM dbc.DBQLogTbl AS lgWHERE cast(starttime as date) = current_date and username='[YOUR USER NAME HERE]' and querytext like '%[PART OF YOUR QUERY HERE]%' order by starttime;

maruthip 1 post Joined 03/10
15 Mar 2010

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

monisiqbal 119 posts Joined 07/09
16 Mar 2010

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

emilwu 72 posts Joined 12/07
01 Oct 2010

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

why divide 300

julianmarble 1 post Joined 10/10
02 Oct 2010

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

You must sign in to leave a comment.