故障发生时怎么样依靠等待时间排查

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 <to_date('2020-01-16 01:58:00', 'yyyy-mm-dd hh24:mi:ss')
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 v$session where event#='378' order by sql_id;
select 'alter system kill session '''|| sid ||','||serial# ||''' immediate;' from v$session where event#='378';
select 'ps -ef|grep '||to_char(spid)||'|grep LOCAL=NO|awk ''{print " -9 "\$2}''|xargs kill' kill_sh from v$process p,v$session s where s.paddr=p.addr and s.type='USER' and s.event#='378';