燃料信息數據庫性能的優化方法研究

劉思穎，陶正亮，何云峰，于俊清*

（1.廣東粵電信息科技有限公司，廣東 廣州510630；2.華中科技大學計算機科學與技術學院，湖北 武漢430074）

0 引 言

數據庫技術是當代信息科學與技術的重要組成部分之一，主要研究計算機在信息處理過程中數據組織和存儲的問題，是管理信息系統的核心［1］。隨著信息技術的發展，數據庫系統在電力企業中得到了廣泛的應用，包括燃料信息系統［2-3］、財務系統［4］、大型設備在線監測和故障診斷系統［5-6］、大數據分析系統［7-8］等。然而，隨著數據庫存儲量的變大，數據庫應用系統的性能問題也越來越突出［9-10］，應用系統的用戶體驗越來越差，提高數據庫的性能是一個亟需解決的問題。Oracle 數據庫是前應用比較廣泛的關系型數據庫之一，據統計，在全球有93%的上市.COM 公司和65 家“財富全球100強”企業都選擇Oracle 數據庫來開展電子商務，中國企業也廣泛采用了Oracle數據庫［11］，因此基于Oracle 數據庫性能優化的研究具有十分重要的意義［12-14］。

通過對火力發電企業中重要的應用系統燃料信息系統的數據庫結構以及Oracle 的分區特性進行分析，使用平均每條SQL語句的執行時間來衡量數據庫的性能，采用多項式擬合求解出局部最優的內存參數，通過調整、記錄幫助用戶找到使性能最優的內存參數。結合Oracle 數據庫以及系統數據的特點，制定合理的優化方案，合理分配Oracle 的內存并解決系統數據庫中表的數據太多的問題，提高系統訪問數據庫的速度，保證系統高效且穩定地運行［15］。

1 內存參數調整策略

1.1 基于數據庫訪問方式的內存參數選取

燃料信息系統是一個典型B/S 架構的應用系統，利 用Java 數 據 庫 連 接（Java Database Connectivity，JDBC）來訪問數據庫。前臺界面發送數據請求傳給Tomcat 應用服務器，Tomcat 通過Java 調用Oracle 數據驅動程序，數據庫驅動程序將數據請求傳給數據庫服務器，然后從數據庫返回數據，數據沿著請求順序返回到前臺界面。其中，Java 代碼會被編譯成字節碼直接運行，Java 字節碼是Java 虛擬機（Java Virtual Machine，JVM）執行的一種命令格式。Oracle 數據庫的SGA 提供一塊區域——Java 池，供運行的Java 代碼以及JVM內的數據使用，Java 池的大小會影響Java 代碼運行的效率，從而影響數據庫的訪問性能。Java 池由參數JAVA_POOL_SIZE 控制，調整該參數的大小會改變系統數據庫的性能。

1.2 基于緩存機制的內存參數選取

緩存是數據交換的緩沖區，一般存在于內存中。當應用程序需要讀取數據的時候，會優先訪問緩存，如果緩存中存在相應的數據，就直接返回數據，這樣就能提高應用程序的執行效率，縮短用戶的等待時間。Oracle 數據庫在執行一條SQL 語句的時候，會在共享池里面確定這條語句的哈希值是否存在。如果哈希值存在，Oracle 執行軟解析，直接運行執行計劃；如果哈希值不存在，Oracle 執行硬解析，對SQL 語句進行解析，生成解析樹，包括視圖替換、表達式轉換等等，然后生成并運行執行計劃。硬解析是很昂貴的操作，大約占整個SQL語句執行時間的70%左右。

在運行執行計劃之后，返回結果數據，oracle 會在數據緩存區里查找結果數據是否存在，如果數據存在，則直接將數據從內存返回給應用程序。如果數據不存在，會將數據從硬盤加載到數據緩存區中，再返回給用戶。內存的存取速度與硬盤的存取速度差異較大，數據從硬盤讀取相當耗時，相對于從硬盤讀取，從內存中讀取數據幾乎不耗時。因此如果數據存在于數據緩存區中，其返回給應用程序的時間將會很短。

