Oracle数据库低效语句监控定位的方法研究

李俊炜

0 引言

随着商业银行应用项目规模的扩展，Oracle数据库在开放平台应用项目中的使用愈来愈广，存储着大部分的平台侧关键应用数据。应用数量不断膨胀的结果是伴随着需要管理的数据库套数日趋庞大，并且涵盖各个业务模块，其管理难度与复杂性也与日俱增。

笔者作为 Oracle数据库的管理员，在日常工作中经常需要应急处理各类数据库突发状况，通过对近年来应急事件的归类分析，发现，因SQL语句效率问题引发系统性能下降，进而影响正常业务交易的情况，占了各类生产问题的50%以上。虽然专业组也研究总结了多种方法来发现数据库性能变化，比如对数据库的等待事件进行监控、对进程的等待事件时间进行监控等，但是这些监控，往往是效率问题达到一定程度后才能发现，而通常到了那个时候，已经对业务产生了实际影响。为此，亟需一种方法，能够在低效率语句尚未给系统造成实际影响前发现异常状况，快速发现问题根源。

1 DB TIME及ELAPSED TIME监控构想的提出

笔者通过对Oracle 10g在线文档的查阅，以使用Oracle 10g之后的时间统计模型作为突破口，结合多年Oracle数据库管理经验，总结归纳了针对Oracle 10g以上版本，“通过对数据库DB TIME及SQL语句ELAPSED TIME的监控及与历史情况对比”的方法。通过生产实际应用检验，该方法可以切实有效地提前发现数据库运行效率异常，并且快速定位造成数据库运行效率异常的问题语句。

本文通过对该方法的原理、实现方式的阐述，对“如何在低效率语句尚未给系统造成实际影响前发现异常状况，如何快速定位发现问题语句”问题给出了一个具有可操作性的方案，并对该监控的后续研究方向提出设想与展望。

2 DB TIME与ELAPSED TIME定义及监控原理

2.1 DB TIME与ELAPSED TIME定义

Oracle 10g起引入了基于时间统计信息（time model statistics）的调优模型。根据Oracle 10g官方在线文档说明，DB TIME是Oracle 10g版本之后的一个重要时间统计模型指标，是数据库实际处理用户级别（不包括后台进程的CPU和非空闲等待时间）各类操作耗时的一个总计数，包含了系统的CPU运行时间及进程的等待时间，用以直观地显示整个系统的运行负载状况。DB TIME在数据库实例启动之后，由0开始计数累加，存储于V$SYS_TIME_MODEL视图中。如果TIMED_STATISTICS 参数未开启，则该数值则一直为0。此外，Oracle每隔1个小时都会对数据库状态保存一个快照，这一时刻的 DB TIME数值会保存在DBA_HIST_SYS_TIME_MODEL中。两个时间点DB TIME数值的差值从某种意义上，也可以表示数据库的繁忙程度。为便于理解，文章后续将采用DB_TIME直接代表两个时间点DB TIME数值的差值。

ELAPSED TIME是SQL从解析、执行直至获取结果的整体时间，包含了非空闲等待的时间。通常情况下，一段时间内所有游标的ELAPSED TIME总和近似于该时间段内数据库的 DB TIME。ELAPSED TIME可以从数据库V$SQLSTAT中获取，而同DB TIME一样，对于SQL语句执行的情况每小时也会有一个快照，记录语句的历史执行情况，保存在 DBA_HIST_SQLSTAT视图中。其中就包括了ELAPSED TIME信息。同一语句在两个时间点 ELAPSED TIME的差值可以认为是语句执行效率的一个直观体现。为便于理解，文章后续采用ELAPSED_TIME直接代表同一语句两个时间点ELAPSED TIME数值的差值。

2.2 DB_TIME与ELAPSED_TIME监控实现原理

通过对生产数据库的观察，很容易发现如下两个现象：

