Example: Investigating Why a Job Ran Slower Today - Teradata VantageCloud Lake

In this scenario, a FastLoad job submitted today took significantly longer than yesterday. You want to determine the root cause. Although the example is specific to FastLoad, you can use this general approach for other utilities.

1. Retrieve the rows for the jobs yesterday and today from DBQLUtilityTbl using the following criteria:
   • Job attributes that uniquely identify this FastLoad job.
     This criteria returns all instances of this FastLoad job. Possible job attributes include:

     • UtilityName = 'FASTLOAD'
     • Username
     • Query band
     • Utility request
   • Job end time: either today or yesterday.
2. Compare column values between the two rows to determine the possible causes. Suggestions include:
   1. Compare RowsInserted values. If the job today loaded significantly more rows, this could be the cause. If not, proceed to the next step.
   2. Compare DelayTime values. An increase indicates the job today was delayed longer by TASM.
   3. Compare the elapsed time of each phase (PhasexEndTime - PhasexStartTime) to identify the phase with a significant increase in elapsed time.
   4. If the phase with a significant increase in elapsed time is the Acquisition phase, check for an increase in MaxDataWaitTime, which may be caused by more load on the client machine or slower network response time.
   5. Compare the resource usages of the phase with a significant increase in elapsed time to find additional clues.
      For example:

      • An increase in PhasexMaxCPUTime may indicate data skew.
      • An increase in Phase1 messages (Phase1BlockCount) may indicate that a smaller message size was used.
3. Examine related query log data in DBQLogTbl. To select the desired data, use:
   • LSN to select all rows of a job.
   • PhasexStartTime and PhasexEndTime to select rows of a specific phase.