oracle awr dbcpu 高,借助AWR报告分析解决oracleCPU过高的问题

简介：

在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%。