资源消耗类应急处理方法oracle

1.高资源消耗进程应急处理

现象描述

某个oracle process CPU使用率非常高。
某个oracle process MEM使用率非常高。

影响因素

暂无

解决方法

1、使用TOPAS观察哪个进程CPU使用率高，找出相关进程号，通过以下命令定位数据库SID号.
Select sid,sql_id,event,status from v$session where paddr in (select addr from v$process where spid=&进程号);
2、使用以下命令查看oracle会话使用内存超过100M的用户

set line 180
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.

2.IO非常高

现象描述

数据库登陆缓慢，或者根本不可用，无法登陆数据库进行查询等操作。表空间满或者文件系统满了（一般情况下不会发生，因为我们有监控告警）。这里主要是针对异常sql引起数据库hang的情况

影响因素

分析IO高的原因，例如：
A、大量的并行；
B、长事务；
C、物理读高

解决方法

在操作系统使用命令: ps -ef|grep LOCAL=NO |awk ‘{print $2}’ |xargs kill -9
kill所有非本地进程,然后检查系统资源状态，检查数据库状态

3.PGA使用过大

现象描述

影响SQL执行的效率

影响因素

PGA使用过大

解决方法

1、查询当前PGA使用大小：

select sum(pga_alloc_mem)/1048576/1024 size_gb from v$process ;

2.查询使用PGA较大的具体进程
例如以下语句可以查出具体占用内存大于100m的进程信息:(例如）

set line 180
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.咨询开发商是否可以删除语句

alter system kill session 'sid,serial#';

4.CPU使用过高应急处理

现象描述

CPU中 usr%使用率达到90以上

影响因素

CPU使用过高，一般表现在以下几点：
A、不良SQL造成的大量等待事件
B、大量的短连接造成CPU负载高。
C、BUG引起

解决方法

1、当CPU出现高负载的时候，首先我们要检查当前的数据库里是否有大量异常等待，例如：latch free, library cache lock/pin 等待事件。
select event,count(*),wait _class from v$session group by event,wait_class order by 2;
如果有，根据相关等待事件分析问题。也可以通过HANG分析，进行阻塞源头会话定位。
2、当CPU出现高负载的时候，检查发现当前数据库并无任何异常等待事件，我们就要参考平时的CPU使用率指标，然后通过会话、事务量来衡量。
3、当CPU出现高负载的时候，检查发现当前数据库并无任何异常等待事件，当前活动SQL语句与平时差别很大，我们可以关闭监听，检查是否由于连接造成的。

5.大表索引被删除导致CPU 100%的应急处理

现象描述

CPU使用率90%以上

影响因素

大表上索引被删除，部分SQL全表扫描导致资源集聚消耗。

解决方法

1、查找某个实例上等待session的信息，定位低效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、非rac环境
a.部分大表索引删除，导致全表扫面，cpu 100%
b.此时数据库基本处于hang状态，应用已经无法响应
c.停止相关应用，kill掉这些等待session, 新建索引（parallel nologging）
d.如果无法停止应用，停止数据库的监听，kill掉等待session，新建索引（parallel nologging）

3、rac环境
a.部分大表索引删除，导致全表扫面，cpu 100%
b.此时该节点基本处于hang状态，应用已经无法响应
c.停止相关应用，kill掉这些等待session, 新建索引（parallel nologging）
d.如果无法停止应用，停止所有节点的监听，kill掉等待session，新建索引（parallel nologging）