从一个小程序脚本看DB Time

转自 http://blog.itpub.net/17203031/viewspace-776773/

从Oracle 10g开始，Oracle数据库开始进入性能度量收集、自诊断、智能调整阶段。应该说，就数据库本质特性而言，9i奠定了基础结构，之后一直到11g，Oracle的体系结构就没有发生本质性的变化。

各种Oracle10g新特性中，笔者认为比较吸引人的特性是ASMM和AWR基础信息库。ASMM（Automatic Shared Memory Managment）让DBA从设置多大的内存池的纠结问题中解放出来。而AWR就是Statspack的“转正”版本，为调优人员发挥作用奠定基础。

AWR（Automatic Workload Repository）建立在Oracle内部的性能度量基础上。在Oracle运行过程中，我们可以通过很多视图来查看数据库的“real time”情况。例如v$session可以看到数据库会话情况，v$sql和v$sqlarea记录了当前shared pool中缓存SQL语句和执行计划情况，v$sysstat记录了系统启动至今的诸多等待事件信息。而AWR就是以特定的间隔频率（默认为一小时），将其记录保存为一系列的Snapshot，从而便于日后诊断的工具。

使用AWR最方便最快捷的方法就是使用Oracle提供的AWR报告生成接口，它是我们可以找到的性能分析、对比报告。应该看到，虽然AWR提供了数据库特定时间区间详细的数据情况，但是很多个性化的分析需求还是不能满足的。

应该看到，AWR本质上是建立在对性能数据的周期性收集上。对我们而言，AWR基础库是一个非常宝贵的信息资源，用好这个资源，可以帮助我们以更好的方式进行性能分析。一些自定义的工具，也是基于AWR库的基础信息。

但是，AWR报告的生成过程是通过调用数据库自身加密存储过程实现的。很多细节和数据选择规律，都是对我们封闭的。当我们进行数据选取时候，要注意指标的含义和度量，避免选择错误。

 

1、DB Time

问题起源自笔者想进行的一个分析，希望将一周内每个小时（或者每几个）的DB Time抽取出来，形成一个关于DB Time变化趋势的图形，从而查看数据库应用负载变化趋势。

在AWR报告分析中，DB Time是首先需要分析的指标。Elapse Time和DB Time也是AWR初学者需要看的第一个重要分析情况。

所谓Elapse Time，就表示生成AWR报告过程中，两个Snapshot镜像之间的时间差。这个值体现的是性能基准，就是告诉分析人员，选择分析的期间是多长时间。

DB Time的概念应该说是很多的。在AWR中的DB Time表示的前台服务进程调用消耗时间，也就是user-level调用。数据库的消耗包括两个层面，一个是前台应用消耗，就是我们连接的应用，比如应用程序访问的消耗。而后台消耗，就是后台进程background process的工作消耗。DB Time应该说是我们分析应用性能消耗的重要考量指标。

但是，单纯的看待DB Time是不行的。因为数据库并行的存在，多个user process是可以并行的。DB Time在计量所有Process度量的时候，是要和CPU的个数相匹配。比较常用的公式是DB Time和Elapse Time * CPU_Count相对比。如果DB Time接近乘积值，就表示数据库应用负载（注意是应用负载）很高。反之，就表示数据库比较空闲。

 

2、第一版脚本

 

回到问题本身。直接使用AWR报告，肯定不能实现只分析DB Time的目的。所以一个思路是挖掘（mine）AWR基础信息库，从里面抽取出来。

AWR库视图是有特征的，代表是以dba_hist_作为前缀。数据库实时的性能视图，在AWR库中的对应物基本是同名的。

比如我们常用的v$sysstat，对应在AWR库里面就是dba_hist_sysstat。

自定义AWR程序脚本，都需要围绕视图dba_hist_snapshot。这个视图记录了每次进行AWR Snapshot生成的信息。应该说，这个视图是AWR的核心。DB Time信息从哪里找呢？

首先想到的是v$sysstat视图，里面的stat_name以key-value方式记录了很多度量。其中有一个项目就是DB time。

于是，第一版脚本生成了。

select snap_id, snap_time, value, v, (value-v) from(

select snap_id, snap_time, value , (lag(value) over (order by snap_id)) v from

(select sp.snap_id, to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') snap_time, value

from dba_hist_snapshot sp, dba_hist_sysstat sy

where sp.snap_id=sy.snap_id

  and sp.instance_number=sy.instance_number

  and sp.begin_interval_time>=to_timestamp('2013-11-09 00:00:00','YYYY-MM-DD hh24:mi:ss')

  and sp.begin_interval_time<to_timestamp('2013-11-15 00:00:00','YYYY-MM-DD hh24:mi:ss')

  and sy.stat_name='DB time'

  --and mod(sp.snap_id,2)=0

order by sp.snap_id));

 

