Oracle故障处理秘籍

michaelliu 2025-03-15

167

TX、TM、DX

现象：数据库大量锁异常等待，系统资源消耗高，CPU负载高。
原因：数据库存在多个事务争用。
解决方法：使用如下SQL查询，定位到长时间不变的holder，kill相关会话。

column event format a30  column sess format a20set linesize 250set pagesize 0break on id1 skip 1select 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 /
alter system kill session '<sid>,<serial#>' immediate;

Cache buffer chains

现象：数据库大量cache buffer chains等待，系统资源消耗高，CPU负载高。

原因：

1. 低效的SQL语句是最重要原因2. 多个进程同时扫描大范围的索引或表3. 应用程序并发执行相同低效率SQL

解决方法：定位该事件相关的会话信息，手工kill会话，记录该会话让对应开发商优化。

select sid,username,machine,program,p1raw,sql_id,logon_time,last_call_et from v$session where event='latch: cache buffers chains';alter system kill session '<sid>,<serial#>' immediate;

library cache lock

现象：数据库大量library cache lock等待，系统资源消耗高，cpu负载接近100%。
原因：

1. 大量对某个对象访问2. shared pool问题

解决方法：定位引发“library cache lock”的会话，分析SQL中相关对象和执行计划，与开发商确认后kill会话。若由于shared pool内部结构问题引发，则使用清空共享池方式处理。

select sql_id,count(*) from v$session where event='library cache lock' group by sql_id order by 2;alter system kill session '<sid>,<serial#>' immediate;
# 下列命令，谨慎操作alter system flush shared pool;

gc buffer busy

现象：数据库大量gc buffer busy等待，cpu占用高，IO繁忙。

原因：rac中多节点同时大量访问某些数据块引发
解决方法：

1. 查看gc buffer busy 事件相关会话select sid,serial#, username,machine,program,p1raw,sql_id,logon_time,last_call_et from v$session where event='gc buffer busy';
2. 查看哪个sql执行次数最多select sql_id,count(*) from v$session where event=' gc buffer busy ' group by sql_id order by 2;
3. 手动kill sessionalter system kill session '<sid>,<serial#>' immediate;

cursor: pin S wait on X

现象：数据库性能下降，cpu和内存占用变高。
原因：

1. 硬解析2. high version counts3. 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=&SID;
3. 根据阻塞者的SQL分析产生原因

latch: undo global data

现象：数据库大量latch: undo global data等待，cpu使用率上升。
原因：一个大事务对某个表进行DML操作，使用大量undo空间。大量并发语句发起对这个表的操作时，由于一致性读，需要使用undo记录进行回滚，产生了该等待。
解决方法：

1. 定位session使用undo量的sqlSELECT 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. 大事务对数据库和应用影响还不大的情况下，可以采用的办法：① 查找v$session_longops，评估是让事务继续执行或kill的代价，选择较低代价。② 如果选择kill会话，可以开启并发回滚事务的特性，加快事务回滚。
3. 大量并发语句，大量"latch: undo global data'等待，应用已经无法响应，cpu使用率达到90%以上的情况：① 联系应用是否可以使用空表临时代替，可以的话，kill session后，将表进行rename，重建空表，让应用临时使用，后续采用分批提交的方式，将原表数据插入到空表。② 如果不能空表替代，只能暂停应用，kill会话等待cpu恢复正常后并发回滚。

01

高资源消耗进程
现象：某个进程CPU / MEM使用率非常高。
解决方法：
1. 使用top查看哪个进程cpu使用率高，再定位数据库sid
select sid, sql_id, event, status from v$session where paddr in (select addr from v$process where spid=&spid);

2. 查看会话使用内存超过100M的用户
set line 300
col MACHINE for a10
col PROGRAM for a25
col USERNAME for a15
select s.sid, s.serial#, s.username, s.machine, s.program,s.process, s.sql_id, p.pga_alloc_mem/1048576 size_m, p.spid
 from v$session s, v$process p where s.paddr=p.addr and p.pga_alloc_mem>104857600 order by 7 desc;
 
 3. 分析进程和会话，决定是否kill
 alter system kill session '<sid>,<serial#>';