由此可見，共享池和數據緩存區的大小會影響數據庫的性能。在Oracle 數據庫中，共享池大小由參數SHARED_POOL_SIZE決定，數據緩存區的大小由參數DB_CACHE_SIZE 決定，對這兩個參數進行合理的調整，就可能提高數據庫的性能。

1.3 多項式擬合求解最優內存參數

燃料信息系統是一個實時系統，并發量比較低，對于每一次操作，好的用戶體驗是最小時間內返回數據結果。用戶的每一次操作都是后臺訪問數據庫的一個過程，因此，可以通過SQL語句的平均執行時間來衡量系統數據庫的性能，平均執行時間越短，數據庫性能越好，平均執行時間越長，數據庫性能問題越嚴重。

通過前面分析，調整Oracle的3個參數：SHARED_POOL_SIZE、DB_CACHE_SIZE 和JAVA_POOL_SIZE可以影響運行性能。每次調整都會帶來不同的性能影響，依次根據每個參數（x值）以及最后的結果（平均一條SQL語句的執行時間，y值）進行多項式擬合，找到這個參數的最優值。

記錄每一次調整的內存參數值以及對應的數據庫性能情況，由參數的值與最終結果構成的集合（xi，yi），i=0，1，2，…，m，求出一個函數：

使偏差的平方和：

達到最小。

顯然I 為a0，a1，a2，…，an的多元函數，求偏差的平方和即為求I 的極小值，根據多元函數求極值的必要條件，就是求I對a0，a1，a2，…，an的偏導數為0，即

在內存參數開始調整的時候，隨著參數值的變大，最終結果y值會慢慢變小（性能變好，平均執行時間變短），當達到最合理值的時候，y值達到最小值，超過這個合理的值繼續變大，y 值會慢慢變大。這符合二次曲線的變化特點，可以采用二次曲線進行擬合并求解。對于求出的二次曲線方程，找到使y 值最小的x 值，即為這個內存參數的最優值。另外，由于x 值必須大于零，并且二次曲線擬合可能帶來誤差，所以根據二次曲線方程求出來的x 值，必須落在給定集合中最大值與最小值之間，如果不在此區間，則該內存參數的最優值為集合中最大值與最小值之一。

多項式擬合的方法求解最優內存參數的方法在理論上能求出合理的內存參數值，如果3 個參數之間互不影響，則能求出最優值。如果3 個參數相互影響較大，則每次求出只是合理的值，多次求解會慢慢逼近最優值。另外，由于這個方法依據之前的調整結果，所以之前的結果集對求出最優內存參數速度的影響較大，即對算法的收斂速度影響較大。在實際使用中，可以先對內存參數以遞增的方式進行調整，若干次調整之后就可以使用算法求解的參數進行設置，這樣多項式擬合求解的效果較好。

2 數據表分區策略

2.1 基于數據特征的分區方法選取

信息系統最基本的功能是數據處理功能，包括數據采集、數據處理、數據存儲、數據管理、數據檢索以及數據傳遞等，屬于數據密集型的應用系統［16］。通常，信息系統在使用初期，數據量都比較小，隨著時間的累計，數據量會慢慢變大，從而引起數據庫性能的問題。

作為一個典型的信息系統——燃料信息系統，數據庫數據量大，數據種類比較雜，但大部分數據都具有相似的特點：近期的數據使用較頻繁，時間久遠的數據很少甚至不會被使用。這樣的數據特點很符合Oracle表分區的應用場景，對于表分區，Oracle 給了兩條建議［17］：

1）一個表的數據大小大于2 GB 時要考慮進行分區。

2）如果表中包含歷史數據，并且新的數據會加到新的分區。比如一個包含一年歷史數據的表，只有當前月份的數據可以被修改，而其他十一個月的數據為只讀。