通过begin_interval_time来定位选取时间片段的范围，通过stat_name确定选取的度量名称。注意，度量中的value记录的是一个累计值，而非期间值。所以使用前后snapshot之间相减的方法进行求差。

 

整理结果片段如下：

 

 

SNAP_ID	SNAP_TIME	VALUE	V	(VALUE-V)/60/1000000
6967	2013/11/9 1:00	740280500	739899753	380747
6968	2013/11/9 2:00	740766773	740280500	486273
6969	2013/11/9 3:00	741324347	740766773	557574
6970	2013/11/9 4:00	741700063	741324347	375716
6971	2013/11/9 5:00	742080734	741700063	380671

 

似乎存在问题。笔者的系统是一个典型的OLAP系统，夜间从2点-3点进行ETL动作。这个时间段的DB Time最多是可以理解。其他时候访问量很少，而且负载不会很高。

结果的时间片中，时间分布比较均匀。虽然在高负载时间段中有起伏，但是起伏幅度不够。

另一个层面，就是度量。根据官方资料介绍，DB Time对应的是microsecond，也就是百万分之一秒。无论如何高峰时间也不能对应上这个取值。

之后，笔者选取了一个小时的snapshot，生成AWR正式报告。看到提取的时间。

 

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	7111	15-11月-13 02:00:36	50	4.0
End Snap:	7112	15-11月-13 03:00:40	60	3.4
Elapsed:		60.07 (mins)
DB Time:		17.03 (mins)

 

一小时内运行的DB time为17分钟。而对应的snapshot信息很难对应到17分钟。

 

SNAP_ID	SNAP_TIME	VALUE	V	(VALUE-V)
7111	2013/11/15 1:00	797983510
7112	2013/11/15 2:00	798476477	797983510	492967

 

 

3、第二个脚本

 问题可能出现的是两个方面，一个是笔者选取的视图位置有问题。另一个笔者对指标取值的分析解析错误。

后者可能性不大。虽然AWR解析资料库是在后台加密情况下完成，但是特殊的解析算法没有过多的意义。

视图位置问题，只有一种可能：就是AWR选择这个DB Time而非v$sysstat中的DB Time。

在度量Oracle过程中，方法论也有很多流派过程。从最早的Hit Rate，到现在的Event Based，相同的数据库度量概念，曾经被赋予不同的度量含义。

Oracle曾经提出过一个system time model的概念，基于视图sys_time_model。笔者猜想是不是AWR选取的是这个模型里面取值呢？

SQL> desc dba_hist_sys_time_model

Name            Type         Nullable Default Comments

--------------- ------------ -------- ------- --------

SNAP_ID         NUMBER                                

DBID            NUMBER                                

INSTANCE_NUMBER NUMBER                                 

STAT_ID         NUMBER                                

STAT_NAME       VARCHAR2(64)                          

VALUE           NUMBER       Y      

修改的第二个版本脚本。

select snap_id, snap_time, value, v, (value-v)/60/1000000 from(

select snap_id, snap_time, value , (lag(value) over (order by snap_id)) v from

(select sp.snap_id, to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') snap_time, value

from dba_hist_snapshot sp, dba_hist_sys_time_model sy

where sp.snap_id=sy.snap_id

  and sp.instance_number=sy.instance_number

  and sp.snap_id in (7111, 7112)

  and sy.stat_name='DB time' 

order by sp.snap_id));

 结果： 

SNAP_ID	SNAP_TIME	VALUE	V	(VALUE-V)/60/1000000
7111	2013/11/15 1:00	3.24849E+11
7112	2013/11/15 2:00	3.25871E+11	3.24849E+11	17.02656

 

出现期望的17.02分钟。对应的转换公式也是百万分之一秒转换。

 

4、结论

问题解决了。有几个方面需要额外说明：

首先，使用db_hist_sysstat不适应的情况，目前只发现在DB Time中存在。其他经常统计的指标，如redo size、hard parse (count)之类的，依然可以使用这个视图。

另外，db_hist_sysstat中的DB Time，笔者认为是一个累计时间，也就是前后台进程累积的度量情况。而非我们目前已经接受的前台应用时间。