利用 ASH 视图监控数据库性能
当前会话监控语句：
select inst_id,sid,username,machine,program,module,action,sql_id,event,blocking_session,logon_time,prev_exec_start,client_info
from gv$session where status ='ACTIVE' and type <> 'BACKGROUND' order by inst_id,sid;
历史活动会话检查：监控检查最近10分钟的数据库等待事件的情况
select event,count(1)
from gv$active_session_history a
where a.sample_time > sysdate - 1/144
group by event
order by count(1) desc;
select event,sql_id,count(1)
from gv$active_session_history a
where a.sample_time > sysdate - 1/144
group by event,sql_id
order by count(1) desc;
最近一段时间每一分钟的ASH中出现的行数，记录数越多，说明数据库越活跃，负载越大，可能等待越多
select trunc(sample_time,'mi'),count(1)
from gv$active_session_history
group by trunc(sample_time,'mi')
order by 1;
select trunc(sample_time,'mi'),event,count(1)
from gv$active_session_history
where
sample_time >=to_date('20181016 17:51:00','yyyymmdd hh24:mi:ss')
and
sample_time <=to_date('20181016 17:54:00','yyyymmdd hh24:mi:ss')
group by
trunc(sample_time,'mi'),event
order by 1;
利用 ASH 视图分析数据库阻塞问题
1 . 分析找出会话阻塞出现的时间段
select trunc(sample_time,'mi'),count(1) from dba_hist_active_sess_histroy
where sample_time > to_date('20180626 01:25:00','yyyymmdd hh24:mi:ss')
and sample_time < to_date('20180626 01:40:00','yyyymmdd hh24:mi:ss')
and event is not null
group by trunc(sample_time,'mi')
having count(1)>2
order by 1;
2 . 分析会话阻塞的等待事件，进一步缩小时间范围
关注新出现的等待事件和等待事件次数变多的
select trunc(sample_time,'mi'),event,count(1) from dba_hist_active_sess_histroy
where sample_time > to_date('20180626 01:26:00','yyyymmdd hh24:mi:ss')
and sample_time < to_date('20180626 01:31:00','yyyymmdd hh24:mi:ss')
and event is not null
group by trunc(sample_time,'mi'),event
having count(1)>2
order by 1,3;
3 . 定位会话阻塞的源头
关注这一列：final_block
with ash as (select instance_number,session_id,event,blocking_session,program,
to_char(sample_time,'YYYYMMDD HH24MISS') sample_time,sample_id,blocking_inst_id
from dba_hist_active_sess_histroy where
sample_time > to_date('20180626 01:26:00','yyyymmdd hh24:mi:ss')
and sample_time < to_date('20180626 01:31:00','yyyymmdd hh24:mi:ss'))
select * from (
select sample_time,blocking_session final_block,SYS_CONNECT_BY_PATH(session_id,',') sid_chain,
SYS_CONNECT_BY_PATH(event,',') event_chain
from ash start with session_id is not null
CONNECT by prior blocking_session = session_id and
prior instance_number = blocking_inst_id and sample_id = prior sample_id) a
where instr(sid_chain,final_block)=0 and not EXISTS
(select 1 from ash b where a.final_block=b.session_id and b.blocking_session is not null)
order by sample_time
实战案例：
select substr(sample_time,1,16),count(1) from dba_hist_active_sess_histroy
where sample_time >'2018-10-09 09.00.00'
and sample_time < '2018-10-09 10.00.00'
and event is not null
group by SUBSTR(sample_time,1,16)
order by 1;
select substr(sample_time,1,16),event,count(1) from dba_hist_active_sess_histroy
where sample_time >'2018-10-09 09.10.00'
and sample_time < '2018-10-09 10.20.00'
and event is not null
group by SUBSTR(sample_time,1,16),event
having count(1)>2
order by 1;
关注这一列：final_block
with ash as (select instance_number,session_id,event,blocking_session,program,
to_char(sample_time,'YYYYMMDD HH24MISS') sample_time,sample_id,blocking_inst_id
from dba_hist_active_sess_histroy where
sample_time > to_date('20180626 01:26:00','yyyymmdd hh24:mi:ss')
and sample_time < to_date('20180626 01:31:00','yyyymmdd hh24:mi:ss'))
select * from (
select sample_time,blocking_session final_block,SYS_CONNECT_BY_PATH(session_id,',') sid_chain,
SYS_CONNECT_BY_PATH(event,',') event_chain
from ash start with session_id is not null
CONNECT by prior blocking_session = session_id and
prior instance_number = blocking_inst_id and sample_id = prior sample_id) a
where instr(sid_chain,final_block)=0 and not EXISTS
(select 1 from ash b where a.final_block=b.session_id and b.blocking_session is not null)
order by sample_time
进一步确定这个会话在做什么：
select inst_id,sample_time,session_id,sql_id,program,module,action,client_id,event
from gv$active_session_history
where session_id=574 and session_serial# = 141
order by sample_time;
select inst_id,sample_time,session_id,sql_id,sql_opname,program,module,action,client_id,event
from gv$active_session_history
where sample_time > '2018-10-09 09.11.00'
and sample_time < '2018-10-09 09.20.00'
and event ='library cache pin'
and inst_id=1
order by sample_time;
CURRENT_obj# -----这个字段
select sample_time,sql_id,sql_opname,event,CURRENT_obj# from v$active_session_history
where event='db file sequential read'
order by sample_time desc;