02

PGA使用过大
现象：SQL执行效率降低。
解决方法：
1. 查看当前pga使用大小
select sum(pga_alloc_mem)/1024/1024/1024 size_gb from v$process;

2. 查看使用pga较大的进程
set line 300
col MACHINE for a10
col PROGRAM for a25
col USERNAME for a15
select s.sid, s.serial#, s.username, s.machine, s.program,s.process, s.sql_id, p.pga_alloc_mem/1048576 size_m, p.spid
 from v$session s, v$process p where s.paddr=p.addr and p.pga_alloc_mem > 104857600 order by 7 desc;

3. 分析进程和会话，决定是否kill
 alter system kill session '<sid>,<serial#>';

03

CPU使用过高
现象：usr%使用率达到90%以上。
原因：
1. 不良sql造成大量等待
2. 大量的短连接造成cpu负载高
3. bug
解决方法：
1. 定位数据库是否有大量异常等待，如latch free、library cache lock/等
select event, count(*), wait _class from v$session group by event,wait_class order by 2;

2. 若无等待事件，则观察数据库中process和session情况，判定是否与平常存在较大差别，同时判断运行的SQL是否与平时差别较大
select count(1) from v$process;
select count(1) from v$session;

04

大表索引删除引发cpu暴增
现象：cpu使用率暴增，达到90%以上。
原因：大表上的索引被删除，部分sql全表扫描导致资源聚集消耗。
解决方案：
1. 定位低效sql
select to_char(a.logon_time,'yyyy-mm-dd hh24:mi') logon_time,
       a.sql_id,
       a.event,
       a.username,
       a.osuser,
       a.process,
       a.machine,
       a.program,a.module,
       b.sql_text,
       b.LAST_LOAD_TIME,
       to_char(b.last_active_time,'yyyy-mm-dd hh24:mi:ss') last_active_time,
       c.owner,c.object_name,
       a.last_call_et,
       a.sid,a.SQL_CHILD_NUMBER,
       c.object_type,p.PGA_ALLOC_MEM,a.p1,a.p2,a.p3,
       'kill -9 '||p.spid killstr
  from v$session a, v$sql b, dba_objects c,v$process p
 where a.wait_class <> 'Idle' and a.status='ACTIVE' and p.addr=a.paddr
   and a.sql_id = b.sql_id(+) 
   and a.sql_child_number = b.CHILD_NUMBER(+)
   and a.row_wait_obj# = c.object_id(+)
   and a.type='USER'
 order by a.sql_id,a.event;
 
 2. 停掉相关应用，kill等待的session，然后重建索引（parallel nologging）解决。
 ② 应用如果无法停止，则手动停止所有节点监听，kill等待的session，新建索引（parallel nologging)
 alter system kill session '<sid>,'<serial#>';
 create index idx_xxx on <table_name>(<column_name>) nologging parallel 8;

05

网络常见问题
现象1：连接不上数据库
解决方案：
1. 分析连接数，查找连接较多的模块，反馈给对应应用厂商
show parameter session
select count(1) from v$session;
注： 若sqlplus也无法连接，则kill其中几个会话
ps -ef | grep LOCAL=NO | awk '{print $2}'
kill <pid>
select machine, program, count(*) from v$session group by machine, program order by 3;

2. 查看监听，观察监听状态
lsnrctl status 
现象2：客户端不定时断开
原因：
分析过程：
1. 检查数据库配置，包括系统超时限制、用户资源限制和网络超时限制；
col PROFILE for a20
col LIMIT for a20
select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles;

cd $ORACLE_HOME/network/admin
cat sqlnet.ora

2. 在不同网段进行模拟，判断不同网段是否断开现象；

3. 分析是否断开时间是否有规律之类的突破口

一般来说，这种情况由网络/防火墙策略限制导致。

