基于ORACLE物化視圖的電力營銷系統優化

周文瓊 王樂球 曹重 簡璇

(1.廣東科學技術職業學院，廣東珠海 519080;2.中山大學，廣東珠海 519080;3.南方電網綜合能源有限公司，廣州 510000;4.重慶電力局，重慶 404100)

在國家電力公司提出發展“數字電力”這一大背景下［1］，用電營銷系統在各級電網公司得到廣泛應用，80%以上的電力營銷系統采用了ORACLE數據庫作為數據庫平臺，對于電網公司系統管理員來說，非常希望ORACLE數據庫運行穩定和性能良好。但是隨著數據量的不斷增加，DBA(Database Administrator，數據庫管理員)常常面臨著數據庫性能問題。下面對一個電力營銷系統實際生產中遇到的案例進行剖析，從中可以領會到Oracle數據庫診斷性能的思路和方法，特別是解決SQL性能優化問題。

1 電力營銷系統概述

用電營銷管理是電力企業管理工作的重要內容，主要承擔電能銷售和社會應用管理工作，是電力生產供銷的最后環節，也是電力工業生產和經營成果的最終體現。用電營銷的業務主管部門是營銷部，其主管業務包括:用電審批、核算發行、電費收繳、購電發行、設備管理、指標查詢、線損分析和客戶管理等，是供電公司直接面向廣大電力客戶的窗口。

電力營銷管理系統基于現代計算機與通信技術，是將電力營銷工作進行信息化管理的綜合信息系統［2］。從20世紀90年代初的“用電管理信息系統”開始，電力營銷信息化經過了二十多年的開發和建設，以地市級為單位的管理系統己經建立起來，能夠高效地完成用電經營管理的日常工作，輔助領導優化決策，顯著提高了企業的用電管理水平和工作效率。

1.1 系統功能結構

根據用電營銷業務的專業分工，目前綜合性的電力營銷系統一般分為五個子系統，即營銷業務管理、電量采集、客戶繳費、客戶服務等四個業務子系統，加上營銷與服務監管子系統，形成“4+1”的核心框架。如圖1所示。

其中營銷業務子系統是用電營銷系統的核心模塊，支持供電公司業擴、抄核收、計量、用電檢查以及業務稽查等日常營銷業務的處理，是其它模塊實施的基礎;電量采集子系統利用調度實時系統、配電信息采集系統等系統的數據采集功能，將購電側、供電側、銷售側三個環節的實時信息整合在一起，形成購、供、售三個環節實時信息的統一監控;客戶繳費子系統與銀行交易系統同共形成統一的銀行信息交互與交易結算的銀聯交易體系;客戶服務子系統是通過電話、傳真、網站、短信等手段，實現營業廳、電話、網站三維一體的客戶服務平臺。

圖1 電力營銷系統功能結構圖

1.2 系統的業務結構

目前，省供電公司的營銷業務是由幾級組織共同組成的縱橫向綜合在一起的系統結構。

1)從橫向看，它分為以下幾個層次:

a)從省供電公司橫向看有營銷處、財務處、生計處、計劃處等;b)從省下屬分公司 (地市級供電公司)橫向看有用電科、財務科、生計科、計劃科等;c)從地市下屬子公司 (縣級供電公司)橫向看有用電股、財務股、生計股、計劃股等;d)從縣 (市)下屬鎮級供電公司橫向看有用電班、財務班、生計班、計劃班等。

2)從縱向看，各層次有如下關系:

由于地市級分公司因其所在城市是其供電地區的負荷中心、業務中心和利潤中心，它除了實現供用電企業產、供、銷環節的業務之外，還代集團公司管理本供電地區控股的縣市級子公司，受省電力集團公司委托代管當地政府控股的縣市級子公司和躉售子公司，因而產生按業務分類產生的營銷、財務、生技、計劃等縱向管理關系。

1.3 數據庫服務器運行環境

系統數據庫服務器的配置環境如表1所示。

表1 數據庫服務器配置環境

1.4 居民用戶電量電費的數據模型