1) 对于一个稳定的数据库，在一天的不同时段可能因为业务量的原因而导致单位时间内DB_TIME相差较远，但是相对于昨天的同一时间段，DB_TIME的差别就可能比较小。更进一步，相比较于前一周相同时间段（同一星期、同一时间），单位时间的DB_TIME差别则会更小。

2) 对于一个稳定的数据库中的某一条语句，若其执行计划没有发生改变，且涉及的数据量没有发生巨大的改变，那么语句的ELAPSED_TIME也应该是基本一致的。

“通过对数据库DB TIME及SQL语句ELAPSE TIME的监控及与历史情况对比”(以下简称“DB_TIME监控”)，正是基于以上两个现象而实现的对数据库运行情况的监控方式，它主要分为4个步骤：

a.每天定时从数据库视图（DBA_HIST_SYS_TIME_MODEL、DBA_HIST_SQLSTAT）中获取数据库DB TIME数值与语句ELAPSED TIME数值的历史信息，存入临时数据库；

b.通过一定规则对所获的历史数据进行解析变形，分别获取不同时间段的DB TIME（对应每周7天的24小时，拆分成24*7个以小时为单位的DB_TIME基线）和每条语句不同执行计划下的执行情况（以获取语句最优的执行计划）；

c.每间隔一段时间即从生产数据库中获取当前的 DB TIME数值与语句ELAPSED TIME值，将获得的DB TIME数值与前一次DB TIME数值相减后，即获得这段时间内数据库的实际 DB_TIME，将之与先前获得的历史 DB_TIME基线进行对比，超过一定的比例则可判定该数据库运行状态存在异常。对于状态异常的数据库，再对语句ELAPSED_TIME进行解析处理，筛选出DB TIME占比较高的语句，进一步分析语句执行计划，是否为前期获得的最优的执行计划，并通过一定规则筛选后将造成DB_TIME增高的语句信息通过报警平台报出，由数据库管理员判断应对措施。整个实现流程，如图1所示：

图1 DB_TIME监控报警流程图

以下章节将重点对生产上 DB TIME与 ELAPSED TIME监控实现的具体技术细节进行介绍，并结合生产实际应用情况阐述其应用价值。

3 DB_TIME监控在生产上的实现

DB_TIME监控已在生产数据库环境实施超过一年，并且取得了一定成效。本章将对DB_TIME监控的具体技术细节进行介绍。

3.1 历史信息的收集

鉴于 oracle数据库默认将历史运行信息保存在 AWR（automatic workload repository）中，以视图的形式展现给用户查询，故在实际生产中，每天2点收集生产数据库历史视图中DB TIME与ELAPSED TIME的信息，并保留一定时间，以作为基线生成样本。考虑到应用版本更新频度，DB TIME与ELAPSED TIME数据并分别保留35天与62天即可满足需求。为保证后续基线生成的效率，数据插入后还需要清理掉35天以前的DB TIME历史数据与62天以前的SEAPSED TIME历史数据，而以下4个步骤可以实现这一目的。

1) 在集中性能数据库maintain_user用户下创建对应的数据表hist_sys_time_model，通过db link链接到远程数据库收集各个需要监控的数据库相关 DB TIME的历史数据，并保存到表 HIST_SYS_TIME_MODEL_35中。

2) 收集 ELAPSED TIME历史数据，并保存到表HIST_SQLSTAT_62中。

3) 删除HIST_SYS_TIME_MODEL_35表中35天前的DB TIME历史数据。

4) 4：删除HIST_SQLSTAT_62表中62天前的ELAPSED TIME历史数据。

3.2 生成历史数据解析变形与基线

由于获得的数据仅仅是生产数据历史各个时间点的DB TIME和ELAPSED TIME的计数数值，无法直接作为基线数据进行判断，因此需要对这些历史数据进行解析变形，生成切实可用的基线。通过以下两个步骤可以达到对历史数据解析变形的目的。

1) 生成DB_TIME基线并插入到BL_HIST_DB_TIME表中，实现对执行出错问题的监控，并生成策略执行的统计报表。

