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

    昨天早上，开发人员抱怨系统很慢，可以运行但运行效率比之前慢很多，大量的提交动作卡住不动，系统呈假死状态。赶紧登录数据库服务器查看情况，首先定位数据库的系统资源(主要是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住。

转载于:https://www.cnblogs.com/oracleblog/p/3580434.html