Skip to content

Instantly share code, notes, and snippets.

@mickey
Created Mar 1, 2018
Embed
What would you like to do?
SELECT w.service_class, CASE w.service_class
WHEN 6 THEN 'etl'
WHEN 7 THEN 'data'
WHEN 8 THEN 'viz'
WHEN 9 THEN 'default'
END,
e.exec_50th, e.exec_75th, e.exec_95th, e.exec_99th,
q.queue_50th, q.queue_75th, q.queue_95th, q.queue_99th,
m.mem_50th, m.mem_75th, m.mem_95th, m.mem_99th
FROM stl_wlm_query w
JOIN (
SELECT w.service_class as class,
CEIL((PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_exec_time) / 1000000))::bigint AS exec_50th,
CEIL((PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_exec_time) / 1000000))::bigint AS exec_75th,
CEIL((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_exec_time) / 1000000))::bigint AS exec_95th,
CEIL((PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total_exec_time) / 1000000))::bigint AS exec_99th
FROM stl_wlm_query w
LEFT JOIN stl_query rq ON rq.query = w.query AND rq.userid = w.userid
WHERE w.queue_start_time >= date_add('day'::character varying::text, -7::bigint, 'now'::character varying::date::timestamp without time zone)
AND w.total_queue_time > 0
AND w.userid > 1
AND rq.starttime >= date_add('day'::character varying::text, -7::bigint, 'now'::character varying::date::timestamp without time zone)
GROUP BY w.service_class
) e ON e.class=w.service_class
JOIN (
SELECT w.service_class as class,
CEIL((PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_queue_time) / 1000000))::bigint AS queue_50th,
CEIL((PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_queue_time) / 1000000))::bigint AS queue_75th,
CEIL((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_queue_time) / 1000000))::bigint AS queue_95th,
CEIL((PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total_queue_time) / 1000000))::bigint AS queue_99th
FROM stl_wlm_query w
LEFT JOIN stl_query rq ON rq.query = w.query AND rq.userid = w.userid
WHERE w.queue_start_time >= date_add('day'::character varying::text, -7::bigint, 'now'::character varying::date::timestamp without time zone)
AND w.total_queue_time > 0
AND w.userid > 1
AND rq.starttime >= date_add('day'::character varying::text, -7::bigint, 'now'::character varying::date::timestamp without time zone)
GROUP BY w.service_class
) q ON q.class=w.service_class
JOIN (
SELECT w.service_class as class,
CEIL((PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY est_peak_mem) / 1000000))::bigint AS mem_50th,
CEIL((PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY est_peak_mem) / 1000000))::bigint AS mem_75th,
CEIL((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY est_peak_mem) / 1000000))::bigint AS mem_95th,
CEIL((PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY est_peak_mem) / 1000000))::bigint AS mem_99th
FROM stl_wlm_query w
LEFT JOIN stl_query rq ON rq.query = w.query AND rq.userid = w.userid
WHERE w.queue_start_time >= date_add('day'::character varying::text, -7::bigint, 'now'::character varying::date::timestamp without time zone)
AND w.total_queue_time > 0
AND w.userid > 1
AND rq.starttime >= date_add('day'::character varying::text, -7::bigint, 'now'::character varying::date::timestamp without time zone)
GROUP BY w.service_class
) m ON m.class=w.service_class
GROUP BY w.service_class,
e.exec_50th, e.exec_75th, e.exec_95th, e.exec_99th,
q.queue_50th, q.queue_75th, q.queue_95th, q.queue_99th,
m.mem_50th, m.mem_75th, m.mem_95th, m.mem_99th;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment