We have migrated to new Google sites!
Post date: Oct 18, 2014 12:9:44 AM
select sh.ctime,query_text,username, db,rsqname,
avg(tfinish-tstart) as run_time,
min(to_char(sh.ctime, 'Month')) as "month",
min(to_char(sh.ctime, 'dd')) as "day",
min(to_char(sh.ctime, 'Day')) weekday,
min(to_char(sh.ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(100 - cpu_idle)::numeric,2) as avg_cpu_used,
round(min(100 - cpu_idle)::numeric,2) as min_cpu_used,
round(max(100 - cpu_idle)::numeric,2) as max_cpu_used,
round(avg(load2)::numeric,2) as avg_load2,
round(min(load2)::numeric,2) as min_load2,
round(max(load2)::numeric,2) as max_load2,
round(avg(disk_rb_rate)::numeric,2) avg_disk_rb_rate,
round(min(disk_rb_rate)::numeric,2) min_disk_rb_rate,
round(max(disk_rb_rate)::numeric,2) max_disk_rb_rate,
round(avg(disk_wb_rate)::numeric,2) avg_disk_wb_rate,
round(min(disk_wb_rate)::numeric,2) min_disk_wb_rate,
round(max(disk_wb_rate)::numeric,2) max_disk_wb_rate,
round(avg(net_rb_rate)::numeric,2) avg_net_rb_rate,
round(min(net_rb_rate)::numeric,2) min_net_rb_rate,
round(max(net_rb_rate)::numeric,2) max_net_rb_rate,
round(avg(net_wb_rate)::numeric,2) avg_net_wb_rate,
round(min(net_wb_rate)::numeric,2) min_net_wb_rate,
round(max(net_wb_rate)::numeric,2) max_net_wb_rate,
round(avg(mem_actual_used)/power(1024,3)::numeric,2) avg_mem_actual_used_gb,
round(min(mem_actual_used)/power(1024,3)::numeric,2) min_mem_actual_used_gb,
round(max(mem_actual_used)/power(1024,3)::numeric,2) max_mem_actual_used_gb,
round(avg(swap_used)::numeric,2) avg_swap_used,
round(min(swap_used)::numeric,2) min_swap_used,
round(max(swap_used)::numeric,2) max_swap_used
from system_history sh,queries_history qh
where sh.ctime between date_trunc('day',localtimestamp- interval '2 days') and date_trunc('day',localtimestamp)
and sh.ctime=qh.ctime
--and to_char(sh.ctime, 'dd')=28 -- If you want for a parerticular day
--and username not in ('gpmon')
and db not in ('gpperfmon')
and date_part('hour',tfinish - tstart)*60+ date_part('minute',tfinish - tstart)> 30
group by sh.ctime,query_text,username, db,rsqname;