借助AWR报告分析解决oracleCPU过高的问题

mahan

简介：
在oracle数据库中，有两个非常实用的自带监控工具EM（Enterprise Manager）和AWR（Automatic Workload Repository）。其中，通过AWR报告可以生成易于阅读的监控报告，可协助进行性能问题的诊断和分析。
本期将介绍一些AWR报告的基本使用和在性能问题诊断中的应用。
什么是AWR：
      从oracle 10g开始，使用名为GATHER_STATS_JOB的计划任务来收集AWR的统计数据，AWR报告中收集了许多不同的统计数据，包括等待事件、各种系统级和会话级的统计数据及使用较多资源的SQL语句相关的信息。
    在默认情况下，Oracle数据库后台进程会以一定间隔（一小时）收集系统当前状态镜像，并且保存在数据库中，默认的保留时间是8天，此策略可根据实际情况进行调整。在特殊的时间点，如测试执行前后，可以通过手动创建快照的方式来增加快照。在生成AWR报告时，通过快照点来选择生成指定时间段的统计报告。
AWR的生成：
一、手动产生AWR报告
1）在Oracle数据库服务器上，用SYSDBA身份登入sqlplus。
2）运行脚本
在sqlplus中输入awrrpt执行命令。
@?/rdbms/admin/awrrpt.sql
3）输入报告参数
a) 选择要生成AWR报告的类型，可选择text类型和html类型，输入html（默认）。
b)报告涉及天数范围
选择要生成报告的日期是多少天的记录。输入天数后，（输入1，则表示要生成今天0点开始到目前的时间段内的报告；输入2，则表示要生成昨天0点开始到现在的时间段内的报告；以此类推。）界面显示出改时间段的数据表格，每个时间点对应一个snap id，间隔时间oracle默认为1小时。
c）输入要生成报告的开始和结束snap编号。
d）确定报告名称。
不输入直接回车，系统会自动生成一个默认名字的报告。
e)生成报告。
提示报告输出结束后，AWR报告存放在指定目录（用sqlplus登录oracle数据库的目录）下 。
案例分析：
现象：
某交易单场景，发现数据库CPU平均使用率达近90%。本案例数据库为12Core，从Instance CPU上看，oracle占用的CPU占服务器中CPU资源（89.9%）的99.5%。

分析过程：
    为了定位数据库CPU过高的问题，测试过程中使用AWR报告，观察数据库的使用情况：
1）先整体查看报告头信息（header）和性能指标（Buffer Nowait，Buffer Hit，Library Hit，Execute to Parse，Parse CPU to Parse Elapsd，Redo NoWait ）。
2）看Time Model Statistics，在整个sql执行时间（sql execute elapsed time）时间为15499秒中，解析时间（parse time elapsed）用了26秒，硬解析时间（hard parse elapsed time）用了26 秒，可知解析时间是花的很少的，所以可以判断出，sql的解析没有成为性能瓶颈，进一步推测，sql在获取数据的过程中遇到了瓶颈。
4）关注下负载的概要信息。数据库层面的TPS仅为1.3，TPS较低。
5）进一步分析发现，从SQL ordered by CPU Time来看，SQL id 为cjnzff37c9gr3和cq32agu0qkpd8的SQL语句，执行次数多，耗CPU较高。
6）针对这2个SQL语句，执行命令，获取sql 执行计划。（需输入SQL id）
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
7）通过查看执行计划，对CTR_LOAN_CONT的表读取占用的cost较多，需要根据索引读取196000行数据。其中CTR_LOAN_CONT表为1200多万条数据，占用空间5888m，统计更新也较准确。部分计划如下：

8）查看 Segments by Logical Reads，96%的逻辑读都为CTR_LOAN_CONT 表的读取。最终定位为，数据库CPU过高为SQL导致。

处理方法：
（1）转移数据库服务器的一部分逻辑处理到应用服务器。即将数据库中的一些通过SQL判断的逻辑通过应用代码进行实现，从而适当的降低数据库服务器的复杂计算。
（2）针对XX模块仍然采用视图，但在视图中添加第三方平台作为条件过滤，并将归并方法UNION（表链接后筛选掉重复的记录再排序）改为UNION ALL（无排序去重）。
调优后效果:
（1）CTR_LOAN_CONT的表读取占用的cost从15359降低到276。
（2）数据库服务器的CPU平均使用率从89.9%降低到11.6%。