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.