mysql 等待事件查询_日常使用查看等待事件的查询sql

1.查某个时间段的等待事件总数

select trunc(sample_time, ‘mi’), count(1)

from gv$active_session_history

where sample_time >= to_date(‘2020-01-16 01:50:00’, ‘yyyy-mm-dd hh24:mi:ss’)

and sample_time < to_date(‘2020-01-16 01:59:00’, ‘yyyy-mm-dd hh24:mi:ss’)

and event is not null

group by trunc(sample_time, ‘mi’)

order by 1;

2.查看某个时间段的等待事件排名

select inst_id, event, count(1)

from gv$active_session_history

where sample_time >=to_date(‘2020-01-16 01:50:00’, ‘yyyy-mm-dd hh24:mi:ss’)

and sample_time

and event is not null

group by inst_id, event

order by 1, 3 desc;

3.根据等待事件查看进程信息

select username,program,sql_id,count(*) from v$session where event=’&’ group by username,program,sql_id order by 4 desc;

4.查看对应sql的历史执行计划

set line 200;

SET PAGESIZE 30

col begin_time format a20;

col username for a15

col PLAN_HASH_VALUE for 99999999999

col execs for 999999999999

col l_read_per for 99999999999

col phy_read_per for 999999999999

col ela_time_per for 99999999999

col cpu_per for 999999999999

– by sql_id

select a.snap_id,to_char(b.begin_interval_time,‘yyyy-mm-dd hh24:mi:ss’) begin_time,a.PLAN_HASH_VALUE,a.PARSING_SCHEMA_NAME username,a.EXECUTIONS_DELTA execs,

round((a.ELAPSED_TIME_DELTA/EXECUTIONS_DELTA)/1000,2) elatim_per_ms,

round(a.BUFFER_GETS_DELTA/a.EXECUTIONS_DELTA,2) l_read_per,round(a.PHYSICAL_READ_REQUESTS_DELTA/EXECUTIONS_DELTA,2) phy_read_per,

round((a.CPU_TIME_DELTA/a.EXECUTIONS_DELTA)/1000,2) cpu_per_ms

from dba_hist_sqlstat a ,DBA_HIST_SNAPSHOT b where a.instance_number=(select instance_number from v$instance)

and a.sql_id=’$2’

and a.snap_id=b.snap_id

and a.instance_number=b.instance_number

and a.executions_delta>0

order by 1;

5.查询当前等待事件排名

set linesize 150 pages 100

col event for a60

select event#,event,count(*) from v$session where status=‘ACTIVE’ and event not like ‘%message%’ group by event#, event order by 3;

6.根据等待事件号杀对应的会话(即根据5查出来的信息)

set line 200 pages 100

select sid,serial#,username,program,sql_id from vsession where event#='378' order by sql_id;

select 'alter system kill session '''|| sid ||','||serial# ||''' immediate;' from vsession where event#=‘378’;

select ‘ps -ef|grep ‘||to_char(spid)||’|grep LOCAL=NO|awk ‘’{print " -9 "$2}’’|xargs kill’ kill_sh from vprocessp,vprocess p,vprocessp,vsession s where s.paddr=p.addr and s.type=‘USER’ and s.event#=‘378’;