获取特定时间段root blocker信息
通过V$ACTIVE_SESSION_HISTORY只获取单个实例的堵塞关系
alter session set nls_date_format='yyyymmddhh24miss';
set linesize 260 pagesize 1000
col sample_time for a15
col final_program for a35
col final_sql for a15
col FINAL_EVT for a30
col WAIT_LVL_EVENT for a30
col FINAL_SID for 999999
col FINAL_SERI# for 9999999
col FINAL_INST for 99
with ash_tab as
(
select *
from gv$active_session_history
where sample_time between
to_date('&date1', 'yyyymmddhh24miss') and
to_date('&date2', 'yyyymmddhh24miss')
and inst_id in (select instance_number from v$instance)
)
select to_char(SAMPLE_TIME,'yyyymmddhh24miss') sample_time,
FINAL_INST,
FINAL_SID,
FINAL_SID_SERIAL# FINAL_SERI#,
FINAL_PROGRAM,
FINAL_SQL,
nvl(FINAL_EVT, 'ON CPU') as FINAL_EVT,
INST_ID,
max(WAIT_LVL_EVENT) as WAIT_LVL_EVENT,
count(*)
from (select INST_ID,
SESSION_ID,
SAMPLE_TIME,
sys_connect_by_path(SESSION_ID, ',') CHAIN,
connect_by_root(INST_ID) FINAL_INST,
connect_by_root(SESSION_ID) FINAL_SID,
connect_by_root(session_serial#) FINAL_SID_SERIAL#,
connect_by_root(PROGRAM) as FINAL_PROGRAM,
connect_by_root(sql_id) FINAL_SQL,
connect_by_root(event) FINAL_EVT,
event,
case
when level>=2 then
event
end as WAIT_LVL_EVENT
from ash_tab t
start with BLOCKING_SESSION is null
connect by prior SESSION_ID = BLOCKING_SESSION
and prior INST_ID = BLOCKING_INST_ID
and prior session_serial# = blocking_session_serial#
and sample_id = prior sample_id)
group by SAMPLE_TIME,FINAL_INST,FINAL_SID, FINAL_SID_SERIAL#,FINAL_EVT,FINAL_PROGRAM, FINAL_SQL,INST_ID
having count(*) > 1
order by SAMPLE_TIME;
通过DBA_HIST_ACTIVE_SESS_HISTORY获取单个实例的堵塞关系
alter session set nls_date_format='yyyymmddhh24miss';
set linesize 260 pagesize 1000
col sample_time for a15
col final_program for a35
col final_sql for a15
col FINAL_EVT for a30
col WAIT_LVL_EVENT for a30
col FINAL_SID for 999999
col FINAL_SERI# for 9999999
col FINAL_INST for 99
with ash_tab as
(
select *
from dba_hist_active_sess_history
where sample_time between
to_date('&date1', 'yyyymmddhh24miss') and
to_date('&date2', 'yyyymmddhh24miss')
and instance_number in (select instance_number from v$instance)
)
select to_char(SAMPLE_TIME,'yyyymmddhh24miss') sample_time,
FINAL_INST,
FINAL_SID,
FINAL_SID_SERIAL#,
FINAL_PROGRAM,
FINAL_SQL,
nvl(FINAL_EVT, 'ON CPU') as FINAL_EVT,
instance_number,
max(WAIT_LVL_EVENT) as WAIT_LVL_EVENT,
count(*)
from (select instance_number,
SESSION_ID,
SAMPLE_TIME,
sys_connect_by_path(SESSION_ID, ',') CHAIN,
connect_by_root(INSTANCE_NUMBER) FINAL_INST,
connect_by_root(SESSION_ID) FINAL_SID,
connect_by_root(session_serial#) FINAL_SID_SERIAL#,
connect_by_root(PROGRAM) as FINAL_PROGRAM,
connect_by_root(sql_Id) FINAL_SQL,
connect_by_root(event) FINAL_EVT,
event,
case
when level>=2 then
event
end as WAIT_LVL_EVENT
from ash_tab t
start with BLOCKING_SESSION is null
connect by prior SESSION_ID = BLOCKING_SESSION
and prior INSTANCE_NUMBER = BLOCKING_INST_ID
and prior session_serial# = blocking_session_serial#
and sample_id = prior sample_id)
group by SAMPLE_TIME,FINAL_INST,FINAL_SID, FINAL_SID_SERIAL#,FINAL_EVT,FINAL_PROGRAM,FINAL_SQL,instance_number
having count(*) > 1
order by SAMPLE_TIME;
oracle ssc提供的脚本1
挖掘dba_hist_active_session_history视图的信息
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 t.lv,
t.iscycle,
t.sample_id,
t.sample_time,
t.inst_id,
t.session_id,
t.session_serial#,
t.inst_id,
t.sql_opname,
t.program,
t.session_type,
t.sql_id,
nvl(t.event,'ON CPU') event,
t.seq#,
t.session_state,
t.blocking_inst_id,
t.blocking_session,
t.blocking_session_status,
t.c blocking_session_count
from (select t.*,
count(*) over(partition by instance_number, sample_time, session_id, session_serial#) c, /*这个是有多少个会话被root blocker堵塞*/
row_number() over(partition by instance_number, sample_time, session_id, session_serial# order by 1) r1 /*计算排名,后面会取排名第一个会话*/
from (select /*+parallel(t,8) */
level lv,
connect_by_isleaf isleaf,
connect_by_iscycle iscycle,
t.*
from ash_tab t
where instance_number = 1
start with blocking_session is not null
connect by nocycle
prior sample_time = sample_time
and prior blocking_inst_id = instance_number
and prior blocking_session = session_id
and prior blocking_session_serial# = session_serial#) t
where t.isleaf = 1) t /*这里的树形查询是先以leaf开始往root查询，因此leaf的connect_by_isleaf是0，root的connect_by_isleaf是1，此时将isleaf是1的查出来就是root blocker，每一个会话的root blocker都会在树形查询中被显示出来，然后再group by分组求count(*),求root blocker的信息和等待*/
where r1 = 1
and c>=2 /*可以选择>=2略去某个单个session等待的会话，这些会话在每个sample——time阶段并没有数状关系，可能是因为ash没有抓取到，所以要将其略去*/
order by sample_time,c desc;
oracle ssc提供的脚本2
With ash As
(
SELECT /*+ materialize */
*
FROM
ash_20200118
),
chains As
(
SELECT
inst_id,
session_id,
level lvl,
sys_connect_by_path('inst '
|| inst_id
|| ' ' || sql_id
|| ' '
|| event, ' -> ') path,
CONNECT_BY_ISLEAF isleaf
FROM
ash
START WITH event = '&event_name'
Connect By nocycle (
PRIOR blocking_session = session_id
AND PRIOR blocking_session_serial# = session_serial#
AND PRIOR blocking_inst_id = inst_id
AND PRIOR sample_id = sample_id /*And ((Prior sample_time) - sample_time Between Interval '-1' Second And Interval '1' Second)*/)
)
Select
inst_id,
lpad(round(ratio_to_report(Count(*)) over() * 100) || '%', 5, ' ') "%This",
Count(*) samples,
path
From chains
Where isleaf = 1
Group By inst_id, path
Order By samples desc;