01

空间问题
现象1：SYSAUX表空间满了，但是没有存储可以扩容。
原因：业务表存放在SYSAUX或一些辅助表的空间太大。
解决方法：检查SYSAUX表空间对象情况，定位哪些对象占用大量空间，判断是否可以清理。
SYSAUX表空间不可用时，数据库核心功能还是可以继续运行的，只不过存放在SYSAUX表空间里的功能使用会受限。
col segment_name format a30
col segment_type format a30
set linesize 300
select OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB 
from dba_segments
where TABLESPACE_NAME='SYSAUX'
order by 4;
现象2：临时表空间使用过度，需要使用临时表空间的SQL运行缓慢。
原因：部分SQL大量使用了临时段。
解决方法：
1. 查看单节点临时表空间使用情况
SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM v$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;
 
 2. 查询会话使用临时段的情况
 SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       S.osuser,
       P.spid,
       S.module,
       S.program,
       s.sql_id
       SUM(T.blocks) * TBS.block_size / 1024  mb_used,
       T.tablespace,
       COUNT(*) sort_ops
  FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
   AND S.paddr = P.addr
   AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid,
          S.serial#,
          S.username,
          S.osuser,
          P.spid,
          S.module,
          S.program,
          TBS.block_size,
          T.tablespace
 ORDER BY sid_serial;
 
 3. 如果确认是由于某些会话过度使用临时段引发，与业务确认后kill会话
 alter system kill session '<sid>,<serial#>' immediate;
 
 4. 如果确认这些会话使用的临时段空间属正常，则加大临时表空间
 alter tablespace TEMP add tempfile '/xx/xx/tempxx.dbf' size 5GB;
现象3：undo表空间无法自动扩展，后台日志报ORA-1628：max extents reached for rollback segment错误，致使数据库调度任务无法完成。
原因：回滚段的max extents达到了参数设置的最大值，造成不能分配下一个extent。
解决方法：
1. 分析trace文件，定位到运行的大事务，针对该大事务进行分解，减少对UNDO表空间资源的需求量；
2. 扩容undo表空间的大小

02

执行计划问题
现象：SQL执行计划发生变化，导致SQL运行缓慢。
原因：统计信息变化 或 SQL语句变化。
解决方法：
1. 通过sql_id确定统计信息是否一致（如下语句会将AWR中所有信息查找出来）
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id,
       ss.instance_number node,
       begin_interval_time,
       sql_id,
       plan_hash_value,
       nvl(executions_delta, 0) execs,
       (elapsed_time_delta /
       decode(nvl(executions_delta, 0), 0, 1, executions_delta)) / 1000000 avg_etime,
       (buffer_gets_delta /
       decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta)) avg_lio,
       (disk_reads_delta /
       decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta)) avg_pio
  from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
 where sql_id = nvl('&sql_id', '5xqs2g5tmnk82')
   and ss.snap_id = S.snap_id
   and ss.instance_number = S.instance_number
   and s.instance_number like nvl('&instance_number', s.instance_number)
   and executions_delta > 0
 order by 1, 2, 3
/

2. 如果统计信息不一致，需要检查统计信息是否是最新，如果统计信息更新时间比较久了，则手动收集统计信息。
3. 如果统计信息正常，检查相关业务表数据量是否大幅增加。
4. 如果数据量大幅增加，则需要考虑清理数据或做SQL优化。

03

动态采样问题
现象：SQL在执行过程中异常缓慢，查看执行计划后发现，SQL在执行过程中采用了动态采样。
Note
-----
   - dynamic sampling used for this statement
原因：动态采样是Oracle的一个特性，用于在没有统计信息的情况下，自动收集相关表的统计信息。但也存在某些特殊情况下会仍用动态采用，从而引起执行效率低下。
解决方法：禁用动态采样
1. 禁用动态采样
select /*+dynamic_sampling(t 0) */
2. 如果命中bug 9272549，则及时更新补丁。

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle故障处理秘籍

评论