long-running-queries-redshift
SELECT
i.pid,
s.user_name,
i.query AS query_id,
substring(s.query_text,1,100) AS short_query,
s.elapsed_time/1000000 AS duration_sec
FROM stv_inflight i
JOIN sys_query_history s
ON i.query = s.query_id
ORDER BY duration_sec DESC;
==========================================
SELECT
i.pid,
s.user_name,
s.query_id,
substring(s.query_text,1,100) AS short_query,
s.elapsed_time/1000000 AS duration_sec
FROM stv_inflight i
JOIN sys_query_history s
ON i.query = s.query_id
WHERE s.status = 'running'
ORDER BY duration_sec DESC;
=============================================
SELECT
i.pid,
u.usename AS user_name,
s.query_id,
substring(s.query_text, 1, 100) AS short_query,
s.elapsed_time / 1000000 AS duration_sec
FROM stv_inflight i
JOIN sys_query_history s ON i.query = s.query_id
LEFT JOIN pg_user u ON s.user_id = u.usesysid
WHERE s.status = 'running'
AND s.elapsed_time / 1000000 > 300 -- Filter for queries running > 5 mins
ORDER BY duration_sec DESC;
===================================================
SELECT
i.pid,
u.usename AS user_name,
s.query_id,
substring(s.query_text, 1, 100) AS short_query,
datediff(second, i.starttime, getdate()) AS duration_sec
FROM stv_inflight i
JOIN sys_query_history s ON i.xid = s.transaction_id
LEFT JOIN pg_user u ON i.userid = u.usesysid
WHERE s.status = 'running'
AND datediff(second, i.starttime, getdate()) > 300
ORDER BY duration_sec DESC;
================================================
SELECT
i.pid,
u.usename AS user_name,
i.query AS query_id,
substring(s.query_text,1,100) AS short_query,
(s.elapsed_time/1000000) AS duration_sec
FROM stv_inflight i
JOIN sys_query_history s
ON i.query = s.query_id
LEFT JOIN pg_user u
ON s.user_id = u.usesysid
ORDER BY duration_sec DESC;
==================================================
SELECT
i.pid,
u.usename AS user_name,
i.query AS inflight_query_id,
s.query_id AS sys_query_id,
substring(i.text, 1, 80) AS inflight_text,
s.status AS sys_status,
datediff(second, i.starttime, getdate()) AS duration_sec
FROM stv_inflight i
LEFT JOIN sys_query_history s
ON i.xid = s.transaction_id
AND s.status = 'running'
LEFT JOIN pg_user u
ON i.userid = u.usesysid
ORDER BY duration_sec DESC;
Comments
Post a Comment