很多數據表符合第二條建議，對這樣的表可以進行分區。使用Oracle支持的范圍分區技術可以將這些數據表按照時間分為兩部分，進行分區的時候指定一個分區時間，在指定時間之前的數據作為一個部分，之后的數據作為另一部分。由于對各分區的操作可以相互獨立地進行，這樣將數據表拆分為了兩個比較小的數據表，解決大表帶來的性能問題。

2.2 分區對數據庫性能的影響

雖然數據表分區可以解決大表帶來的問題，但是表分區也是有代價的，一個表分成幾個區，對于這幾個分區的維護需要時間，因此，并不是所有的表都適合被分區，分區能否帶來數據庫性能提升不能僅從理論方面分析，還需結合實際系統應用的場景。

下面將從兩種情況——表有無索引和表的增刪改查操作比例不同，通過具體的實驗來測試表分區對數據庫性能的影響。由于系統中對表的使用主要是查找和插入數據，刪除和修改數據情況相對較少，為了簡化實驗，將表的使用只分為查找和插入數據兩種情況。

實驗選擇了一個55 個字段的數據庫表（HC_GHJLD），它是燃料系統的核心表之一，存儲入廠煤計量數據，查詢和插入操作頻繁。根據HC_GHJLD 構造3 張結構完全相同的表，其中一張表（表名為HC_TEST）臨時存儲數據，另外兩張表作為測試表，其中一個表（表名為HC_TEST_BEFORE）不做任何處理，另一個表（表名為HC_TEST_AFTER）按日期類型的字段到廠日期（DCRQ）進行分成兩個區，分區時間間隔為一年，即一年前至今的數據作為一個分區，一年以前的所有數據作為另一個分區。

1）查找插入語句比例不同對表分區的影響

準備11組測試數據，每組120 000條測試語句，這120 000條語句只包含查找和插入語句，11組數據的查找和插入語句的比例分別為0∶10、1∶9、2∶8、3∶7、4∶6、5∶5、6∶4、7∶3、8∶2、9∶1、10∶0。其中，查找和插入語句比例為0∶10 表示一個表只會插入數據，而不會查找，這樣的表基本不會存在；查找和插入語句比例為10∶0表示一個表的數據不會增加，這樣的表中可能存放一些固定數據，基本不更新。

根據實驗結果生成折線圖如圖1 所示，從圖中可以看出，當查找與插入語句比例為0∶10到6∶4之間時，隨著比例的變大，時間縮短比例也會變大。當查找與插入語句比例為6∶4 的時候，時間縮短的比例達到最大，為63.34%。當查找與插入語句比例為7∶3 到9∶1之間時，時間縮短比例很穩定，都是60%以上。當查找與插入語句比例為10∶0 時，分區并不能縮短執行時間。

圖1 分區前后不同查找與插入比例的執行時間縮短比例圖Fig.1 The execution time reduction ratio of different query and insertion ratios before and after table partition

2）表索引對分區的影響

在關系型數據庫中，索引是一種與表有關的數據庫結構，使用索引可以加快數據查找的速度［18］。對表進行分區時，如果表本身就包含索引，表的幾個分區可以共同使用一個索引，這種索引叫全局索引。每個分區也可以有自己的索引，這種索引稱為本地索引，下面的實驗過程與前面的相同，選擇的表和測試數據完全一樣，唯一的區別就是測試表（HC_TEST_BEFORE 和HC_TEST_AFTER）都不包含索引。

根據實驗結果生成折線圖如圖2所示，可以看出，時間縮短比例基本和查找與插入語句比例呈線性關系，查找與插入語句的比例越大，時間縮短的百分比越高。

圖2 表沒有索引在分區前后不同查找與插入比例的執行時間縮短比例圖Fig.2 The execution time reduction ratio of different query and insertion ratios before and after table(no index)partition