2) 生成 SQL语句 ELAPSED_TIME基线，并插入BL_SQL_TIME_READS表中。

3) 3.3 DB TIME、ELAPSED TIME计数的收集与基线比较

4) 在生成DB TIME基线与ELAPSED TIME基线后，基线的准备工作便告一段落，紧接着需要实现的即是对生产数据库即时DB TIME收集。

5) 通过语句实现将即时 DB TIME计数插入表DB_TIME_CHECK中。

6) 通过语句实现将即时ELAPSED_TIME计数大于30秒的语句信息插入SQLSTATS_NOW表中。

7) 完成DB TIME收集后，将与之前的DB TIME数值相减后即得出了单位时间内的DB_TIME，再与基线进行比较后，将DB_TIME超出阀值的数据库的TOP 10语句信息记录表DB_TIME_CHECK_SQL中。

8) 获取了超出DB_TIME基线数据库的TOP 10语句信息后，需要对比这些语句的执行计划是否为最优，或者是否为第一次实施。

通过上述步骤即实现了生产数据库获取 DB_TIME、ELAPSED_TIME历史基线、当前数值的信息，并通过当前数值与基线对比得出存在效率问题的语句的操作。

4 结论与建议

4.1 对DB_TIME监控中所取得的效果

××银行数据中心开放系统部在2010年12月初部署了对DB_TIME监控，每天7点至18点对所有联机系统每隔15分钟进行监控，对于DB TIME超标的系统，在TIVOLI平台报警的同时将相关信息通过 NOTES发送数据库专业组。对于 DB_TIME监控报警，数据库专业组专门撰写了DB_TIME监控报警处理手册，并建立了一套问题跟踪系统，用以专门跟踪语句的进展及结果。

截止至2011年7月16日，DB_TIME报警共3276次，涉及22套数据库578条语句。针对这些语句，开放系统部共创建事件单、问题单 253个，其中已得到优化的语句共474条，其他语句尚在开发中心分析优化中；在这些报警中，开放系统部共启动8次预案进行干预处理，皆在系统性能恶化、影响业务前成功完成应急处理，自针对联机系统部署DB_TIME监控以来，未发生一起因语句效率问题最终影响联机业务的事件。

4.2 进一步提升DB_TIME监控力度的思考

对生产联机数据库DB_TIME监控的效果显著，符合初期监控策略制定的目标，但通过对各类生产上发生的各类效率问题的分析，DB_TIME监控还有不少可以改进提升的地方：

1) 对于批量系统，由于批量启动依赖于上游数据等客观条件限制，因此批量开始时间不完全固定，无法简单地使用 DB_TIME基线对比进行监控。后续需要对批量系统的相关监控进行进一步研究，使得监控满足各类生产系统。

2) 目前的监控仅限于通过DB_TIME与ELAPSED_TIME联动发现存在效率问题的语句，但对于一些资源异常占用引发语句执行时间过长（比如锁），则无法迅速定位问题根源，依旧需要富有经验的专业组成员进一步分析后才能发现问题所在，在问题分析处理环节有待提高效率。后续可以将之与数据库WAIT EVENT进行联动，更加自动、准确、快速地发现、定位语句执行时间过长的根本原因。

5 总结

在本研究方向上还存在着潜在研究方向，如何自动、准确、快速地发现、定位语句执行时间过长的根本原因，将是一个长期的课题。本文对 Oracle系统运行效率监控方法进行了初步的研究分析，为今后进一步的研究工作打下基础。

[1]Richmond Shee, Kirtikumar Deshpande and K Gopalakrishnan，Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning，[C]McGraw-Hill/Osborne，2004.

[2]Hailey,K. “Performance Tuning in Oracle 10g,” [C]proceedings of the Hotsos Symposium, 2004.

[3]Vaidyanatha, G., K. Deshpande, and J. Kostalec, Jr. Oracle[C]Performance Tuning 101. Oracle Press/Osborne, 2001.