使用AWR报告诊断Oracle性能问题

在做单交易负载测试时，有的交易响应时间超出了指标值，在排除完测试环境等可能造成交易超时的原因后，去分析数据库问题。数据库用的是Oracle，对于Oracle数据库整体的性能问题， awr的报告是一个非常有用的诊断工具，于是采用Oracle自带的性能分析工具awr进行监控分析。

生成awr报告

1、   以sysdba登录数据库：sqlplus / as sysdba，如下图所示： 

2、   在负载测试执行前后分别取一个快照：exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT，如下图所示：

3、   压力测试执行完后，执行awrrpt命令（@?/rdbms/admin/awrrpt）获取awr报告，并且输入报告类型为HTML（awr报告类型有html和txt两种），如下图所示： 

4、   列出awr报告生成的时间段（1表示1天内的），如下图所示：

5、   输入开始和结束的snap id。如果不指定什么时候生成快照，则默认每隔1个小时生成一次。前面输入的时间段为1天，则会从当天0点开始，每隔1个小时生成一个快照，如下图示：

6、   接下来提示输入awr报告的名称，输入完名称之后，点击“file-Log Session”将报告保存到本地，再回车，就会生成相应的报告，如下图所示：

7、   awr报告生成完成后，双击打开即可查看awr报告记录的详细信息。

一、  awr定位问题

1、   Load Profile：了解系统整体负载情况，如每秒钟事务数，每秒物理读写次数，每秒逻辑读写次数，SQL语句的解析，特别是硬解析数等。

 

2、   Instance Efficiency Percentages (Target 100%)：除了Execute to Parse %（70%以上）和Parse CPU to Parse Elapsd，其他的各指标都应该接近100%。如果不符合则基本上可以确定系统存在性能问题，但即使符合也不能说明系统完全正常。

3、   Top 10 Foreground Events by Total Wait Time：这里列出了消耗时间最多的10个等待事件，每种事件都表示一种原因。

 

1)       Log file sync过高问题。如果log file sync等待时间很长，超过5ms，一般它的等待时间在5ms以下。分析发现是commit请求过多，导致请求堆积在log buffer所致，修改脚本将commit请求降低，问题得到解决。总结Log file sync过高原因：

a)      硬件磁盘老化

b)      commit请求过多

c)      log buffer设置过小

2)      direct path read。direct path read就是不经过Cache，直接到磁盘上去读。而且这种全表扫描还会导致的现象就是tps的抖动、响应超时。总结导致超时原因：

a)      全表扫描

b)      SQL语句中有排序(order by等) 

 接下来在main report中点击SQL statistics，之后会显示SQL ordered by Elapsed Time，这是SQL语句的执行时间列表，如下图所示：

 

从上图可以看出，第一条sql语句的执行时间很长，所以值得怀疑，点开它的sql id，就会显示对应的sql语句，然后在plsql上执行这条sql语句，查看这条语句的执行计划，从而判断是否为全表扫描。

 

通过查看该sql的执行计划，发现为全表扫描（TABLE ACCESSS FULL）,全表扫描是数据库服务器用来搜寻表的每一条记录的过程，直到所有符合给定条件的记录返回为止。

为该sql语句的cst_accno字段添加索引，如下图示：

 

重新跑负载测试，这时该交易响应时间达标。