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

相关文章

来自专栏大数据

快速数据管道设计:通过交换表更新各个事件决策

在 VoltDB(这是一种数据库) 经常使用到的术语,快速数据管道(Fast data pipeline),这是一种全新的现代应用程序 —— 这种应用程序将流式...

1697
来自专栏媒矿工厂

视频编码的GPU加速

前言 随着视频编解码技术的不断发展,视频逐步向着高清晰、高动态、高数据量的方向演进。这对视频编解码终端的计算能力提出了越来越高的要求。同时,在GPU领域,随着C...

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

SQL On Storm ——EasyCount系统概述

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

2449
来自专栏java工会

学java就两个问题

1638
来自专栏HappenLee的技术杂谈

大数据小视角3:CarbonData,来自华为的中国力量

对于OLAP查询来说,存在多种不同类型的查询,存储结构的不同会影响到不同查询的数据表现。所以CarbonData的定位是作为一种通用的查询存储数据,通过Spar...

663
来自专栏weixuqin 的专栏

计算机组成原理学习笔记(一)

2875
来自专栏精讲JAVA

5 本最佳的 Java 面向对象理论和设计模式的书籍

对于Java程序员来说,掌握面向对象的设计理论和一些设计模式是必备技能。就像我在另一篇博客Java程序员应该知道的10个面向对象理论中提到的,不学理论就开始编程...

1244
来自专栏数据库新发现

一道面试题引出的系列数据库性能,数据安全问题及解决方案

友情链接:http://enmotech.com/web/classify/43.html

552
来自专栏数据库新发现

数据库性能和数据库安全:面试题上以小见大

友情链接:http://enmotech.com/web/classify/43.html

630
来自专栏逍遥剑客的游戏开发

面向对象vs面向数据

1274

扫描关注云+社区