[PPAS / Postgresql] SQL 경합 현상 분석 쿼리

- SQL 경합 현상 분석 쿼리

 

SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, ka.current_query AS blocking_statement,

TO_CHAR(NOW() - ka.query_start, 'HH24:MI:SS') AS blocking_duration,

kl.pid AS blocking_pid, ka.usename AS blocking_user,

a.current_query AS blocked_statement,

TO_CHAR(NOW() - a.query_start, 'HH24:MI:SS') AS blocked_duration

FROM pg_catalog.pg_locks bl

JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.procpid

JOIN pg_catalog.pg_locks kl

JOIN pg_catalog.pg_stat_activity ka

ON kl.pid = ka.procpid

ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid

WHERE bl.granted = 't';

 

-------------------------------------------------------------------------------

 

SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, ka.query AS blocking_statement,

TO_CHAR(NOW() - ka.query_start, 'HH24:MI:SS') AS blocking_duration,

kl.pid AS blocking_pid, ka.usename AS blocking_user,

a.query AS blocked_statement,

TO_CHAR(NOW() - a.query_start, 'HH24:MI:SS') AS blocked_duration

FROM pg_catalog.pg_locks bl

JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid

JOIN pg_catalog.pg_locks kl

JOIN pg_catalog.pg_stat_activity ka

ON kl.pid = ka.pid

ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid

WHERE bl.granted = 'f'