【云和恩墨大讲堂】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 条评论
登录 后参与评论

相关文章

来自专栏编程札记

MongoDB替换MySQL?

早在上个世纪就有什么面向对象数据库等非关系型数据库扬言要替代关系型数据库,但是到现在,那些数据库都默默无闻,关系型数据库却依旧是应用最广泛的数据库,为什么?

3445
来自专栏携程技术中心

干货 | 分布式架构系统生成全局唯一序列号的一个思路

作者简介 丁宜人,10年java开发经验。携程技术中心基础业务研发部用户中心资深java工程师,负责携程账号的基础服务和相关框架组件研发。之前在惠普公司供职6年...

39410
来自专栏PHP技术

mongodb与mysql相比的优缺点

与关系型数据库相比,MongoDB的优点: ①弱一致性(最终一致),更能保证用户的访问速度: 举例来说,在 传统的关系型数据库中,一个COUNT类型的操作会锁...

4535
来自专栏架构师之路

58同城mysql实战(纯干货)

《大数据量下,58同城mysql实践》 WOT(World Of Tech)2015,互联网运维与开发者大会将在北京举行,会上58同城将分享《大数据量下,58同...

4405
来自专栏Golang语言社区

高并发服务器的设计--缓存的设计

为什么需要缓存呢? 很简单的道理,拿QQ做个比方,每天有几亿用户登录、查询个人信息,且这些信息基本不会变化,如果你是架构师,你会选择全部从数据库中查询么,估计会...

34110
来自专栏数据和云

经典文档:Oracle Database 12.2新特性概览解读下载

在2017 OOW大会上,关于Oracle Database 12.2 数据库的新特性介绍仍然引人瞩目,会后公布了 Oracle VP Swonger的文档,我...

2957
来自专栏量化投资与机器学习

战斗民族开源神器ClickHouse:一款适合于构建量化回测研究系统的高性能列式数据库(一)

编辑部原创 编译:wally21st、 西西 未经允许,不得转载 对于一些私募、投资机构和个人来说,量化投资研究、回测离不开数据的支持。当数据量达到一定数量,如...

3738
来自专栏智能大石头

大数据分析中使用关系型数据库的关键点

相当一部分大数据分析处理的原始数据来自关系型数据库,处理结果也存放在关系型数据库中。原因在于超过99%的软件系统采用传统的关系型数据库,大家对它们很熟悉,用起来...

644
来自专栏程序员阿凯

Java面试通关要点汇总集 核心篇

964
来自专栏代码世界

初识数据库

一、数据库概念 数据库(Database,简称DB)   数据库技术是计算机应用领域中非常重要的技术,它产生于20世纪60年代末,是数据管理的最新技术,也是软件...

3407

扫码关注云+社区