16.10 - Examples of Using the DBQL Utility Job Log Table - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

You can use DBQLUtilityLogTbl to diagnose utility performance issues and do capacity planning. The following are just some examples of how to use the table.

Investigating Why a Job Ran Slower Today

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 various 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 might 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 particular job.
    • PhasexStartTime and PhasexEndTime to select rows of a specific phase.

Performance Anomaly Detection

In this scenario, you want to detect job instances whose elapsed time is 50% longer than the last 30-day average for the same job. This approach can be used to detect anomalies for other metrics, such as CPU time or I/Os.

  1. Select rows from DBQLUtilityTbl for jobs ending within the last 30 days and store them in a temporary table called Last30dayJobInstances.
    CREATE VOLATILE TABLE Last30dayJobInstances AS
      (SELECT U.*,
    SUBSTRING(UtilityRequest FROM 1 FOR 100) AS UtilReqShort
    FROM DBC.DBQLUtilityTbl U
    WHERE CAST(JobEndTime AS date) >= DATE-30)
    WITH DATA
    PRIMARY INDEX (UtilityName, UserName)
    ON COMMIT PRESERVE ROWS;
  2. Use the Last30dayJobInstances table to calculate the last 30-day average elapsed time of each job, grouped by attributes such as utility name, user name, and utility request. Store the results in another temporary table called Last30dayAverage. Each row contains the average elapsed time and job attributes.
    CREATE VOLATILE TABLE Last30dayAverage AS
      (SELECT AVERAGE((JobEndTime - JobStartTime) HOUR TO SECOND)
                 AS AvgJobElapsedTime,
              UtilityName, UserName,
              SUBSTRING(UtilityRequest FROM 1 FOR 100) AS UtilReqShort
              FROM Last30dayJobInstances J
       GROUP BY UtilityName, UserName,
                SUBSTRING(UtilityRequest FROM 1 FOR 100))
    WITH DATA
    PRIMARY INDEX (UtilityName, UserName)
    ON COMMIT PRESERVE ROWS;
  3. Select job instances whose elapsed time is more than 50% of the average using the Last30dayJobInstances and Last30dayAverage tables.
    SELECT I.*
    FROM Last30dayJobInstances I,
         Last30dayAverage A
    WHERE I.UtilityName = A.UtilityName
      AND I.UserName = A.UserName
      AND I.UtilReqShort = A.UtilReqShort
      AND (
           (EXTRACT(HOUR FROM ((I.JobEndTime - I.JobStartTime) HOUR TO SECOND)) * 3600) +
           (EXTRACT(MINUTE FROM ((I.JobEndTime - I.JobStartTime) HOUR TO SECOND)) * 60) +
           (EXTRACT(SECOND FROM ((I.JobEndTime - I.JobStartTime) HOUR TO SECOND)))
          ) > (
               ((EXTRACT(HOUR FROM A.AvgJobElapsedTime) * 3600) +
                (EXTRACT(MINUTE FROM A.AvgJobElapsedTime) * 60) +
                (EXTRACT(SECOND FROM A.AvgJobElapsedTime))
               ) * 1.5);

Capacity Planning

In this scenario, you calculate these monthly metrics for each utility for the previous 12 months:

  • Number of job instances
  • Average job elapsed time
  • Total processing volume (byte counts and row counts)
  • Average processing volume
  • Average throughput (rows per second and bytes per second)
    SELECT TRUNC(CAST(JobEndTime AS DATE), 'RM') JobMonth, UtilityName,
        COUNT(*) AS NumJobs,
        AVG(
            (EXTRACT(HOUR FROM ((JobEndTime - JobStartTime) HOUR TO SECOND)) * 3600) +
            (EXTRACT(MINUTE FROM ((JobEndTime - JobStartTime) HOUR TO SECOND)) * 60) +
            (EXTRACT(SECOND FROM ((JobEndTime - JobStartTime) HOUR TO SECOND)))
           ) AS AvgJobTime,
        SUM(RowsInserted), AVG(RowsInserted),
        SUM(RowsUpdated ), AVG(RowsUpdated ),
        SUM(RowsDeleted ), AVG(RowsDeleted ),
        SUM(RowsExported), AVG(RowsExported),
        SUM(Phase1ByteCount), AVG(Phase1ByteCount),
        SUM(Phase2ByteCount), AVG(Phase2ByteCount),
        AVG(RowsInserted) / AvgJobTime AS InsRowsPerSec,
        AVG(RowsUpdated ) / AvgJobTime AS UpdRowsPerSec,
        AVG(RowsDeleted ) / AvgJobTime AS DelRowsPerSec,
        AVG(RowsExported) / AvgJobTime AS ExpRowsPerSec,
        AVG(Phase1ByteCount) / AvgJobTime AS AvgPhase1BytesPerSec,
        AVG(Phase2ByteCount) / AvgJobTime AS AvgPhase2BytesPerSec
    FROM   (SELECT *
            FROM DBC.DBQLUtilityTbl U
            WHERE CAST(JobEndTime AS DATE) >= ADD_MONTHS(TRUNC(CURRENT_DATE, 'RM'), -12)
              AND CAST(JobEndTime AS DATE) < TRUNC(CURRENT_DATE, 'RM') ) AS OneYearLog
    GROUP BY JobMonth, UtilityName
    ORDER BY JobMonth, UtilityName;