一、查看阻塞的源头
1.在60分钟内查看引起等待最多的SQL：
select ash.user_id,
u.username,
s.sql_text,
sum(ash.wait_time +
ash.time_waited) ttl_wait_time
from v$active_session_history ash,
v$sqlarea s,
dba_users u
where ash.sample_time between sysdate - 30/2880 and sysdate
and ash.sql_id = s.sql_id
and ash.user_id = u.user_id
group by ash.user_id,s.sql_text, u.username
order by ttl_wait_time;
2.在数据库hang的时间段内，有多少个session在等待'enq: TA - contention'
select event,count(*) from dba_hist_active_sess_history
where event='enq: TA - contention' and instance_number=2
group by sample_time,event order by sample_time;
3.查看哪些会话在等待
select session_id,session_serial#,session_state,blocking_session_status,
blocking_session b_sess,BLOCKING_SESSION_SERIAL# b_ser,BLOCKING_INST_ID inst
from dba_hist_active_sess_history
where event='enq: TA - contention' and instance_number=2
order by sample_time;
4.查看哪些会话在阻塞另一个会话
select session_id,session_serial# serial,event,session_state,blocking_session_status state,
blocking_session b_sess,BLOCKING_SESSION_SERIAL# b_ser,blocking_inst_id inst
from dba_hist_active_sess_history
where session_id=3666 and session_serial#=11235 and instance_number=2
order by sample_time;
二、查看锁的源头
WITH LOCK_1 AS
( SELECT DISTINCT S.INST_ID, S.SID, S.BLOCKING_SESSION, S.LAST_CALL_ET
FROM GV$SESSION S
WHERE S.BLOCKING_SESSION IS NOT NULL
and BLOCKING_SESSION_STATUS = 'VALID' )
SELECT BLOCKING_SESSION
FROM LOCK_1
WHERE BLOCKING_SESSION NOT IN ( SELECT SID FROM LOCK_1);
实现 Oracle 查找锁之间依赖关系的最源头SID (首先得到锁的SID)，再通过SID找sql查问题..
*** 思路
1、证明有锁，需要满足v$session.BLOCKING_SESSION[阻塞会话的SID] IS NOT NULL and BLOCKING_SESSION_STATUS = 'VALID'条件；
2、阻塞会话的SID 不在 SID 中就是最源头的SID (首先得到锁的SID)；
3、注意：v$session单实例，gv$session 多节点；