等待事件类应急处理方法oracle

1.“TX,TM,DX”锁应急处理

现象描述

数据库大量锁异常等待，系统资源消耗高，cpu负载高 （针对大量’TX,TM,DX’等类型的锁造成的大量异常等待）

影响因素

多个事务争用造成。

解决方法

以下语句列出是谁造成了阻塞
column event format a30
column sess format a20
set linesize 250
set pagesize 0
break on id1 skip 1
select decode(request,0,‘Holder:’,’ Waiter:‘) || s.inst_id || ‘:’ || s.sid||’,'|| s.serial# sess,
id1, id2, lmode, request, l.type, ctime, s.username,s.sql_id, s.event
– ,s.service_name
from gv$lock l, gv$session s
where (id1, id2, l.type) in
(select id1, id2, type from gv$lock where request>0
)
and l.sid=s.sid
and l.inst_id=s.inst_id
order by id1, ctime desc, request
/

按照这个语句多查询几次，如果Holder不变，则KILL掉。操作前记录相关日志

2.“Latch free”应急处理

现象描述

数据库大量latch free等待，系统资源消耗高，cpu负载接近100%

解决方法

1.查询当前active的会话模块：
select username,machine,count() from v$session where status=‘ACTIVE’ having count()>6 group by username,machine order by 1;
将会话数量过多的模块通知开发商，让他们切换部分业务到另外一个节点
然后进行系统资源监控和数据库监控

3.“Cache buffer chains”应急处理

现象描述

数据库大量cache buffer chains等待，系统资源消耗高，cpu负载高

影响因素

A.低效的SQL语句是发生 cache buffers chains(热块争用），锁存器争用的最重要原因。
B.多个进程同时扫描大范围的索引或表时，可能广泛引发cache buffers chains 锁存器争用。
C.应用程序打开执行相同的低效率SQL语句的多个并发会话，并且这些SQL语句都设法得到相同的数据集，这种情景十分普遍

解决方法

1.查看 latch: cache buffers chains 事件相关的会话信息;
select sid,username,machine,program,p1raw,sql_id,logon_time,last_call_et from v$session where event=‘latch: cache buffers chains’;
使用ora命令 ora get_kill_sh &sql_id &username 进行查杀.
查杀后记录该sql语句,丢给相应的开发商处理

2、查看哪个SQL执行的次数最多
select sql_id,count(*) from v$session where event=‘latch: cache buffers chains’ group by sql_id order by 2;

4.“Library cache lock”应急处理

现象描述

数据库大量library cache lock等待，系统资源消耗高，cpu的idle为0

影响因素

library cache lock出现的情况比较复杂，例如：
A、大量对某个对象访问；
B、shared pool有问题；

解决方法

1、看看是不是某条SQL引起
select sql_id,count(*) from v$session where event=‘library cache lock’ group by sql_id order by 2;
然后分析SQL中的对象和执行计划等，再跟开发商确认，用ora get_kill_sh进行杀
2、shared pool内存不够引起，例如不合理的shared pool参数、导致对象不断的age in和age out、从而并发解析导致library cache、latch shared pool等竞争。
3、其他DDL、游标多版本、11g的密码延迟、12c update user$等

5.“gc buffer busy”应急处理

现象描述

一般的现象为CPU较高，IO较忙，处理方法与cache buffer chains应急处理一样

影响因素

gc buffer busy出现在RAC中，出现概率并不高，因为BOSS是对业务做了分离的，是由于多节点同时大量访问某些数据块引起的

解决方法

1.查看 latch: cache buffers chains 事件相关的会话信息;
select sid,serial#, username,machine,program,p1raw,sql_id,logon_time,last_call_et from v$session where event=‘gc buffer busy’;
使用ora命令alter system kill session ‘sid,serial#’ 进行查杀.
查杀后记录该sql语句,丢给相应的开发商处理
2、查看哪个SQL执行的次数最多
select sql_id,count(*) from v$session where event=’ gc buffer busy ’ group by sql_id order by 2;

6.“cursor: pin S wait on X”事件应急处理

现象描述

影响因素

一般包含以下几种：
1、常见硬解析
2、High Version Counts
3、BUG

解决方法

1、查找等待事件的阻塞者：
select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),‘XXXXXXXX’) sid from v$session where event = ‘cursor: pin S wait on X’;
2、查看阻塞者在做什么：
select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT
from v$session where SID=31;
3、根据阻塞者的SQL分析产生原因。

