Example: Performance Anomaly Detection - Teradata VantageCloud Lake

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 those rows 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);