【云和恩墨大讲堂】SQL玩转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报告。

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

  • @?/rdbms/admin/awrrpt.sql -- 标准报告,特定时间段内总体性能报告
  • @?/rdbms/admin/awrddrpt.sql -- 对比报告,两个时间段内性能对比
  • @?/rdbms/admin/ashrpt.sql -- ASH报告,特定时间段内历史会话性能报告
  • @?/rdbms/admin/awrsqrpt.sql -- SQL报告,特定时间段内SQL性能报告

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混入大量无用数据, 导致生成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,而且是连续的多个时间的数据。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-12-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏码神联盟

高效编程所需要做的那点事

聊聊如果才能高效编程 计划(Plan) 所谓Plan,其实就是对应于编程中的设计阶段,当然,这里的Plan并不像设计那样重量级。它要求我们程序员在正式...

2779
来自专栏Java架构师进阶

Java高级工程师面试总结

原因:面试的第一个问题,一般都是让你简单介绍下你自己,或者介绍一下你最近的项目,而一个面试者,如果连自己的简历都无法熟知,对里面提到的项目、技术都无法描述清楚的...

952
来自专栏腾讯Bugly的专栏

【团队分享】苍翼之刃:论File Descriptor泄漏如何导致Crash?

这一期的团队分享,我们特邀苍翼之刃的开发负责人Jay,为大家分享在Android项目中遇到的一些Crash。 苍翼之刃 外文:BlazBlue Revolut...

5457
来自专栏Java技术栈

爱上 Java 的10 大理由,Python 弱爆了!

Java和JVM已经存在了很长一段时间了,基于这个事实,一些程序员开始将很多事情视为理所当然。今天我们就来说一说“Java之所以能够成为并将继续是软件项目领先平...

1254
来自专栏Golang语言社区

一个调度系统的开发与性能优化

背景:随着Go的不断发展,流行度越来越高,业界对Go的认可度也越来越高,所以很多团队或者公司在遇到性能问题时都会尝试使用Go来重构系统,尤其是云计算领域,大家期...

1561
来自专栏腾讯大数据的专栏

SQL On Storm ——EasyCount系统概述

1. 摘要: TDW很好的解决了海量数据离线处理问题,但是在如下场景下:实时报表,实时监控,实时推荐,实时分析,TDW无法满足需求。而storm是应对这些场景的...

3079
来自专栏码匠的流水账

聊聊系统设计中的trade-off

trade-off翻译过来大致是折中的意思,也就是说系统设计通常牵扯的点比较多,有的设计方案这个方面比较好,但是又有其他缺点,没有十全十美的方案,只是在特定的上...

1653
来自专栏编舟记

架构整洁之道导读(三)

上回说到组件聚合,反映的是组件内部的“基本元素”的选择标准。第14章介绍的组件耦合则是指组件和组件之间的关系,这些依赖关系有些是好的,有些是不好的,我们即将看到...

1293
来自专栏帘卷西风的专栏

开源CEGUI编辑器之二(MFC重写的ImagesetEditor)

转载请注明出处:帘卷西风的专栏(http://blog.csdn.net/ljxfblog)

911
来自专栏微信公众号:Java团长

Java高级工程师面试总结

原因:面试的第一个问题,一般都是让你简单介绍下你自己,或者介绍一下你最近的项目,而一个面试者,如果连自己的简历都无法熟知,对里面提到的项目、技术都无法描述清楚的...

991

扫码关注云+社区