7.“latch: undo global data”事件应急处理

现象描述

一个大事务对某个表进行DML操作，使用大量undo空间。大量并发语句发起对这个表的操作，由于一致性读，需要使用undo记录进行回滚，产生latch:undo global data等待，cpu使用率上升

影响因素

一般包含以下几种：
1、大事务对某个表进行DML操作

解决方法

1、查找session使用undo量的SQL：
SELECT r.name rbs,
nvl(s.username, ‘None’) oracle_user,
s.osuser client_user,
p.username unix_user,
s.sid,
s.serial#,
p.spid unix_pid,
t.used_ublk * TO_NUMBER(x.value) / 1024 / 1024 as undo_mb ,
TO_CHAR(s.logon_time, ‘mm/dd/yy hh24:mi:ss’) as login_time,
TO_CHAR(sysdate - (s.last_call_et) / 86400, ‘mm/dd/yy hh24:mi:ss’) as last_txn,
t.START_TIME transaction_starttime
FROM v$process p,
v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND s.paddr = p.addr
AND r.usn = t.xidusn(+)
AND x.name = ‘db_block_size’
ORDER by undo_mb desc
/

2、大事务对数据库和应用影响还不大得情况下，可以采取的方法：
a.查找v$session_longops，评估是让事务进行还是Kill发起大事务的session各自的代价，选择其中一个代价较低的方式。
b.如果是选择kill掉session，可以开启并发回滚事务的特性，加快事务回滚。

3、大量并发语句，大量’latch:undo global data’等待，应用已经无法响应，CPU使用90%以上的情况：
a.此时不管是采用何种回滚特性（并发回滚、单进程回滚）,由于已经没有cpu资源，回滚都非常耗时。
b.联系应用确认是否可以空表暂时代替，如果可以，可以再kill掉session后，将表rename掉，重新建一种空表，让应用临时使用。
c.后续使用分批提交的方式，将源表数据回插空表。
d.如不能空表代替，则只能暂停应用，kill掉等待session，cpu恢复正常后并发回滚，或建空表回插数据。
4、事件处理完毕后，对发起大事务的程序发给应用侧修改，如果是个人发起，则加强培训

8.“enq:US-content” or 回滚表空间使用过度事件应急处理

影响因素

一般包含以下几种：
1、回滚表空间使用过度，session发起新事务查找回滚段时需要排队等待
2、Oracle Bug

解决方法

1、查找undo表空间使用情况的SQL：
select b.tablespace_name,
nvl(used_undo,0) “USED_UNDO(M)”,
total_undo “Total_undo(M)”,
trunc(nvl(used_undo,0) / total_undo * 100, 2) || ‘%’ used_PCT
from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name
from dba_undo_extents
where status in ( ‘ACTIVE’,‘UNEXPIRED’)
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) total_undo
from dba_data_files
where tablespace_name in
(select value
from v$spparameter
where name = ‘undo_tablespace’
and (sid = (select instance_name from v$instance) or
sid = ‘*’))
group by tablespace_name) b
where a.tablespace_name (+)= b.tablespace_name
/
2、回滚表空间中大部分都是ACTIVE extent的情况
a.查询是哪些session引起，用“二十”中提供的SQL
b.临时新增undo数据文件解决
c.事后根据查找到的session对提交应用侧修改程序。个人引起则加强培训
3、回滚表空间中大部分都是UNEXPIRED extent的情况
a.减小undo_retention值
b.如果undo_retention的值正常，则新增undo数据文件，加大undo表空间
回滚表空间使用情况正常
a.查看undo相关的隐含参数是否设置正确

9.“gc buffer busy”问题

现象描述

环境：SunOS 5.9(64bit, sparc)+RAC+oracle 10.2.0.3
场景：话务性能库不断报出 gc buffer busy ，系统资源消耗较高。

影响因素

以下几点可导致此事件：
A、Hot Blocks；
B、低效率的查询；

解决方法

1、查询事件相关用户sql
select sid,username,machine,program,p1raw,sql_id,logon_time,last_call_et from v$session where event=‘gc buffer busy’;

2、根据sql_id,username查询出sql，分析执行效率，与厂商协商解决。
select sql_text from v$session from v$sqlarea where sql_id = ‘xx’;