ITPub博客

首页 > 数据库 > Oracle > 一个查询引发的血案---记一次Oracle系统调优

一个查询引发的血案---记一次Oracle系统调优

原创 Oracle 作者:htyro 时间:2014-03-27 14:12:46 0 删除 编辑
    昨天早上,开发人员抱怨系统很慢,可以运行但运行效率比之前慢很多,大量的提交动作卡住不动,系统呈假死状态。赶紧登录数据库服务器查看情况,首先 定位数据库的系统资源(主要是cpu)的消耗情况,通过top命令查看,发现系统CPU和内存这块都正常,未出现瓶颈现象,再次思考,难道是某张或几张主 表被锁导致业务阻塞?

    带着这个疑问,分别查看了v$session_wait,v$lock两张表,发现v$session_wait里面大量的“SQL*Net message from client”等待事件,且wait_type为“IDLE”,也就是空间等待。查询v$lock表,通过CTIM排序也发现类似的情况,有几个 session持有TM类型的锁,事件都是几千秒,类似下面的图:

  TM是表级的锁,LMODE均为3,也就是SX(表级共享行级排他锁),按理来说不会阻塞其他TM锁级别的会话。再次查看v$lock里面block为1 的SID,看看是哪个对象产生了阻塞,结果发现产生阻塞的对象是一张子表,而产生阻塞的SID是对其主表进行了DML操作(delete了一条记录但未提 交),此时问题基本上确定了,因为产生阻塞的SID正在执行一个存储过程,该存储过程涉及操作多张表,其中就有一张很关键的主业务表,在执行删除操作后, 下面的操作是一个大的查询赋值,此查询当天不知道为什么HANG住了,导致持有的锁一直未释放,从而阻塞了对其他子表进行操作的SESSION,话说回来 也是一个典型的子表缺失索引的主外键表操作问题。


PS:提供几条SQL用于快速定位问题

 

---查看哪些会话被阻塞
select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid  
||','||d.serial# block_msg, a.block  
   from v$lock a,v$lock b,v$session c,v$session d  
    where a.id1=b.id1  
     and a.id2=b.id2  
     and a.block>0  
     and a.sid <>b.sid  
     and a.sid=c.sid  
     and b.sid=d.SID  
   ;  




--查看某个会话正在执行的SQL
select sql_text from v$sqltext_with_newlines where (hash_value,address)
in (select sql_hash_value,sql_address from
v$session where sid=145) order by address,piece;

 

另:对于外键表同时有2列外键到同一张主表的情况要特别注意,如果单独一列没有索引,那么在主表做DML操作未提交时,另外一个会话如果也对主表做DML操作,

此时会对子表请求一个lmode=5的锁,也就是事务排他锁,此时会HANG住。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22166274/viewspace-1130687/,如需转载,请注明出处,否则将追究法律责任。

上一篇: GoldenGate使用教程
请登录后发表评论 登录
全部评论

注册时间:2009-07-22

  • 博文量
    12
  • 访问量
    86500