一、背景

由于行内某交易系统需要进行版本更新，为保证新版本上线后可以达到最优效果，根据需求，对该系统的Oracle数据库进行了一次深入诊断、分析和优化。在分析过程中，果然发现了一些由于SGA设置问题导致的系统运行风险。经过简单的优化，问题最终得以顺利解决。本文对整个分析排查过程、解决方案及最终效果进行了简单的描述和记录。

二、分析过程

数据库在日常没有发现性能问题时只需进行一些常规检查，日常进行的常规检查主要包括以下操作：

空间查看：

df -h

内存查看：

内存使用情况：

free -g
大页使用情况：

cat /proc/meminfo|grep Huge

内存页表使用情况： cat /proc/meminfo|grep Table

CPU查看：

cat /proc/cpuinfo

主机动态性能查看：

vmstat 1

检查后发现，除主机动态性能外，其他检查项均正常，主机动态性能的问题为输出结果中wa比较高；一般来讲vmstat中wa为0，说明压力正常；当该值较大时，说明io等待比较严重，这可能是磁盘大量随机访问造成的，也有可能是磁盘的带宽出现了瓶颈。

该主机上仅运行了该交易系统的数据库，因此造成大量io访问的也只有可能是db。进一步到数据库中进行分析，查看此时数据库的等待事件：

select event,sql_id,count(*) from v$session group by event,sql_id order by 3;

此时，并没有发现数据库有严重的等待，看起来较为异常的仅有一个read by other session和gc的等待事件，对相关事件的sql进行分析，可以发现该sql如下：

该sql是应用程序框架直接生成，对该sql进行分析，发现其执行计划看似合理，但其实并不好，mct_id虽然走了索引范围扫描，但是返回的行数都是在30w以上，也就是说这个条件的筛选率并不好（这里我个人认为，这个操作和全表扫描没什么太大区别）。

经过查询，发现该表还是比较大，整个数据有大概2000w。此时，从表的数量、执行计划来看，我们不难判断该sql肯定会造成大量的逻辑读，而在Oracle集群中，大量的逻辑读产生read by other session和gc等待的现象并不奇怪。

但是需要注意的是，数据库中除了这个sql，并没有其他的异常等待事件，而大量的逻辑读，一般是从cache中读取数据，cache中读取数据也不会造成严重的iowait才对。所以，很有可能造成严重的iowait另有其因。

为了进一步确认问题，生成一个awr对数据库在这个时间段的整体情况进行分析：

从DB TIME上来看，整个数据库的压力并不大，看起来并没有什么瓶颈。但是继续看load profile和instance efficiency percentages就可以发现有两个明显的问题，即physical read和buffer hit；从awr中明显可以看到，此时数据库的物理读比较大(9000+块/秒)，而buffer hit仅有80%。

很明显，buffer的命中率过低。该值过低则说明我们需要查询的数据，在buffer中可能并没有。此时，数据库则会到磁盘中对数据进行读取。因此，也会造成此时数据库的物理读非常大。

此时，第一个想法就是看看数据库的内存分配情况是否出现不合理，导致大部分的数据无法缓存到内存中。继续从awr的内存部分进行查看：

发现SGA的大小为4G。而在最开始检查该数据库主机分配的内存大小为16G。可以看出来，SGA分配的比例有确实有点小，仅占整个主机的1/4。

三、原因说明

根据上述的排查过程，其实可以明显发现，在该系统中目前有四个个问题比较明显：

1.buffer hit命中率较低；

2.物理读较高；

3.存在sql运行效率差；

4.系统的iowait略高；

结合四个问题，基本上可以得出下述结论：

由于数据库的SGA分配过小，数据库中存在一些运行效率不高的sql，导致客户端需要从数据库中返回大量的数据。数据库中获取数据需要先从buffer中读取数据(此时，会产生逻辑读)，如果buffer中无法找到数据，则会到磁盘上读取数据(此时，产生物理读)。因此，不仅产生了大量的逻辑读，也产生了大量的物理读，而buffer的命中率也由于buffer中无法缓存更多的数据导致buffer hit很低。

另外，我们从生成的sqlrpt中也可以看到，此时sql的逻辑读和物理读都非常大：

由于产生了大量的物理读，那么就会产生大量的io消耗；该系统磁盘为stata盘，磁盘读写性能也有限，故最终造成了高iowait。

四、优化处理

了解原因后，针对该系统的优化处理就相对简单，主要可以分为两种方式：

1.增加缓存大小，让数据库可以缓存更多数据；

2.优化sql语句，减少数据读取量；

针对第一点，由于主机分配的内存为16G，那么我们可以加大SGA（比较保守的情况下，我们将SGA的大小从4G增加到8G）；

而针对于第二点，由于sql是应用程序生成，应用方修改有些难度，那么我们则建议将该表用做分区表，从而减小数据获取量；

五、优化效果

由于该系统是行内交易类的生产系统，修改分区表结构，属于重大变更，没有经过测试环境无法直接上生产，所以最终就能优化SGA。

不过仅仅优化完SGA后完成后，整体效果也还是非常明显的。在第二天的同一时间点，数据库连接的session更多的情况下，数据库的db_time提升了2倍多，数据库物理读从9000+下降至1000+，buffer hit的命中率从80%提升到99%。而主机中的iowait也基本消失。

墨天轮原文链接：https://www.modb.pro/db/74414（复制到浏览器或者点击“阅读原文”立即查看）

END

记一次高io wait问题分析及解决-设置合理的SGA

一、背景

二、分析过程

三、原因说明

四、优化处理

五、优化效果

相关推荐