將兩次實驗結果進行對比，當查找與插入語句比例在0∶10 到4∶6 之間時，有索引和沒有索引在表分區前后的時間縮短比例基本相同，有索引情況的時間縮短比例略高。當查找與插入語句比例在5∶5到10∶0之間時，沒有索引情況下在表分區前后的時間縮短比例比有索引情況下的時間縮短比例更高，并且隨著查找與插入語句比例增大，兩者的差別也變大。

2.3 表分區策略的制定

Oracle 提供的分區建議是面向所有的數據庫，沒有結合實際系統的數據表和特定的分區方法。燃料信息數據庫采用時間范圍分區，將數據分為兩個區，制定數據表分區的策略還需結合具體的情況。一般來說，數據表表中的記錄越多，表的大小就越大，下面通過實驗測試表中記錄條數不同對分區的影響，根據實驗結果來制定分區策略。

為了加快縮短每組實驗的時間，在表HC_TEST_BEFORE 和HC_TEST_AFTER 同樣的字段上建立唯一索引，準備兩組數據：

1）準備12 組分別包含1 000 條、3 000 條、5 000條、10 000條、20 000條、50 000條、100 000條、150 000條、200 000 條、300 000 條、400 000 條、500 000 條數據作為數據庫表中的原始數據。

2）準備12 組測試數據，查找與插入語句比例為6∶4，每組數據的條數為原始數據條數*0.8，即對于1 000 條原始數據準備1 000*0.8=800 條測試數據，以此類推。

根據實驗結果生成折線圖如圖3 所示，紅色字體表示負值，可以看出，20 000 條記錄是一個臨界點，當表中的記錄條數小于20 000條時，記錄條數越少，表分區帶來的開銷比例越大；當表中的記錄條數大于20 000 條時，表分區會在不同程度上縮短執行時間。當表中的記錄條數為100 000條時，表分區時間縮短比例達到最大—77.13%。

圖3 不同記錄條數在表分區前后的執行時間縮短比例圖Fig.3 The execution time reduction ratio of different number of records before and after table partition

根據這個實驗，制定數據表分區策略為：當表中包含的記錄條數大于20 000 條時，如果表上執行的查找和插入操作次數在0∶10到9∶1 之間，就對這個表進行分區，分區方法為時間范圍分區。

3 系統的設計與實現

3.1 系統框架描述

為了方便燃料信息系統的用戶隨時了解數據庫的性能情況，設計了一個Oracle數據庫性能監控的系統，其結構如圖4所示。系統展示了數據庫的四種性能信息：共享SQL 區的使用率、數據字典緩沖區的命中率、數據緩存區的命中率以及日志緩沖區的申請失敗率，并實現了兩種數據庫性能調整與優化的方案：調整內存參數和數據表分區。在數據庫性能監控的系統中，用戶在發現數據庫出現性能問題的時候，可以通過調整內存參數或者對表進行分區來優化數據庫的性能。

圖4 系統功能模塊圖Fig.4 System function module diagram

3.2 數據庫性能監控

數據庫性能監控是為了實時反映數據庫的性能情況，為了能很直觀地顯示數據庫性能情況，通過折線圖來實時展示數據庫性能信息，利用多線程技術，每隔5 s從數據庫系統視圖中讀取一次性能數據，主要采集SGA的性能信息，包括共享池、數據緩沖區和日志緩沖區。用戶登錄系統之后，會顯示性能監控界面，如圖5所示。

3.3 內存參數調整策略的實現

調整內存參數只能調整3 個參數DB_CACHE_SIZE、JAVA_POOL_SIZE 和SHARED_POOL_SIZE，同時還會顯示SGA 當前的大小，但SGA 的值SGA_TARGET 不可改，用戶設置最小的參數值點擊確定會彈出確定對話框，圖6所示。

