<< 返回文章列表

SQL玩转AWR裸数据

2017年12月22日
罗海雄
1690

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

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

image.png

底层存储为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;

image.png

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

image.png

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 : 同一组内,排在当前行之后的数据

image.png

如图所示,可以看到,我们要的是拿当前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、我们一般不会满足获取一个指标的变化值的,下面的表,才是我们希望获得的。

image.png

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

image.png

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

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


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

image.png

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

image.png

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

image.png

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

image.png

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

image.png


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


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

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

image.png

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;

image.png

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

image.png

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


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

image.png

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