查看一段时间内的活动会话的TOP N等待事件
v$active_session_history视图挖掘:
rac环境查询两个实例
set linesize 260 pagesize 10000
col INST_ID for 9
col sample_time for a30
col sample_id for 9999999999
col event for a40
col rn for 99
with ash_tab as (select *
from gv$active_session_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss'))
select *
from (select INST_ID,
sample_time,
sample_id,
nvl(event,'on cpu') event,
count(*) cnt,
ROW_NUMBER() OVER(PARTITION BY INST_ID,sample_time ORDER BY count(*) desc) rn
from ash_tab
group by INST_ID, sample_time,sample_id,nvl(event,'on cpu')
)
where rn < 10
and cnt>&cnt
order by sample_time asc,rn asc;
rac环境查询一个实例
set linesize 260 pagesize 10000
col INST_ID for 9
col sample_time for a30
col sample_id for 9999999999
col event for a40
col rn for 99
with ash_tab as (select *
from gv$active_session_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss'))
select *
from (select sample_time,
sample_id,
nvl(event,'on cpu') event,
count(*) cnt,
ROW_NUMBER() OVER(PARTITION BY sample_time ORDER BY count(*) desc) rn
from ash_tab
where inst_id in (select instance_number from v$instance)
group by sample_time,sample_id,nvl(event,'on cpu')
)
where rn < 10
and cnt>&cnt
order by sample_time asc,rn asc;
dba_hist_active_sess_history视图去挖掘信息：
rac环境查询两个实例
col INSTANCE_NUMBER for 9
col sample_time for a30
col sample_id for 9999999999
col event for a40
col rn for 99
with ash_tab as (select *
from dba_hist_active_sess_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss'))
select *
from (select INSTANCE_NUMBER,
sample_time,
sample_id,
nvl(event,'on cpu') event,
count(*) cnt,
ROW_NUMBER() OVER(PARTITION BY INSTANCE_NUMBER,sample_time ORDER BY count(*) desc) rn
from ash_tab
group by INSTANCE_NUMBER, sample_time,sample_id,nvl(event,'on cpu')
)
where rn < 10
and cnt>&cnt
order by sample_time asc,rn asc;
rac环境查询一个实例
col INSTANCE_NUMBER for 9
col sample_time for a30
col sample_id for 9999999999
col event for a40
col rn for 99
with ash_tab as (select *
from dba_hist_active_sess_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss'))
select *
from (select INSTANCE_NUMBER,
sample_time,
sample_id,
nvl(event,'on cpu') event,
count(*) cnt,
ROW_NUMBER() OVER(PARTITION BY INSTANCE_NUMBER,sample_time ORDER BY count(*) desc) rn
from ash_tab
where instance_number in (select instance_number from v$instance)
group by INSTANCE_NUMBER, sample_time,sample_id,nvl(event,'on cpu')
)
where rn < 10
and cnt>&cnt
order by sample_time asc,rn asc;
