故障解决
某天上午9点15分接到客户电话,客户反应数据库cpu使用率过高,接近100%,正值业务高峰期,连忙安排一系列的排查定位。为了避免客户信息的暴露,下面主要列出了排查过程中的几个重要节点,具体表名也将用其他代替。
下面罗列主要的处理节点。
获取AWR查看信息
涉及到性能问题,最直观,最先想到的,就是awr。所以我们先获取了9点到9点15的awr报告,发现执行异常sql为a视图的相关查询
查询对应SQL
通过top命令获取cpu使用率高pid,再追溯到执行的sql语句,定位到a视图相关查询
select a.sid,a.serial#,a.sql_id,a.machine,a.program,a.status,c.sql_text
from gv$session a, gv$process b, gv$sql c
where a.paddr=b.addr and b.spid in (xxx,xxx);
查询并强制杀掉等待事件
查询his库的等待事件,发现大量的latch: buffer cache chains和少量TX – row lock contention等待事件,强制杀掉等待事件的系统进程,暂时缓解了cpu使用率,但过段时间后latch: buffer cache chains再次大量产生。
由于是业务高峰期,若cpu100%,则大量业务不能使用,所以只能一边排查问题,一边观察latch: buffer cache chains等待事件,一旦大量出现,先及时杀掉
select inst_id,event,program,machine,sql_id from gv$session where wait_class <> ‘Idle’;
select 'kill -9 ' || spid from v$process where addr in (select paddr from v$session where event='latch: buffer cache chains'));
关闭引起等待事件的应用
在排查过程中,发现latch: buffer cache chains等待事件基本都是由同一个应用模块发起,这些等待对应的sql基本都是和a视图有关,和客户商量后,决定暂时先关闭该应用。关闭后,latch: buffer cache chains等待事件出现下降。
因为去前面已经定位到应该是视图a的相关查询有问题,所以集中排查该视图,最终发现该视图使用到的5张基表中,只有一张表,a表有统计信息,且收集时间为当天早上8点45分。询问客户,发现今早某维护人员对该表创建了索引并收集了统计信息。
决定先回退表a的统计信息
exec dbms_stats.delete_table_stats(ownname => 'OWNER',tabname => 'A') ;
删掉该表统计信息后,再开启应用,cpu使用率正常。
分析故障
至此故障算是已经解决,下面分析下这次故障中的2个重点因素:
1、latch: buffer cache chains等待事件
该等待事件的出现,通常意味着sql语句读取了它所需要的更多的buffer,并且有许多会话等待读取同一个块。出现的原因有以下2点:
l 不够优化的SQL
l 热点块征用
2、optimizer_mode = choose,采用这个值,表示Oracle及可以采用基于规则的RBO,也可以采用基于成本的CBO,到底采用那种方式,取决于当前SQL中被访问的表中是不是有可以使用的统计信息。
如果有多个表被访问,其中一个或多个有统计信息,那么Oracle会对没有统计信息的表进行采样统计(即不全部采样),统计完成后,使用基于代价的优化方法CBO。如果所有被访问的表都没有统计信息,Oracle就会采用基于规则的优化方法RBO。
表创建索引并收集统计信息,由于视图中的其他4个表都没有收集过统计信息,唯独a表收集了,这导致在查询视图时,Oracle从之前一直使用的RBO方式改成了CBO方式,这导致了sql的执行效率大幅下降,CPU消耗大幅增加,造成了latch: buffer cache chains等待事件突增,进而造成了数据库运行缓慢,业务卡顿。