Report CPU usage over 24 Hours

/* SQL to report CPU usage over 24 Hour period */
/* Make sure the collectintervals set correct */

select TheDate,
cast((cast((TheTime (format '99:99:99')) as char(8))) as time(0)) as TheTime,
(sum(CPUIdle) / TOTAL_CPU) * 100 AS CPUIdle,
(sum(CPUIoWait) / TOTAL_CPU) * 100 AS CPUIoWait,
(sum(CPUUServ) / TOTAL_CPU) * 100 AS CPUUServ,
(sum(CPUUExec) / TOTAL_CPU) * 100 AS CPUUExec,
SUM(CPUIdle + CPUIoWait + CPUUServ + CPUUExec) AS TOTAL_CPU,
(avg(CPUUServ + CPUUExec) / max(CPUUServ + CPUUExec)) * 100 AS CPUBusyParallelEff
from dbc.resusagespma
where thedate >= date - 1 and
collectintervals = '10'
group by 1,2
order by 1,2
;

SQL to check Cylinder Migration

/* SQL to check Cylinder Migration */
/* make sure the collectintervals value is set correct */

SELECT TheDate,
CAST((CAST((TheTime (FORMAT '99:99:99')) AS CHAR(8))) AS TIME(0)) AS TheTime,
SUM(FileCylAllocs),
SUM(FileCylMigrs),
SUM(FileMCylPacks),
SUM(FileCylDefrags),
(AVG(FileCylMigrs) / MAX(FileCylMigrs)) * 100 AS VprocCylMigrsParallelEff
FROM dbc.resusagesvpr
WHERE collectintervals = '2' AND
vprtype = 'AMP'
GROUP BY 1,2
ORDER BY 1,2
;

TPERF per user

Below script helps to check the amount of TPERF being used by a user. The eg. below uses 246 as the system TPERF, you have to change this to reflect your systems value.

SELECT dlt.UserName

,dlt.LogDate

/* Identify total CPU time used by queries executed by user for logdate */

,SUM(dlt.AMPCPUTime+ dlt.ParserCPUTime) AS Sum_CPUTime

/* Identify impact CPU time (ie to account for skew imbalance) used by queries executed by user for logdate */

,SUM((dlt.MaxAMPCPUTime*(HASHAMP()+1))+ dlt.ParserCPUTime) AS Sum_ImpactCPUTime

/* Identify total TPerfhours used by queries executed by user for logdate where system TPerf is 246 in this eg. */

,(Sum_ImpactCPUTime * 246.00) / (64.00 * 3600.00) AS Sum_ImpactTPerfHour

/* Remove 10% of avail TPerf for OS overhead */

,246.00 * 24 * 0.90 AS Sum_AvailTPerfHour


FROM dbqlogtbl_hst dlt


WHERE dlt.LogDate > CURRENT_DATE -95

AND dlt.UserName LIKE ANY ( 'UserName' )

GROUP BY 1,2

;