宋新芳，張秋菊

（中國石油遼河油田分公司，遼寧 盤錦 124010）

0 引言

遼河油田油氣水井生產(chǎn)數(shù)據(jù)管理系統(tǒng)（簡稱A2系統(tǒng)）是一套集稀油、稠油、天然氣的生產(chǎn)動(dòng)態(tài)數(shù)據(jù)管理與應(yīng)用的平臺，實(shí)現(xiàn)了油氣水井生產(chǎn)情況的及時(shí)跟蹤，提高了油氣水井各類生產(chǎn)報(bào)表的及時(shí)性和準(zhǔn)確性，為企業(yè)油氣生產(chǎn)決策提供了有效支持。A2系統(tǒng)自2008年建成并投入使用至今，伴隨油田生產(chǎn)的不斷持續(xù)，企業(yè)油氣水井生產(chǎn)數(shù)據(jù)正以每天新增18萬條記錄的速度迅猛增長，系統(tǒng)數(shù)據(jù)量已超過380GB，系統(tǒng)應(yīng)用規(guī)模也越來越大，已達(dá)到5000多用戶。在系統(tǒng)數(shù)據(jù)量和用戶數(shù)量持續(xù)增長的情況下，A2系統(tǒng)的響應(yīng)速度日益變慢。

為了提升系統(tǒng)性能，保障系統(tǒng)穩(wěn)定高效的運(yùn)行，遼河油田曾嘗試運(yùn)用了索引優(yōu)化、SQL語句優(yōu)化、參數(shù)調(diào)整等多種數(shù)據(jù)庫性能優(yōu)化技術(shù)，對A2系統(tǒng)進(jìn)行優(yōu)化調(diào)整，取得了一定效果。但是，在A2系統(tǒng)的實(shí)際應(yīng)用過程中，有一部分模塊的SQL語句，雖然通過人工分析并沒有發(fā)現(xiàn)任何問題，但在系統(tǒng)運(yùn)行時(shí)SQL語句的執(zhí)行速度緩慢，也就是存在了應(yīng)用軟件在開發(fā)測試環(huán)境和實(shí)際生產(chǎn)環(huán)境下系統(tǒng)性能出現(xiàn)較大偏差的問題。

針對上述問題，企業(yè)專門研究了Oracle系統(tǒng)的SPM（SQL PlanManagement）技術(shù)。SPM技術(shù)，可將數(shù)據(jù)庫優(yōu)化任務(wù)交給Oracle系統(tǒng)后臺，由后臺自動(dòng)地分析處理，并執(zhí)行最優(yōu)的SQL執(zhí)行計(jì)劃路徑，以達(dá)到系統(tǒng)性能優(yōu)化的目標(biāo)。企業(yè)通過運(yùn)用SPM技術(shù)，能夠?qū)㈤_發(fā)測試環(huán)境下和生產(chǎn)環(huán)境下的系統(tǒng)，以及升級前后的系統(tǒng)有效地關(guān)聯(lián)起來，使得不同環(huán)境下的應(yīng)用軟件性能不會(huì)出現(xiàn)較大衰減。

1 SQL優(yōu)化技術(shù)演變

1.1 傳統(tǒng)技術(shù)

SQL語句執(zhí)行速度主要取決于SQL語句的執(zhí)行計(jì)劃，而Oracle優(yōu)化器主要依據(jù)所訪問表和其他對象的統(tǒng)計(jì)信息、優(yōu)化器參數(shù)、系統(tǒng)硬件配置、Oracle參數(shù)設(shè)置、SQLProfile等信息，來綜合分析并確定SQL語句最佳執(zhí)行計(jì)劃。

