Oracle故障处理秘籍（二）

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：PGCM、OCP、YCP
Skill：Oracle、Mysql、PostgreSQL、国产数据库
Platform：CSDN、墨天轮、公众号（呆呆的私房菜）

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#>';

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#>';

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;

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;

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 

分析过程：
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. 分析是否断开时间是否有规律之类的突破口


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