本文涉及的性能問題是“用戶電量突變分析”(居民用戶)，該模塊對抄表質量的稽查非常重要。“用電電量突變分析”主要查找有電量突增突減等異常現象的用戶，以便發現錯抄問題或設備故障問題。

電力用戶分為高壓用戶、低壓用戶和居民用戶，三類用戶報裝流程和管理方式各不相同，居民用戶具有信息簡單、數據量大的特點，“用戶電量突變分析”模塊涉及的數據庫模型圖如圖2所示。

1.5 數據量評估

用電營銷系統數據庫數據量的評估如表2所示。

表2 用電營銷系統主要業務數據量評估

圖2 居民電費臺賬數據庫模型

2 ORACLE數據庫SQL優化

在基于ORACLE數據庫的電力實際應用中，80%的性能問題是由于用戶使用了不恰當的SQL查詢語句造成的，因此優化SQL語句尤其是復雜SQL語句可以提升整個系統的運行效率。在集中式ORACLE數據庫中，SQL查詢的執行總代價主要包括:I/0代價、CPU代價和內存代價，調整影響執行效率的三大因素可以減少系統總代價。SQL語句優化的步驟如圖3所示。

2.1 使用AUTOTRACE功能輔助SQL優化

Oracle數據庫的客戶端工具SQL*PLUS提供了AUTOTRACE功能，可以跟蹤SQL的執行計劃，并收集統計信息，經常被作為SQL的優化工具之一被使用［3］。

2.1.1 啟用AUTOTRACE功能

在Oracle 11g，運行MYMORACLE_HOME dbmsadminutlxplan.sql腳本創建plan_table表，便可啟動該功能。

2.1.2 使用AUTOTRACE功能

AUTOTRACE常用選項如下:

2.2 Oracle物化視圖

圖3 SQL優化步驟

物化視圖 (MV，Materialized Views)從Oracle8i被引入，也被稱為快照，物化視圖是包括一個查詢結果的數據庫對像，它通過預計算或匯總構建獨立存貯，MV將查詢結果存儲在一個段中，當用戶提交查詢時返回查詢結果，而不需要重新執行查詢，從而極大提高相關性能。物化視圖是典型的以空間換時間的手段，通過物化視圖，Oracle可以實現更少的邏輯讀取，更少的寫操作，更少的cpu消耗及更快的響應速度［4］。

用SQL創建物化視圖的常用語法如下:

主要參數的含義如下:

1)refresh:視圖刷新的方式。

·fast:增量刷新。只刷新自上次刷新以后進行的修改，假設前一次刷新的時間為t1，那么使用fast模式刷新物化視圖時，只向視圖中添加t1到當前時間段內，主表變化過的數據。為了記錄這種變化，建立增量刷新物化視圖還需要一個物化視圖日志表。create materialized view log on(主表名)。

·complete:全部刷新，相當于重新執行一次創建視圖的查詢語句。

·force:默認方式。如可以使用fast模式時，采用fast方式;否則采用complete方式。

2)on:數據刷新的時間:

·on demand:在用戶需要刷新的時候刷新;

·on commit:當主表數據提交時，立即刷新MV中的數據;

·start with:從指定的時間開始;

·next:每隔指定時間刷新一次。

3 電力營銷系統遇到的問題及解決過程

3.1 問題描述

系統運行初期，“用戶電量突變分析”模塊運作正常，問題出現約在系統運行一年后，操作員及技術員同時報告該模塊運行緩慢。

以下是問題診斷和解決過程，由于這個模塊不影響其他正常營銷業務，選擇在下班后進行診斷。我們進入該模塊，做好初始選擇后，單擊【確定】進行查詢。

3.2 登錄數據庫獲取等待事件

系統界面一直處于等待狀態，檢查系統CPU和進程狀況，判斷數據庫經歷等待后，我們查詢v$session_wait獲取各進程等待事件:

發現存在db file sequential read等待，表明全表掃描操作成為該模塊的性能影響因素。

3.3 捕獲相關SQL

下面的腳本通過上述獲取等待事件session的SID，獲取影響性能的問題SQL:

