alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select
SID
,SERIAL#
,STATUS
,SQL_ID
,SQL_EXEC_START
,PREV_SQL_ID
,PREV_EXEC_START
,LOGON_TIME
from v$session
order by LOGON_TIME;
select * from v$session_longops;
select * from information_schema.INNODB_TRX order by trx_id\G
SELECT TRX_ID, TRX_MYSQL_THREAD_ID FROM information_schema.INNODB_TRX
WHERE TIMEDIFF(NOW(),TRX_STARTED) > '00:30:00';
select
trx.trx_started,
th.processlist_host,
th.processlist_user,
e.sql_text
from information_schema.innodb_trx trx
inner join performance_schema.threads th
on trx.trx_mysql_thread_id = th.processlist_id
inner join performance_schema.events_statements_history e
on th.thread_id = e.thread_id
;
https://lets.postgresql.jp/documents/technical/statistics/2
(9.4)
SELECT pid, waiting,state, (current_timestamp - xact_start)::interval(3)
AS duration, query FROM pg_stat_activity
WHERE pid <> pg_backend_pid();
(9.6)
SELECT pid, wait_event,wait_event_type,state, (current_timestamp - xact_start)::interval(3)
AS duration, query FROM pg_stat_activity
WHERE pid <> pg_backend_pid();
select pid,query,xact_start,state from pg_stat_activity;
select * from sys.dm_tran_active_transactions;
go
