一 問題描述

開發(fā)同事反饋生產(chǎn)環(huán)境某個接口慢，一個普通的按主鍵更新的update竟然需要5分鐘，而我手動執(zhí)行秒返回，猜測是發(fā)生了阻塞，需要排查出阻塞源。

有時，一個事務(wù)里會包含多個sql，有的還包含上傳附件等操作，一個事務(wù)長時間不提交會導(dǎo)致其他相關(guān)sql被阻塞，比如事務(wù)里的sql執(zhí)行得很快，但上傳附件卡住的話，那也會阻塞其他相關(guān)sql。

查看鎖阻塞的sql只能查看到阻塞方事務(wù)最近執(zhí)行的一條sql，查不到之前的sql，所以有時候查出的阻塞結(jié)果不準(zhǔn)確，這時候需要結(jié)合對這個表的審計來確定阻塞方sql。

二 排查思路

1.創(chuàng)建一個針對這個被阻塞的表的審計

2.讓同事復(fù)現(xiàn)下這個問題

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

4.根據(jù)audsid查看這個會話的審計結(jié)果，看看對這個表都做了啥操作。

三 排查過程

#這里以在自己測試環(huán)境模擬一個簡單的鎖阻塞為例

3.1 創(chuàng)建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'，否則默認(rèn)只審計SELECT。

#查看當(dāng)前有哪些審計

select * from DBA_AUDIT_POLICIES;

3.2 故障模擬

#在會話1用scott用戶登錄，執(zhí)行一個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再執(zhí)行個其他的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 模擬結(jié)束后關(guān)閉審計

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

3.5 查看審計結(jié)果

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

#備注

之前審計過的記錄在審計結(jié)束后，記錄不會清空。

?

?