Worst Performing SQL (taking more than 30 minutes) in Greenplum in last 2 days

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;