oracle处理阻塞与死锁

发表于 更新于 分类于

1. 死锁处理

1.1. 建立触发器

登录oracle的dba权限用户，然后执行以下SQL：

1
2
3
4
5
create or replace trigger login_on_record_ip
after logon on database
begin   
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;

1.2. 查询锁死

1
2
3
select username,lockwait,status,machine,program,logon_time,CLIENT_INFO from v$session
where sid in
(select session_id from v$locked_object);

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

Lockwait：死锁的状态

Status： 状态，active表示被死锁

Machine： 死锁语句所在的机器

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

logon_time：登录时间

CLIENT_INFO：客户端登录IP

1.3. 查看死锁的语句

1
2
3
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.4. 查找死锁的进程

1
2
3
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

1.5. 杀死死锁的进程

1
alter system kill session 'sid,serial#';

2. 阻塞处理

2.1. 查询阻塞

以下语句可以查询执行超过15分钟的SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SELECT S.SQL_TEXT,
       S.SQL_FULLTEXT,
       S.SQL_ID,
       ROUND(ELAPSED_TIME / 1000000 / (CASE
               WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN
                1
               ELSE
                EXECUTIONS
             END),
             2) "执行时间'S'",
       S.EXECUTIONS "执行次数",
       S.OPTIMIZER_COST "COST",
       S.SORTS,
       S.MODULE, --连接模式（JDBC THIN CLIENT：程序）
       -- S.LOCKED_TOTAL,
       --S.PHYSICAL_READ_BYTES "物理读",
       -- S.PHYSICAL_READ_REQUESTS "物理读请求",
       --S.PHYSICAL_WRITE_REQUESTS "物理写",
       -- S.PHYSICAL_WRITE_BYTES "物理写请求",
       S.ROWS_PROCESSED      "返回行数",
       S.DISK_READS          "磁盘读",
       S.DIRECT_WRITES       "直接路径写",
       S.PARSING_SCHEMA_NAME,
       S.LAST_ACTIVE_TIME
  FROM GV$SQLAREA S
 WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE
               WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN
                1
               ELSE
                EXECUTIONS
             END),
             2) > 900 --100 0000微秒=1S
   AND S.PARSING_SCHEMA_NAME = USER
   AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') =
       TO_CHAR(SYSDATE, 'YYYY-MM-DD')
   AND S.COMMAND_TYPE IN (2, 3, 5, 6, 189)
 ORDER BY "执行时间'S'" DESC;

2.2. 查询阻塞的进程

1
select sid,serial# from v$session where sql_id='sql_id'

sql_id为上文查出来的S.SQL_ID。

2.3. 杀死阻塞的进程

1
alter system kill session 'sid,serial#';