傳統(tǒng)上Oracle主要采用兩種技術(shù)來保證SQL語句執(zhí)行計(jì)劃的穩(wěn)定性：一種是在SQL編程語句中增加提示（HINT），強(qiáng)制要求Oracle優(yōu)化器采用某種固定的執(zhí)行計(jì)劃；另一種是使用存儲(chǔ)大綱（StoredOutline）技術(shù)，即將優(yōu)化的SQL執(zhí)行計(jì)劃提示信息存儲(chǔ)在Oracle內(nèi)部的一組表格中，強(qiáng)制要求相關(guān)SQL語句使用這些存儲(chǔ)大綱。這兩種技術(shù)的一個(gè)共同特點(diǎn)是將相關(guān)SQL語句的執(zhí)行計(jì)劃事先固定下來，而不考慮未來環(huán)境的變化。

SQLProfile則是Oracle從10g版本開始提供的另一種確保SQL語句執(zhí)行計(jì)劃最優(yōu)化的技術(shù)。簡而言之，SQLProfile是關(guān)于SQL語句的統(tǒng)計(jì)分析信息，例如：針對某條SQL語句，Oracle采用了哪些取樣數(shù)據(jù)，執(zhí)行了該語句哪些片段，以及該語句執(zhí)行情況的歷史數(shù)據(jù)分析等，這些信息構(gòu)成了SQL語句的SQLProfile。當(dāng)Oracle執(zhí)行SQL語句時(shí)，優(yōu)化器不僅利用該語句所訪問對象的統(tǒng)計(jì)信息，而且還要利用SQLProfile，來整體評估SQL語句執(zhí)行計(jì)劃的優(yōu)劣性，并在分析評估的基礎(chǔ)上產(chǎn)生最優(yōu)的SQL執(zhí)行計(jì)劃。

1.2SPM 技術(shù)

OracleSPM通過維護(hù)一個(gè)SQL執(zhí)行計(jì)劃的基線（Baseline），來自動(dòng)控制SQL語句執(zhí)行計(jì)劃的演化過程。在啟用SPM技術(shù)之后，系統(tǒng)僅執(zhí)行基線中的SQL執(zhí)行計(jì)劃，新產(chǎn)生的SQL執(zhí)行計(jì)劃只有在優(yōu)化器證明其不會(huì)導(dǎo)致系統(tǒng)性能衰減時(shí)，才能被加入到執(zhí)行計(jì)劃基線（PlanBaseline）中。

當(dāng)一個(gè)SQL語句被重復(fù)執(zhí)行時(shí)，Oracle就將這些語句記錄在語句日志（Statementlog）中，優(yōu)化器也會(huì)將這些語句的執(zhí)行計(jì)劃保存于執(zhí)行計(jì)劃歷史（PlanHistory）中，包括SQL語句文本、Outline、綁定變量、編譯環(huán)境信息等。Oracle11g系統(tǒng)的SQL自動(dòng)優(yōu)化任務(wù)（AutomaticSQLTuningTask）將每天晚上在維護(hù)窗口針對資源消耗過大的SQL語句進(jìn)行分析和驗(yàn)證，當(dāng)確定SQL執(zhí)行計(jì)劃不會(huì)導(dǎo)致系統(tǒng)性能衰減之后，才會(huì)將該計(jì)劃加入到計(jì)劃基線（PlanBaseline）中并得到執(zhí)行。這樣，基線中的執(zhí)行計(jì)劃都是系統(tǒng)可接受的（Accepted）。

2 SPM的使用方式及過程

SPM有兩種使用方式：第一種方式是快速逐次捕獲（Onthefly capture），即通過設(shè)置 OPTIMIZER＿CAPTURE＿SQL＿PLAN＿BASELINES的初始化參數(shù)為TRUE（該參數(shù)缺省值為FALSE），來開啟SPM的快速逐次捕獲功能。Oracle將自動(dòng)識別重復(fù)執(zhí)行的SQL語句，并記錄在執(zhí)行計(jì)劃歷史中，第一次執(zhí)行的SQL語句執(zhí)行計(jì)劃將自動(dòng)成為計(jì)劃基線，同時(shí) OPTIMIZER＿USE＿SQL＿PLAN＿BASELINES參數(shù)保持為缺省值TRUE。

