【云和恩墨大讲堂】SQL玩转AWR裸数据

编辑手记：Oracle线上嘉年华第四讲，SQL玩转AWR裸数据，教你真正利用AWR中的裸数据对系统性能进行分析并调优。

作者简介：

罗海雄

云和恩墨优化专家

ITPUB论坛数据库管理版版主，2012 ITPUB全国SQL大赛冠军得主，他还是资深的架构师和性能优化专家，对 SQL 优化和理解尤其深入；从开发到性能管理，他有着超过10年的企业级系统设计和优化经验。曾经服务于甲骨文公司，组织和主讲过多次《甲骨文技术开发人员日》和《Oracle圆桌会议》，并具备丰富的制造行业系统架构经验。

今天的主题是SQL玩转AWR裸数据。 

AWR相信DBA们都不陌生。Automatic Workload Repository，自动负载信息库，就是Oracle把数据库中比较重要的性能视图里的信息，定期从内存保存到数据库里面。默认情况下，Oracle 会每个一个小时保存一次。另外，Oracle也有机制保证信息库的大小不至于无限增长，所以一般信息库只保留7天的数据。默认是保存在SYSAUX表空间中。主要的信息涵盖了Oracle较重要的性能相关信息。

底层存储为WRH$* 表， 通常可通过DBA_HIST_*访问，总共有100多张表。

对于AWR, 常规的用法是生成AWR或者ASH报告。

下面列举了几个最常见的方法。

首先，AWR反应的是点对点的数据。比如说，我生成一个今天9:00到12:00的AWR报告，那么，我看到的，就是12:00和9:00两个时间点的变化。但是，9:00-10:00, 10:00-11:00,11:-12:00 分别是什么样的，我们看不到。

AWR混入大量无用数据, 导致生成AWR报告需要30秒到几分钟的时间，所以，如果我们有裸数据，其实可以更高效，更深入的挖掘Oracle数据库的性能信息。在正式接触裸数据前，我们需要先了解AWR在数据库里存的是什么样的数据。

在裸数据里面，记录的各种指标主要有4类

最多的一种是"累计值"

举个例子 dba_hist_sysstat 里会记录数据库的逻辑读。记录的不是这一个小时产生的逻辑读，而是从数据库启动到产生快照的时候的总的逻辑读。这就叫累计值，大多数的指标的是累计值。

也有部分数据记录的是"当前值"

比如说，数据库当前的PGA使用量，数据库的会话数等，还有比较特殊的，会记录两次快照之间的变化值。我们可以认为，这是一种预计算，最常见的记录变化值的两类数据，分别是SQL相关统计信息，以及段(segment)相关统计信息，当然，SQL/Segment记录变化值的同时，也记录了累计值。

还有一类，记录的是”统计值“

就是把一段时间内的数据，做了统计之后保存了起来，这些主要是METRIC类的数据。比如说，每秒CPU, 每秒最大等待时间等。

对于DBA来说，最关心的一般是变化值，

两次快照之间的变化量。这是一个简单的SQL, 获取数据库的历史性能信息里的redo size 信息

select SNAP_ID,STAT_NAME,VALUE from DBA_HIST_SYSSTAT

where STAT_NAME=‘redo size’ order by snap_id;

我们现在看到的，就是累计值。那么，怎么方便的获取变化值呢？

1、要取得变化值，需要取出后面的记录，减去前面的记录。

如果仅仅是两个时间点，最简单的方法就是访问这个表两次，然后相减。 

select a.value - b.value from DBA_HIST_SYSSTAT A,DBA_HIST_SYSSTAT B

where A.STAT_NAME=‘redo size’and A.STAT_NAME = B.STAT_NAME and a.snap_id = 123 and b.snap_id = 122

这样得到是两个点之间的差值，但是对我们来说，玩玩是不够的。

2、有时候，我们希望得到一个时间段内，每两个连续快照之间的变化值。比如说，9:00-21:00, 我们希望获得 9:00-10:00, 10:-11:00... 20:00-21:00, 每个时间段分别的变化值。

这里就涉及到Oracle的分析函数了分析函数

Oracle的分析函数提供了在一个结果集内，跨行访问数据的能力。分析函数里面的LEAD/LAG正是跨行获取数据的利器

LAG : 同一组内，排在当前行之前的数据

LEAD : 同一组内，排在当前行之后的数据

如图所示，可以看到，我们要的是拿当前value 减去 lag value。

select snap_id,stat_name,

 value-lag(value)  over

