【ORCAL】进程太厉害，影响了主业务系统，该怎么处理？

v$locked_object v$lock锁表的问题

一、症状描述

　　今天客户反馈说，他们的主业务系统宕机了，是因为某个服务器有个进程占用太多资源带宽，而且杀死后会自动恢复，严重影响业务，希望我们协助查询一下具体的原因！

　　我分析了一下原因，应该是执行SQL或程序时，程序没有响应或SQL执行一直处于执行状态（有些时候进程根本停不下来，就直接关掉了窗口），没有成功，也没有报错。

　　具体分析：当对数据库某个表的某一记录做更新或删除等操作，执行完毕后该条语句 没提交事务，假如其他人同时也对该数据库执行一条对于这一记录做更新操作的语句。则在执行的时候就会处于等待状态，便陷入死锁，一直没有执行成功，也没有报错。

二、病因定位：

1 检查数据库，确认 是否真实存在死锁，若有，具体是哪台机器哪个程序：

　　select sid,username, lockwait, status, machine, program from v$session where sid in (select session_id from v$locked_object);

　　Username：死锁语句所用的数据库用户；

　　Lockwait   ：死锁的状态，如果有内容表示被死锁。

　　Status       ：状态，active表示被死锁

　　Machine   ：死锁语句所在的机器。

　　Program   ：产生死锁的语句主要来自哪个应用程序。

2 确定死锁后，还可以检查是哪个语句产生死锁等待。

select sql_text
  from v$sql
 where hash_value in
       (select sql_hash_value
          from v$session
         where sid in (select session_id from v$locked_object))

三、解决过程

1 通过kill掉死锁的session进程

select b.username, b.sid, b.serial#, logon_time
  from v$locked_object a, v$session b
 where a.session_id = b.sid
 order by b.logon_time;

SQL> alter system kill session '12,73';

 

2 查询未提交事务的SQL，大概率是其引起。

select s.sid,
       s.username,
       s.osuser,
       s.program,
       to_char(s.LOGON_TIME, 'yyyymmdd hh24:mi:ss') as LOGON_TIME,
       to_char(t.START_DATE, 'yyyymmdd hh24:mi:ss') as START_DATE,
       s.status,
       (select q.SQL_TEXT
          from v$sql q
         where q.LAST_ACTIVE_TIME = t.START_DATE
           and rownum <= 1) as SQL_TEXT
  from v$session s, v$transaction t
 where s.sADDR = t.SES_ADDR;

但我当时实验语句 like 后面并不对

3 获取oracle进程，直接kill掉oracle进程然后重启不建议（或直接重启数据库）

 

--以下几个相关表是常用的

SELECT * FROM v$lock;  //锁信息
SELECT * FROM v$sqlarea; //查询共享池中已经解析过的SQL语句及其相关信息
SELECT * FROM v$session;//会话信息
SELECT * FROM v$process ;//进程信息
SELECT * FROM v$locked_object;//已锁的对象
SELECT * FROM all_objects;//查询所有对象信息
SELECT * FROM v$session_wait;//等待的会话信息

--查看被锁的表

select b.owner,b.object_name,a.session_id,a.locked_mode

from v$locked_object a,dba_objects b

where b.object_id = a.object_id;

 

--查看那个用户那个进程造成死锁

select b.username,b.sid,b.serial#,logon_time 

from v$locked_object a,v$session b 

where a.session_id = b.sid 

order by b.logon_time;

 

--查看连接的进程
 SELECT sid, serial#, username, osuser FROM v$session; 

--查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode

SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;

这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。

--杀掉进程  sid,serial#

alter system kill session'210,11562';

整理不易，希望可以帮到你！