SPM的第二種使用方式是批量加載（Bulkloading），數(shù)據(jù)庫管理員（DBA）通過DBMS＿SPM程序包可手工批量加載復(fù)雜的、資源消耗過大的SQL語句的計(jì)劃基線。

遼河油田針對某些復(fù)雜的、消耗大量資源的SQL語句，采用了批量加載的方法。其過程主要包括：利用AWR報(bào)告獲取SQL語句；生成自動(dòng)優(yōu)化任務(wù)；執(zhí)行自動(dòng)優(yōu)化任務(wù)；接受Oracle自動(dòng)優(yōu)化任務(wù)產(chǎn)生的SQLProfile。

3 SPM優(yōu)化效果分析

SQL語句在優(yōu)化之前AWR報(bào)告中的執(zhí)行結(jié)果如表1所示。

表1中，ElapsedTime（ms）項(xiàng)是記錄執(zhí)行上述SQL語句所消耗的時(shí)間。從表1可以看到，該語句消耗時(shí)間為30273ms，即30秒。這30秒的時(shí)間遠(yuǎn)遠(yuǎn)超出了用戶的等待時(shí)限（一般來說，應(yīng)用系統(tǒng)單個(gè)功能界面的用戶等待時(shí)限是8秒）。表中的BufferGets項(xiàng)是指系統(tǒng)從內(nèi)存中讀取的數(shù)據(jù)量，該語句從內(nèi)存讀取的數(shù)據(jù)量為1246155塊，即9735MB。

系統(tǒng)在未實(shí)施SPM優(yōu)化前，需要從內(nèi)存讀取超大量的數(shù)據(jù)，致使系統(tǒng)運(yùn)行時(shí)間過長。企業(yè)用戶普遍反映系統(tǒng)運(yùn)行速度慢、等待時(shí)間長，用戶一遍遍地致電投訴，但問題一直未能得到很好的解決，用戶的滿意度大大下降，運(yùn)維管理人員的壓力也增大。

在實(shí)施OracleSPM自動(dòng)優(yōu)化，并且采用Oracle推薦的SQL Profile之后，系統(tǒng)的執(zhí)行效果得到明顯改善。表2為系統(tǒng)優(yōu)化后的執(zhí)行結(jié)果。

表2 優(yōu)化后的執(zhí)行結(jié)果

從表2可以看出，系統(tǒng)運(yùn)行時(shí)間ElapsedTime為4653ms，即 4.6 秒，BufferGets為 303480 塊，即 2370MB?？梢?，系統(tǒng)運(yùn)行時(shí)間從30秒下降到4.6秒（8秒之內(nèi)），從內(nèi)存讀取的數(shù)據(jù)量從9735MB下降到2370MB，系統(tǒng)的運(yùn)行效率得到很大的提高。用戶反映系統(tǒng)的響應(yīng)速度明顯加快，系統(tǒng)使用的滿意度也大大提升。

4 結(jié)束語

數(shù)據(jù)庫技術(shù)的發(fā)展可謂日新月異，利用新技術(shù)可使數(shù)據(jù)庫的管理更加科學(xué)、高效、簡化。SPM技術(shù)，解決了人工分析所不能解決的系統(tǒng)衰減問題，讓數(shù)據(jù)庫優(yōu)化變得智能、高效。盡管SPM技術(shù)在大型、復(fù)雜、負(fù)載高的應(yīng)用系統(tǒng)中具有一定的應(yīng)用價(jià)值，良好的系統(tǒng)架構(gòu)設(shè)計(jì)、合理的資源配置，以及軟件開發(fā)過程中高效的程序編寫，卻依然是影響系統(tǒng)性能高低的關(guān)鍵因素。

［1］羅敏.品悟性能優(yōu)化［M］.北京：清華大學(xué)出版社，2011.