OracleSQL计划管理技术在油田的应用研究

宋新芳，张秋菊

（中国石油辽河油田分公司，辽宁 盘锦 124010）

0 引言

辽河油田油气水井生产数据管理系统（简称A2系统）是一套集稀油、稠油、天然气的生产动态数据管理与应用的平台，实现了油气水井生产情况的及时跟踪，提高了油气水井各类生产报表的及时性和准确性，为企业油气生产决策提供了有效支持。A2系统自2008年建成并投入使用至今，伴随油田生产的不断持续，企业油气水井生产数据正以每天新增18万条记录的速度迅猛增长，系统数据量已超过380GB，系统应用规模也越来越大，已达到5000多用户。在系统数据量和用户数量持续增长的情况下，A2系统的响应速度日益变慢。

为了提升系统性能，保障系统稳定高效的运行，辽河油田曾尝试运用了索引优化、SQL语句优化、参数调整等多种数据库性能优化技术，对A2系统进行优化调整，取得了一定效果。但是，在A2系统的实际应用过程中，有一部分模块的SQL语句，虽然通过人工分析并没有发现任何问题，但在系统运行时SQL语句的执行速度缓慢，也就是存在了应用软件在开发测试环境和实际生产环境下系统性能出现较大偏差的问题。

针对上述问题，企业专门研究了Oracle系统的SPM（SQL PlanManagement）技术。SPM技术，可将数据库优化任务交给Oracle系统后台，由后台自动地分析处理，并执行最优的SQL执行计划路径，以达到系统性能优化的目标。企业通过运用SPM技术，能够将开发测试环境下和生产环境下的系统，以及升级前后的系统有效地关联起来，使得不同环境下的应用软件性能不会出现较大衰减。

1 SQL优化技术演变

1.1 传统技术

SQL语句执行速度主要取决于SQL语句的执行计划，而Oracle优化器主要依据所访问表和其他对象的统计信息、优化器参数、系统硬件配置、Oracle参数设置、SQLProfile等信息，来综合分析并确定SQL语句最佳执行计划。

传统上Oracle主要采用两种技术来保证SQL语句执行计划的稳定性：一种是在SQL编程语句中增加提示（HINT），强制要求Oracle优化器采用某种固定的执行计划；另一种是使用存储大纲（StoredOutline）技术，即将优化的SQL执行计划提示信息存储在Oracle内部的一组表格中，强制要求相关SQL语句使用这些存储大纲。这两种技术的一个共同特点是将相关SQL语句的执行计划事先固定下来，而不考虑未来环境的变化。

SQLProfile则是Oracle从10g版本开始提供的另一种确保SQL语句执行计划最优化的技术。简而言之，SQLProfile是关于SQL语句的统计分析信息，例如：针对某条SQL语句，Oracle采用了哪些取样数据，执行了该语句哪些片段，以及该语句执行情况的历史数据分析等，这些信息构成了SQL语句的SQLProfile。当Oracle执行SQL语句时，优化器不仅利用该语句所访问对象的统计信息，而且还要利用SQLProfile，来整体评估SQL语句执行计划的优劣性，并在分析评估的基础上产生最优的SQL执行计划。

1.2SPM 技术

OracleSPM通过维护一个SQL执行计划的基线（Baseline），来自动控制SQL语句执行计划的演化过程。在启用SPM技术之后，系统仅执行基线中的SQL执行计划，新产生的SQL执行计划只有在优化器证明其不会导致系统性能衰减时，才能被加入到执行计划基线（PlanBaseline）中。

当一个SQL语句被重复执行时，Oracle就将这些语句记录在语句日志（Statementlog）中，优化器也会将这些语句的执行计划保存于执行计划历史（PlanHistory）中，包括SQL语句文本、Outline、绑定变量、编译环境信息等。Oracle11g系统的SQL自动优化任务（AutomaticSQLTuningTask）将每天晚上在维护窗口针对资源消耗过大的SQL语句进行分析和验证，当确定SQL执行计划不会导致系统性能衰减之后，才会将该计划加入到计划基线（PlanBaseline）中并得到执行。这样，基线中的执行计划都是系统可接受的（Accepted）。

2 SPM的使用方式及过程

SPM有两种使用方式：第一种方式是快速逐次捕获（Onthefly capture），即通过设置 OPTIMIZER＿CAPTURE＿SQL＿PLAN＿BASELINES的初始化参数为TRUE（该参数缺省值为FALSE），来开启SPM的快速逐次捕获功能。Oracle将自动识别重复执行的SQL语句，并记录在执行计划历史中，第一次执行的SQL语句执行计划将自动成为计划基线，同时 OPTIMIZER＿USE＿SQL＿PLAN＿BASELINES参数保持为缺省值TRUE。

SPM的第二种使用方式是批量加载（Bulkloading），数据库管理员（DBA）通过DBMS＿SPM程序包可手工批量加载复杂的、资源消耗过大的SQL语句的计划基线。

辽河油田针对某些复杂的、消耗大量资源的SQL语句，采用了批量加载的方法。其过程主要包括：利用AWR报告获取SQL语句；生成自动优化任务；执行自动优化任务；接受Oracle自动优化任务产生的SQLProfile。

3 SPM优化效果分析

SQL语句在优化之前AWR报告中的执行结果如表1所示。

表1中，ElapsedTime（ms）项是记录执行上述SQL语句所消耗的时间。从表1可以看到，该语句消耗时间为30273ms，即30秒。这30秒的时间远远超出了用户的等待时限（一般来说，应用系统单个功能界面的用户等待时限是8秒）。表中的BufferGets项是指系统从内存中读取的数据量，该语句从内存读取的数据量为1246155块，即9735MB。

系统在未实施SPM优化前，需要从内存读取超大量的数据，致使系统运行时间过长。企业用户普遍反映系统运行速度慢、等待时间长，用户一遍遍地致电投诉，但问题一直未能得到很好的解决，用户的满意度大大下降，运维管理人员的压力也增大。

在实施OracleSPM自动优化，并且采用Oracle推荐的SQL Profile之后，系统的执行效果得到明显改善。表2为系统优化后的执行结果。

表2 优化后的执行结果

从表2可以看出，系统运行时间ElapsedTime为4653ms，即 4.6 秒，BufferGets为 303480 块，即 2370MB。可见，系统运行时间从30秒下降到4.6秒（8秒之内），从内存读取的数据量从9735MB下降到2370MB，系统的运行效率得到很大的提高。用户反映系统的响应速度明显加快，系统使用的满意度也大大提升。

4 结束语

数据库技术的发展可谓日新月异，利用新技术可使数据库的管理更加科学、高效、简化。SPM技术，解决了人工分析所不能解决的系统衰减问题，让数据库优化变得智能、高效。尽管SPM技术在大型、复杂、负载高的应用系统中具有一定的应用价值，良好的系统架构设计、合理的资源配置，以及软件开发过程中高效的程序编写，却依然是影响系统性能高低的关键因素。

［1］罗敏.品悟性能优化［M］.北京：清华大学出版社，2011.