oracle排查长时间没提交的事务造成的阻塞案例

一 问题描述

开发同事反馈生产环境某个接口慢，一个普通的按主键更新的update竟然需要5分钟，而我手动执行秒返回，猜测是发生了阻塞，需要排查出阻塞源。

有时，一个事务里会包含多个sql，有的还包含上传附件等操作，一个事务长时间不提交会导致其他相关sql被阻塞，比如事务里的sql执行得很快，但上传附件卡住的话，那也会阻塞其他相关sql。

查看锁阻塞的sql只能查看到阻塞方事务最近执行的一条sql，查不到之前的sql，所以有时候查出的阻塞结果不准确，这时候需要结合对这个表的审计来确定阻塞方sql。

二 排查思路

1.创建一个针对这个被阻塞的表的审计

2.让同事复现下这个问题

3.查下阻塞，抓下阻塞方的audsid

4.根据audsid查看这个会话的审计结果，看看对这个表都做了啥操作。

三 排查过程

#这里以在自己测试环境模拟一个简单的锁阻塞为例

3.1 创建FGA审计，只审计被阻塞的表

#审计SCOTT.T2表

begin

dbms_fga.add_policy (

object_schema => 'SCOTT',

object_name => 'T2',

policy_name => 'TEST',

statement_types => 'SELECT,INSERT,UPDATE,DELETE'

);

end;

/

--必须指定statement_types => 'SELECT,INSERT,UPDATE,DELETE'，否则默认只审计SELECT。

#查看当前有哪些审计

select * from DBA_AUDIT_POLICIES;

3.2 故障模拟

#在会话1用scott用户登录，执行一个sql，但不提交

SQL>  update scott.t2 set name='ffff' where id=2;

#在会话2也修改同一条记录

SQL>  update scott.t2 set name='ggggg' where id=2;

会话2会被会话1阻塞。

#在会话1再执行个其他的sql

SQL> select 1 from dual;

3.3 查看阻塞

3.3.1 查看阻塞链

select *from (select a.inst_id,a.sid, a.serial#,a.sql_id,a.event,a.status,connect_by_isleaf as isleaf,sys_connect_by_path(SID, '<-') tree,level as tree_levelfrom gv$session astart with a.blocking_session is not nullconnect by nocycle a.sid = prior a.blocking_session)where isleaf = 1order by tree_level asc;

可以看到3152这个会话阻塞了3635。

3.3.2 查看具体阻塞

SELECT DISTINCT s1.inst_id as blocking_inst_id,s1.username as blocking_username,s1.machine as blocking_machine,s1.module as blocking_module,s1.sid as blocking_sid,s1.audsid as blocking_audsid,s1.serial# as blocking_serial#,c1.sql_text as blocking_sql_text,s1.status as blocking_staus,s1.event as blocking_event,s2.inst_id as waiting_inst_id,s2.username as waiting_username,s2.machine waiting_machine,s2.module as waiting_module,s2.sid as waiting_sid,s2.audsid as waiting_audsid,s2.serial# as waiting_serial#,c2.sql_text as waiting_sql_text,s2.status as blocking_staus,s2.event as waiting_eventFROM gv$lock l1,gv$session s1,gv$lock l2,gv$session s2,gv$sqlarea c1,gv$sqlarea c2,gv$process b1,gv$process b2WHERE     s1.sid = l1.sidAND s2.sid = l2.sidAND s1.inst_id = l1.inst_idAND s2.inst_id = l2.inst_idAND s1.paddr = b1.addrAND s2.paddr = b2.addrAND c1.SQL_ID=s1.PREV_SQL_ID       AND s2.sql_hash_value = c2.hash_valueAND l1.block > 0AND l2.request > 0AND l1.id1 = l2.id1AND l1.id2 = l2.id2UNIONSELECT DISTINCT s1.inst_id as blocking_inst_id,s1.username as blocking_username,s1.machine as blocking_machine,s1.module as blocking_module,s1.sid as blocking_sid,s1.audsid as blocking_audsid,s1.serial# as blocking_serial#,c1.sql_text as blocking_sql_text,s1.status as blocking_staus,s1.event as blocking_event,s2.inst_id as waiting_inst_id,s2.username as waiting_username,s2.machine waiting_machine,s2.module as waiting_module,s2.sid as waiting_sid,s2.audsid as waiting_audsid,s2.serial# as waiting_serial#,c2.sql_text as waiting_sql_text,s2.status as blocking_staus,s2.event as waiting_eventFROM gv$lock l1,gv$session s1,gv$lock l2,gv$session s2,gv$sqlarea c1,gv$sqlarea c2,gv$process b1,gv$process b2WHERE     s1.sid = l1.sidAND s2.sid = l2.sidAND s1.inst_id = l1.inst_idAND s2.inst_id = l2.inst_idAND s1.paddr = b1.addrAND s2.paddr = b2.addrAND c1.hash_value=s1.sql_hash_valueAND c1.address=s1.sql_addressAND s2.sql_hash_value = c2.hash_valueAND l1.block > 0AND l2.request > 0AND l1.id1 = l2.id1AND l1.id2 = l2.id2

这里看到sid为3152的会话阻塞了sid为3635的会话。

阻塞方sql为select 1 from dual

被阻塞方sql为update scott.t2 set name='ggggg' where id=2

这显然不合理。因为select 1 from dual不会阻塞任何sql。

记录下阻塞源的audsid。

这里是75356902。

3.4 模拟结束后关闭审计

begin
dbms_fga.drop_policy(object_schema=>'SCOTT',object_name=>'T2',policy_name=>'TEST');
end;
 /

3.5 查看审计结果

select * from dba_fga_audit_trail where session_id=阻塞方audsid;

#这里是75356902

select * from dba_fga_audit_trail where session_id=75356902;

找到造成阻塞的sql了： 

update scott.t2 set name='ffff' where id=2

#备注

之前审计过的记录在审计结束后，记录不会清空。