When im running a query,some tables/views are involved in this.I want to derive some view level information from it.Can any one suggest any idea.
I was wondering how to modify this query (posted above by @Random) to account for different generations of hardware in a coexistent environment ?
cast(cast(cast(TheTime as format '99:99:99.99')
as char(11)) as time(6)) as time_of_day,
((sum(CPUUExec)) / (sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle)) ) *100 as CPU_USR,
((sum(CPUUServ)) / (sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle)) ) *100 as CPU_SYS,
((sum(CPUIoWait)) / (sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle)) ) *100 as CPU_WIO,
((sum(CPUIdle)) / (sum(CPUIoWait+CPUUExec+CPUUServ+CPUIdle)) ) *100 as CPU_IDLE
where TheDate > (CURRENT_DATE - interval '1' DAY)
group by thedate,thetime
order by thedate,thetime;
SELECT thedate , timetime, (CPUUEXEC / NULLIFZERO(NCPUS)) CPUOPUSER
FROM dbc.resusagespma WHERE TheDate = CURRENT_DATE ORDER BY thedate , thetime DESC ;
I am using DBQLogTbl SUM ( AMPCPUTime ) per hour in a specific date. The maximum value is 174550.98.
We have one node and 9 CPUs. The total available cpu seconds in one hour is 60*60*9 = 32400 seconds
If DBQLogTbl AMPCPUTime is in seconds, this is my understanding, then this exceeds the total available cpu seconds.
If DBQLogTbl AMPCPUTime is in 100 milliseconds then max is 17455 seconds then the ratio to total seconds is 1.8 -- The system CPU ( sar 5 10 ) was showing 0% idle
Can someone advice please?
This is used to calculate the total available cpu secs on the system.
there could be queries that run more than one hour, or at least span two hours. If AMPCpuTime is summed up to "per hour", which is probably a query with a GROUP BY on a timestamp (starttime, firstresptime, etc), then those queries are charged to a particular hour, rather than splitting among hours.
That could be a reason why a DBQLogTbl SUM (AMPCpuTime) per hour could show high numbers.