Example: Capacity Planning - Teradata Vantage

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;