用戶通過最優設定，系統后臺會根據當前的SGA_TARGET 值，利用二次曲線擬合，求出每個參數的最優值，即為推薦給用戶的最優內存參數。

圖5 數據庫性能監控Fig.5 Database performance monitoring interface

圖6 調整內存參數Fig.6 Interface for adjusting memory parameters

3.4 數據表分區策略的實現

數據表分區首先要根據分區策略確定需要進行分區的數據表。通過數據庫的審計功能，可以獲得表名、使用次數（查找和插入次數和）、查找次數、插入次數等信息。遍歷AUDIT_SUMMARY中的每一條記錄，如果查找和插入比例在0∶10到9∶1 之間，則根據表名從數據庫系統視圖USER_TAB_COLUMNS中查詢表中是否包含日期字段，如果包含，則根據聚集函數COUNT（*）查詢表中的記錄條數，如果記錄條數大于20 000，則根據表名從系統視圖USER_INDEXES 中查詢表上是否包含索引，根據查詢的結果更新當前表的記錄條數和有無索引的信息。最后得到的結果顯示在界面上，如圖7所示。

圖7 根據算法求出適合分區的表Fig.7 The table suitable for partition according to the algorithm

Oracle 數據庫支持分區，但是只支持在表創建的時候進行分區，對于存在數據的表不能直接分區。系統利用SQL 語句實現動態分區，即由用戶選擇分區的表并且指定分區時間間隔，過程對用戶透明。分區的具體步驟如下：

1）根據當前時間生成字符串與待分區的表名拼成新表的臨時表名。

2）按照分區字段和分區時間生成分區語句，其中分區語句包含舊表信息（表字段和字段數據類型）和舊表數據。

3）執行分區語句創建臨時新表，此時臨時表已分區并包含舊表的數據。

4）通過數據庫提供的RENAME（重命名）操作將舊表重命名，將臨時表表名重命名為舊表表名，此時舊表表名已為別名，臨時表（稱為新表）已命名為舊表表名。

5）從舊表的元數據中提取字段默認值、備注、約束等字段信息增加到新表。

6） 從 系 統 視 圖 USER_INDEXS 和 USER_TRIGGERS 中分別提取舊表的索引、觸發器等表的信息。

7）通過DROP 語句刪除舊表的所有信息（數據和結構），索引、觸發器等信息會同時被刪除。

8）將提取的舊表索引、觸發器等信息增加到新表。

9）分區結束。

4 結語

隨著信息時代的發展，數據庫技術的應用從傳統的商務數據處理逐漸發展到許多新的領域，數據庫的規模也不斷擴大，數據庫應用系統的性能問題也越來越突出。在對數據庫優化技術的國內外研究現狀以及信息系統進行深入研究分析之后，發現許多優化思想對現有的系統并不適用，數據庫性能優化工具也不能很好地解決數據庫的性能問題。本文結合數據庫性能優化理論方面的研究，從兩個方面制定優化策略對數據庫進行調整以達到優化目的，并且設計了一個數據庫性能監控系統。

1）結合信息系統訪問數據庫的原理以及緩存對SQL 語句執行的影響選擇需要調整的內存參數，使用平均一條SQL 語句的執行時間來衡量數據庫的性能，對內存參數和數據庫性能關系構建數學模型，采用多項式擬合，并利用貪心算法，求出每個參數的局部最優解，通過不斷迭代找到最適合系統的內存參數值。

2）根據數據庫支持的分區技術結合信息系統數據的特點，確定分區的方法為根據時間范圍分區，通過大量實驗來研究不同情況下表分區對數據庫性能的影響，確定合適的分區策略，給用戶推薦系統數據庫中所有適合分區的表，并使用程序實現分區。

3）設計了一款數據庫性能監控工具，通過折線圖從共享SQL區的使用率、數據字典緩沖區命中率、數據緩存區的命中率以及日志緩沖區的申請失敗率4個方面實時顯示系統性能情況。

［參 考 文 獻］（References）