3.4 使用AUTOTRACE跟蹤SQL執行

定位到問題SQL后，將問題SQL編輯成腳本文件qSql.sql，檢查該SQL的執行計劃:

3.5 分析問題

問題SQL訪問數據的條件為:絕對值 (本期有功電量－上期有功電量)/上期有功電量＞0.3

該條件的意義是查找電量增加或減少幅度超過30%的數據，訪問的v_JMDLDF實際上是一個視圖，查詢視圖創建語句:

從上面的SQL執行計劃可以看出，SQL調用了四個底層表，邏輯讀高達1538827。降低邏輯讀是解決問題的關鍵。SQL涉及的四個底層表分別是:CSB(參數表)、JMDLDF_HIS(居民電量電費歷史表)、JMDLDF(居民電量電費當月表)和JMYH(居民用戶表)，其中:CSB查詢當前的計費年月，數據量非常小;JMDLDF_HIS查詢上月的有功電量信息，雖然按年度做了分區表，但一年的數據約600萬行，該SQL對其做了全表掃描，導致模塊執行緩慢;JMDLDF查詢當月的有功電量信息，約50萬行數據;JMYH根據戶號查詢居民的用戶信息，約50行數據。

3.6 解決問題

每月居民電量電費信息與居民用戶信息的數量相當，約為50萬行，因此我們可以建立一個物化視圖，生成包含約50萬行數據的電量對比數據，而非每次查詢時再去三張大規模基表做連接查詢;再在該物化視圖上創建基于函數的索引。

1)創建物化視圖。

2)創建基于函數的索引。

3)查詢電量突變用戶信息的SQL。

3.7 診斷結果及分析

優化后系統性能大大提高，主要指標對比如表3所示，SQL執行時間優化前后比較棒圖如圖4所示。

表3 數據庫服務器配置環境

圖4 優化前后比較

使用物化視圖Vm_JMDLDF代替普通視圖V_JMDLDF，系統“用戶電量突變分析”模塊速度大大提高，而且前端的應用程序不需要做任何的修改。性能何以提高的原因如下:

1)物化視圖的使用:視圖是外模式的基本單位，常常在應用系統中使用視圖，對視圖的查詢，Oracle都實際上轉換為視圖SQL語句的查詢，而物化視圖是一種特殊的物理表，可以提高系統查詢性能。

2)基于函數索引的建立:索引是影響SQL語句性能的一個重要因素，建立合適的索引可以避免全表掃描減少I/0開銷，提高數據查詢速度。普通視圖上無法創建索引，而在物化視圖上可以創建索引;基于函數的索引是ORACLE 8i的新特性，它易于使用并且提供計算好的值，在不修改應用程序的邏輯上提高了查詢性能。

4 結語

Oracle的物化視圖功能強大，值得去研究和探索。本文針對電力營銷系統應用中出現的性能問題，結合實際用電營銷業務，提供了創建物化視圖的解決思路和和方案，該問題的圓滿解決取決于用戶對Oracle數據庫和電力企業業務的了解。隨著企業信息化進程的加速，企業管理信息系統中的數據量的持續上升，數據庫性能的優化將越來越引起人們的重視［6］。

［1］周文瑜，王濤，沈又幸.用電營銷決策支持系統的研究與開發［J］.電網技術，2006，30(S):540－543.

［2］馬力克，郭斌.寧夏電力公司營銷信息化管理平臺構建［J］.中國電力，2009，04:654－69.

［3］Bob Bryla，Biju Thomas.Oracle Database 10G New Features For Administrators Study Guide［M］.Burr Ridge:McGraw － Hill，2005:351 －398.

［4］Sam R.Alapati，Charles Kim.Oracle數據庫管理藝術11g新特性［M］.北京:人民郵電出版社，2009.

［5］蓋國強.深入淺出Oracle DBA入門、進階與診斷案例［M］.北京:人民郵電出版社，2006.

［6］高原，耿國華，劉曉寧.Oracle數據庫系統事后優化研究［J］.計算機工程與應用，2005，2:181－182.