利用 ASH 視圖監(jiān)控數(shù)據(jù)庫性能
當前會話監(jiān)控語句：
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;
歷史活動會話檢查：監(jiān)控檢查最近10分鐘的數(shù)據(jù)庫等待事件的情況
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中出現(xiàn)的行數(shù)，記錄數(shù)越多咏瑟，說明數(shù)據(jù)庫越活躍，負載越大归榕，可能等待越多
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 視圖分析數(shù)據(jù)庫阻塞問題
1 .? 分析找出會話阻塞出現(xiàn)的時間段
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 .? 分析會話阻塞的等待事件，進一步縮小時間范圍
關(guān)注新出現(xiàn)的等待事件和等待事件次數(shù)變多的
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 .? 定位會話阻塞的源頭
關(guān)注這一列：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
實戰(zhàn)案例：
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;
關(guān)注這一列：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;