［1］ Marco Vieira，Henrique Madeira. Towards a security benchmark for database management systems［C］. 2005 International Conference on Dependable Systems and Networks（DSN 2005），2005.

［2］ 韓鵬.某火電廠燃料全過程信息化管理系統開發與應用［D］.北京：華北電力大學，2016.HAN Peng.Development and application of the fuel overall process management information system of a thermal power plant［D］.Beijing：North China Electric Power University，2016.

［3］ 王夢婷，馬平.B/S模式火電廠燃料管理信息系統的設計與實現［J］.計算機仿真，2014，31（10）：137-140，169.WANG Mengting，MA Ping.B/S mode coal-fired power plant fuel management information system design and implementation［J］.Computer Simulation，2014，31（10）：137-140，169.

［4］ 杜梓平.財務信息管理系統的設計與實現［D］.北京：北京郵電大學，2009.DU Ziping. Design and implementation of the financial management information system ［D］. Beijing：Beijing University of Posts and Telecommunications，2009.

［5］ 王頂磊.大型發電機組轉機狀態智能監測［J］.湖北電力，2019，43（06）：61-66.WANG Dinglei.Intelligent monitoring on rotary machinery status of large generating sets［J］. Hubei Electric Power，2019，43（06）：61-66.

［6］ 宋宇.水輪發電機組故障診斷系統的設計與實現［J］.電子技術與軟件工程，2020，（19）：162-163.

［7］ 孫鵬，林光，邢智，等.智能電表及其數據在共享型配網中的深度應用研究［J］.湖北電力，2020，44（02）：58-66.SUN Peng，LIN Guang，XING Zhi，et al.Deep application research of smart meters and its data in sharing-type distribution network［J］.Hubei Electric Power，2020，44（02）：58-66.

［8］ 劉洋.一種基于大數據分析的電力客戶服務需求預測方法［J］.中國新技術新產品，2020，（23）：23-25.

［9］ CAI Juan. Research on application of computer database technology in information management［C］. 2014 IEEE workshop on advanced research and technology in industry applications（WARTIA），2014.

［10］ YAO Leiyue，JIANG Jie. Analysis and optimization for bottlenecks of database in massive management of information system［C］.2010 International Conference on Computer and Communication Technologies in Agriculture Engineering，2010.

［11］ 袁愛梅.Oracle 數據庫性能優化研究［D］.上海：華東師范大學，2007.

［12］ 王文閣.信息系統Oracle 數據庫性能優化研究［J］.數字技術與應用，2020，38（11）：59-61.WANG Wenge. Research on performance optimization of oracle database in information system［J］.Digital Technology and Application，2020，38（11）：59-61.

［13］ 付文哲，韓震，司玉杰.關于處理數據庫優化設計［J］.電腦知識與技術，2020，16（32）：28-29.

［14］ 吳尚，張靖，徐道磊.Oracle數據庫性能的優化設計思路研究［J］.數字技術與應用，2019，37（12）：167，169.WU Shang，ZHANG Jing，XU Daolei.Research on optimal design of oracle database performance ［J］. Digital Technology and Application，2019，37（12）：167，169.

［15］ 陶正亮.燃料信息數據庫性能的優化方法研究［D］.武漢：華中科技大學，2016.TAO Zhengliang.Research on optimization method of fuel information database performance［D］. Wuhan：Huazhong University of Science and Technology，2016.

［16］ 吳璇.基于信息系統的審計理論、模型及應用［D］.天津：天津大學，2004.WU Xuan.Theory，model and application of auditing based on information system［D］.Tianjin：Tianjin University，2004.

［17］ 蔣勇.ORACLE 數據庫分區技術及其應用［J］.科技信息，2011，（29）：53-54.

［18］ GUO Wenming，HU Zhiqiang. Memory database index optimization ［C］. 2010 International Conference on Computational Intelligence and Software Engineering，2010.