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

ops/2024/11/25 11:31:29/

一 问题描述

开发同事反馈生产环境某个接口慢,一个普通的按主键更新的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阻塞

99427d4d725e42bfb67a81dc80e33d6d.png

#在会话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;

413c757dff7d4afcb62a257779f2c091.png

可以看到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

ada4bdf85a60413892512d368b736231.png

这里看到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;

d4a61fdef6e047118a2fb14f41dc2ea8.png

找到造成阻塞的sql了: 

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

#备注

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

 

 


http://www.ppmy.cn/ops/136552.html

相关文章

使用LUKS对Linux磁盘进行加密

前言 本实验用于日常学习用&#xff0c;如需对存有重要数据的磁盘进行操作&#xff0c;请做好数据备份工作。 此实验只是使用LUKS工具的冰山一角&#xff0c;后续还会有更多功能等待探索。 LUKS&#xff08;Linux Unified Key Setup&#xff09;是Linux系统中用于磁盘加密的一…

网页F12:缓存的使用(设值、取值、删除)

一、设置值 例如&#xff1a;设置一个key为name&#xff0c;value为Jack的值 1、在控制台输入代码 localStorage.setItem(name,Jack) 2、查看缓存值 二、取值 1、在控制台输入代码 取出key为name的值 localStorage.getItem(name) 三、删除 删除指定key的值 1、在控制台输…

UE材质不透明蒙版选项

①什么是不透明蒙版 在 Unreal Engine 5 (UE5) 中&#xff0c;不透明蒙版插槽 (Opacity Mask) 是材质中的一个重要参数&#xff0c;用于控制材质的透明度和不透明区域。具体来说&#xff0c;它允许你在材质中实现 部分透明 或 不透明 的效果&#xff0c;通常用于实现如 不规则…

moduo之单例模板Singleton

简介 moduo提供了单例模板类&#xff0c;是线程安全的 结构 单例是动态分配的&#xff0c;不是使用静态变量。其线程安全是通过pthread_once_t #mermaid-svg-N0Vthvvibe8sdg0v {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}…

设计模式——组合实体模式

定义与概念 组合实体模式&#xff08;Composite Entity Pattern&#xff09;是一种设计模式&#xff0c;主要用于将多个相关的实体组合成一个单一的实体&#xff0c;以实现对这些实体的统一管理和操作。这种模式在处理复杂的对象关系时非常有用&#xff0c;它将相关的数据和操…

解决k8s拉取私有镜像401 Unauthorized 问题

拉取镜像时未指定账户和密码通常是因为需要访问的镜像仓库启用了认证&#xff0c;但 Kubernetes 默认配置中未提供访问凭据。要解决此问题&#xff0c;可以按照以下步骤配置镜像仓库的认证信息&#xff1a; 1. 创建 Kubernetes Secret 为镜像仓库配置访问凭据&#xff0c;使用…

量子感知机

神经网络类似于人类大脑&#xff0c;是模拟生物神经网络进行信息处理的一种数学模型。它能解决分类、回归等问题&#xff0c;是机器学习的重要组成部分。量子神经网络是将量子理论与神经网络相结合而产生的一种新型计算模式。1995年美国路易斯安那州立大学KAK教授首次提出了量子…

2024年北京海淀区中小学信息学竞赛(初赛)试题

来源&#xff1a;2024年北京海淀区中小学信息学竞赛&#xff08;初赛&#xff09;试题 | 6547网 2024年北京海淀区中小学信息学竞赛&#xff08;初赛&#xff09;试题 题目总数&#xff1a;24 总分数&#xff1a;100 单项选择题(共有 15 道小题&#xff0c;每道小题3分&am…