Total Duration - Time in state @ View Point: How to get these from DBQL

Database
Highlighted
Enthusiast

Total Duration - Time in state @ View Point: How to get these from DBQL

Hi All,

 

I am looking out for the help to calculate Total Duration and  Time in state (@ View Point )through dbql tables.

 

While monitoring we can point out certain sql with these parameters.But for batch queries for monthly audit these figures are very important. Through these reports it will be easy to find sqls continuously taking long time to complete

 

Is there any way I can get these from DBQL? 

Difference between StartTime and FirstStepTime is certainly not these. It does not cover the last packet recive time.

Cant use LastRespTime as it is NULL for my box (The timestamp of the last response. This field is NULL if you are not using DBQL CPU/IO Collection algorithm 3.)       

Can it be difference between LastStateChange and StartTime ( After completion, session will change the state from RESPONDING to COMPLETE)

       

6 REPLIES 6
Teradata Employee

Re: Total Duration - Time in state @ View Point: How to get these from DBQL

You probably want to start with:

  Select UserId, QuerId, FirstStepTime, FirstRespTime, cast(FirstRespTime-FirstStepTime as interval hour to second) as ELT from QryLogV


The FirstStepTime is when the work begins; FirstRespTime is when the final result set is queued in spool and ready to send to the user.  These two fields tell you how long it took to run the query, not counting the time it took to transmit results to the user.  You might also want to join this query to QryLogSQLV, QryLogStepsV and/or QryLogExplainV to learn what parts of long-running queries took the longest.

Teradata Employee

Re: Total Duration - Time in state @ View Point: How to get these from DBQL

Total Duration and Time In State are essentially "delta" figures between snapshots. LastStateChange is something entirely different (and if you look, it's generally well before StartTime - not of use here.)

StartTime = when the parser sees the request

FirstStepTime = when the AMP(s) start processing the first query step for the request

FirstRespTime = when the last AMP says the last query step is done

 

FirstStepTime - StartTime includes throttle DelayTime as well as time waiting for MLK steps ("First, we lock...") and internal delays such as acquiring a WORKNEW AWT.

FirstRespTime - FirstStepTime is the elapsed time during which the request executes query steps (potentially consuming CPU)

 

If you need total end-to-end request duration or specifically time in "Responding" state, then you can set DBQLLogLastResp to True in dbscontrol to log a dummy "RESP" step in DBQLStepTbl when Response mode ends, or (as you mention) use collection algorithm 3 so LastRespTime is populated in DBQLogTbl.

 

BTW - there is no "Complete" state, the session just goes Idle or logs off.

Enthusiast

Re: Total Duration - Time in state @ View Point: How to get these from DBQL

Thanks Fred.

 

LastStateChange will definitely be abort or session completion (IDEL or LOGOFF)

 

I will go with difference in LastStateChange and StartTime. This seems logical as this will the time through which resources are in use by the session. 

 

 

 

 

Teradata Employee

Re: Total Duration - Time in state @ View Point: How to get these from DBQL

Not LastStateChange. Use FirstRespTime - FirstStepTime.

Enthusiast

Re: Total Duration - Time in state @ View Point: How to get these from DBQL

Yes

 

LastStateChange is wrong. In many cases StartTime is greater than LastStateChange.

 

 

Teradata Employee

Re: Total Duration - Time in state @ View Point: How to get these from DBQL

LastStateChange refers to a TASM / TDWM system state, which isn't something directly related to the query itself.