(12cR1)
--セッションID確認
select sys_context('userenv','sid') from dual;
--待機イベント確認
select
sid
,event
,total_waits
,time_waited_micro
from v$session_event
where sid = 40
order by event;
select
sample_time
,session_id
,sql_id
,event
,session_state
,time_waited
from v$active_session_history
where session_id= 40
order by sample_time;
(5.6)
--パフォーマンススキーマセットアップテーブル
select * from performance_schema.setup_actors;
select * from performance_schema.setup_consumers;
select * from performance_schema.setup_instruments;
select * from performance_schema.setup_objects;
select * from performance_schema.setup_timers;
--待機イベント取得の有効化
update performance_schema.setup_consumers set enabled = 'YES'
where name like '%waits%';
--セッションID確認
show processlist\G
select * from performance_schema.threads\G
--待機イベント確認
select * from performance_schema.events_waits_current
where thread_id = 32\G
select * from performance_schema.events_waits_history
where thread_id = 32\G
select * from performance_schema.events_waits_history_long
where thread_id = 32\G
select event_name,object_name,count(*)
from performance_schema.events_waits_current
where thread_id = 32
group by event_name,object_name
order by event_name,object_name
;
select event_name,object_name,count(*)
from performance_schema.events_waits_history
where thread_id = 32
group by event_name,object_name
order by event_name,object_name
;
select event_name,object_name,count(*)
from performance_schema.events_waits_history_long
where thread_id = 32
group by event_name,object_name
order by event_name,object_name
;
(9.6)
--セッションID確認
select pg_backend_pid();
--待機イベント確認
select
datid
,datname
,pid
,usesysid
,usename
,application_name
,client_addr
,client_hostname
,client_port
,backend_start
,xact_start
,query_start
,state_change
,wait_event_type
,wait_event
,state
,backend_xid
,backend_xmin
,query
from pg_stat_activity
where pid = 2154;
(2016)
--セッションID確認
select @@spid;
--待機イベント確認
select
session_id
,wait_type
,waiting_tasks_count
,wait_time_ms
,max_wait_time_ms
,signal_wait_time_ms
from sys.dm_exec_session_wait_stats
where session_id = 55
;