(partition by stat_name order by snap_id)

  from dba_hist_sysstat

 where stat_name = redo size

  order by snap_id;

这就是分析函数LAG的完整语法。

3、我们一般不会满足获取一个指标的变化值的，下面的表，才是我们希望获得的。

这里又引入了进阶SQL的另一个写法：行列转换。

具体我就不细讲了，大家可以体会一下，如何使用sum(case when .. then .. end )或者max(case when .. then .. end )的形式的形式来进行行列转换

，但用Case when来写行列转换，很容易使SQL冗长，而且容易出错。

Oracle 11g中，提供了更方便的方式进行行列转换

大家可以看到，标黄大写的PIVOT， 正是Oracle 11g中引入的行列转换利器。使用PIVOT, 增减指标极其简单：

很轻松就加了两个指标，如果觉得列名不好看，也可以自己指定。

其实，我们可以很轻松的就把AWR报告中的"Load Profile"部分通过行列转换给取出来，而且，是多个连续变化的值。

把跑的结果拷到Excel, 很容易就出来一个漂亮的趋势图。

但是，这个图是有问题的：图里的REDO Size是以byte为单位的，值太大，把别的指标统统压到和0差不多，多个指标要到同一个图，还能看出各自的趋势，对于多指标关联的分析很有作用。

这时候，又有一个分析函数出来了。没错，因为我们是在对Oracle的性能数据进行分析，所以，需要大量的使用”分析函数“

分析函数： Ratio_To_Report 求当前行数据在所有同组数据内占的比例。比如说，我的结果集里有3行，分别是1,3,6. 那么1对应的那一行，占总数据(1+3+6)的10%, 出来的结果就是0.1（10%）.

select * from (

 select snaptime,RATIO_TO_REPORT(value) over(partition by stat_name) value,stat_name,snap_id 

 from (… )) PIVOT (sum(value) for stat_name in (

…))order by snap_id;

在这个图里面，大家就都平等了，也更方便的去看各个指标之间是否存在关联

再给大家看另一个SQL, 还是ratio_to_report, 这次，我们拿到的结果，其实是AWR报告里另一个非常重要的数据:Top Timed Events

我把每个时间段的CPU时间和非空闲事件给放在一起，然后计算每个事件(含CPU)在每个时间段占的百分比，就得到 Top Timed Events，而且是连续的多个时间的数据。

今天的分享到此结束，更多精彩，尽在Oracle线上嘉年华。敬请关注！

文章转自数据和云公众号，原文链接

【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组 本篇文章讲解的主要内容是：***如何使用lag函数让结果集重复数据只显示一次、用行转列pivot写法优化部门之间计算工资差异类似需求、如何通过ceil函数对已有数据进行分组打印、放假安排团队分组值班，如何通过ntile()over(order by )快速进行人员分组***
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高 怎样对数据组合重新排列并去重的问题、通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本篇文章主要介绍的两个方面，第一个方面曾经有好几个网友和同事问我，第二个问题真的是很多同行的通病，认为分析函数是万金油，一股脑用。
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据 如何识别重叠的日期范围、日期出现次数、确定当前记录和下一条记录之间相差的天数【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据 本篇文章讲解的主要内容是：***汇总报表时常要求按季度分类汇总这就需要通过给定年份获取对应的季度开始结束时间、业务数据不连续的情况下如何统计所有年份数据、如何统计相同月份与周内日期聘用的员工、如何返回2月或12月聘用的所有员工以及周二聘用的所有员工***
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report 本篇文章讲解的主要内容是：***计算部门中那个工资等级的员工最多、通过返回部门10最大工资所在行的员工名称小案例来讲解max/min() keep() over()、通过查询工资最高的人小案例来介绍fisrt_value、last_value、通过计算各个部门的工资合计以及各个部门合计工资占总工资的比例小案例来介绍如何计算百分比及ratio_to_report分析函数的使用***
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表 插入数据、阻止对某几列插入的实现、复制表的定义和数据以及注意事项、比创建约束功能更强大！用 WITH CHECK OPTION限制数据录入、如何一个insert将数据同时插入多个表【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。但是！ORA - 01723 : 不允许长度为 0 的列。
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起 本篇文章讲解的主要内容是：***如果有重复数据如何检查出两个表中的差异数据及对应条数、表连接做聚合容易出现重复计算的错误、多表查询空值处理问题、NOT IN的子查询范围不能是空值，否则查询结果为空。***
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载