dba_hist_sess_history

1.dbw是否繁忙
select trunc(sample_time, 'mi'),count(*),instance_number
from dba_hist_active_sess_history
where  sample_time >=to_date('20200710-0950','yyyymmdd-hh24mi')
and    sample_time <=to_date('20200710-0955','yyyymmdd-hh24mi')
and  program like '%DBW%'
group by instance_number,trunc(sample_time, 'mi')
order by 1;

2、select program,count(*） from dba_hist_active_sess_history where program like '%DBW%' group by program ;

3、查询某段时间内的等待事件

select snap_id,instance_number,to_char(sample_time,'YYYY-MM-DD HH24:MI:SS'),count(*),event from
dba_hist_active_sess_history
where  sample_time >=to_date('2020-07-10 09:30:00','YYYY-MM-DD HH24:MI:SS')
and    sample_time <=to_date('2020-07-10 11:00:00','YYYY-MM-DD HH24:MI:SS')
group by snap_id,instance_number,to_char(sample_time,'YYYY-MM-DD HH24:MI:SS'),event
having count(*) > 30
order by count(*),event;

4、在这期间主要的等待事件为    
select instance_number,event,count(*) from
dba_hist_active_sess_history
where  sample_time >=to_date('20200507-0050','yyyymmdd-hh24mi')
and    sample_time <=to_date('20200507-0300','yyyymmdd-hh24mi')
and snap_id = 27148
group by instance_number,event order by 3 desc;

5、查看各个实例的总的等待会话数据

select inst_id,trunc(sample_time,'mi'),count(*) from gv$active_session_history
where sample_time >=to_date('2020-07-09 08:00:00', 'yyyy-mm-dd hh24:mi:ss')
and sample_time <to_date('2020-07-09 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
and event is not null
group by inst_id,,trunc(sample_time,'mi');

6、 查看单个实例总的等待会话数目

select envet,count(*)from gv$active_session_history
 where sample_time >=to_date('2020-07-09 08:00:00', 'yyyy-mm-dd hh24:mi:ss')
 and sample_time <to_date('2020-07-09 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
 and event is not null
 and inst_id=1
 group by event
 order by 2 desc;

7、进一步查询等待严重的sql语句是哪些：

select instance_number, USER_ID, SQL_ID, event,count(*) from  dba_hist_active_sess_history
where sample_time >=to_date('2020-07-09 08:00:00', 'yyyy-mm-dd hh24:mi:ss')
and sample_time <to_date('2020-07-09 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
--and snap_id in (27147,27148)
and instance_number = 2
and event = 'wait for a undo record'
group by instance_number, USER_ID, SQL_ID, event order by 5 desc;