產(chǎn)銷系統(tǒng)后臺Oracle數(shù)據(jù)庫優(yōu)化設計與實現(xiàn)

劉澤輝

（天津天鐵冶金集團技術中心，河北涉縣056404）

0 引言

天鐵集團內(nèi)先后建設的大型系統(tǒng)，如產(chǎn)銷、生產(chǎn)系統(tǒng)以及財務nc系統(tǒng)，為節(jié)約管理成本、提高數(shù)據(jù)共享度，業(yè)務數(shù)據(jù)一般都比較集中，導致數(shù)據(jù)庫中存放的數(shù)據(jù)量很大。如何對其進行快速訪問，提高系統(tǒng)響應時間就顯得十分重要。在實際應用中，往往采用各種優(yōu)化措施，使SQL查詢經(jīng)過數(shù)據(jù)庫優(yōu)化器的處理，得到最佳執(zhí)行計劃，即數(shù)據(jù)訪問路徑，提高響應速度。本文根據(jù)Oracle的特點，并結(jié)合SQL語句查詢特點，有針對性地提出了一些優(yōu)化方法，從而達到了縮短相應時間，提高響應速度的目的。

1 目前產(chǎn)銷系統(tǒng)現(xiàn)狀

為了不影響系統(tǒng)的正常使用，搭建了運銷系統(tǒng)的測試環(huán)境，其后臺數(shù)據(jù)庫為正式環(huán)境最近的完整數(shù)據(jù)庫，以此來模擬用戶使用的真實場景。針對數(shù)據(jù)量多的形式發(fā)票表、材料履歷表、發(fā)貨材料表、碼單表、結(jié)齊單等十幾個表進行了模擬，發(fā)現(xiàn)使用目前的SQL語句進行查詢、更新等操作得到結(jié)果集，少則需要5～6 s，一般需要10 s左右，有的甚至達到了20 s以上，這個響應速度難以滿足用戶使用要求，因此必須對其進行優(yōu)化，以提高響應速度。

2 設計思路及技術實現(xiàn)

對Oracle進行如下優(yōu)化：實例調(diào)整、SQL調(diào)整。其中實例調(diào)整主要是結(jié)合Oracle的特點，針對Oracle的一些參數(shù)進行優(yōu)化；SQL語句調(diào)整則是對具體語句進行分析，找出消耗資源多的，通過優(yōu)化器等工具，設置正確的索引，避免不必要的全表掃描。

2.1 實例調(diào)整

首先進行實例調(diào)整。實例涉及到SGA內(nèi)存區(qū)和一組Oracle后臺處理進程。對實例的調(diào)整就是對SGA內(nèi)存區(qū)和Oracle后臺處理進程的調(diào)整。因為SGA在oracle中的重要性，對該問題的解決中，主要是針對SGA內(nèi)存區(qū)的調(diào)整。

2.1.1 內(nèi)存區(qū)結(jié)構(gòu)

SGA就是系統(tǒng)全局區(qū)，是指內(nèi)存中允許多個進程相互通信的區(qū)域。在Oracle中，SGA對所有進程來說都是全局可用的。圖1為SGA結(jié)構(gòu)圖。

2.1.2 調(diào)整結(jié)構(gòu)

由于Oracle的一些重要操作均在SGA中進行，如語句解析、臨時數(shù)據(jù)存儲等，SGA的大小會影響數(shù)據(jù)的查詢。據(jù)實驗統(tǒng)計，在Oracle對數(shù)據(jù)過程中，同樣的數(shù)據(jù)從內(nèi)存中讀取要比從磁盤中讀取速度要快的多，一般是磁盤讀取的十幾倍，因此要盡可能地將數(shù)據(jù)保存在內(nèi)存中，減少磁盤的I/O操作。但若SGA太大，會減少操作系統(tǒng)的內(nèi)存，使得一些應用程序不能運行，出現(xiàn)系統(tǒng)崩潰現(xiàn)象，一般SGA大小為物理內(nèi)存的50%～60%。從運銷系統(tǒng)Oracle數(shù)據(jù)庫的SGA參數(shù)上看，其大小為所使用操作系統(tǒng)的40%，設置上偏小，需要增加SGA所占內(nèi)存的比重。

修改SGA的部分參數(shù)。Oracle的SGA由buffer cache、shared pool、large pool等幾部分組成，當多用戶查詢數(shù)據(jù)量增大時，會出現(xiàn)內(nèi)部爭用內(nèi)存的現(xiàn)象。為此Oracle10g的版本新增一個參數(shù)SGA_TARGET，來實現(xiàn)內(nèi)部的自動調(diào)整。而產(chǎn)銷系統(tǒng)所使用的數(shù)據(jù)庫版本也為Oracle10g，而其在當初設計時，并沒有使用該參數(shù)，使得內(nèi)部爭用內(nèi)存的現(xiàn)象時有發(fā)生，影響了查詢的相應速度，因此將該參數(shù)啟用，實現(xiàn)Oracle的SGA自動管理。

圖1 SGA結(jié)構(gòu)圖

2.2 SQL語句調(diào)整

2.2.1 索引設置

在對大表進行掃描時，首先要避免不必要的全表掃描。最常見的做法是給大表添加索引。給大表添加索引后，可以通過訪問索引的方式獲得記錄的物理位置，從而達到訪問表的目的。對于一個擁有大量字段的表，如果只需要返回其中少量字段，那么在這些字段上建立索引，通過索引訪問獲得記錄，將大大降低物理磁盤讀寫次數(shù)，從而降低了整個查詢響應時間。通過對運銷系統(tǒng)典型響應速度慢的幾個畫面進行查看，發(fā)現(xiàn)共同特點就是其用來查詢的某些SQL語句進行了全表掃描，相當于一個上千萬的數(shù)據(jù)表，要一行行讀取，即使計算機速度再快，其響應時間也至少在10s甚至幾十秒以上，且不考慮數(shù)據(jù)遠距離傳輸?shù)膯栴}。針對這些語句進行分析，查看使用的SQL查詢語句是否有條件限制，并結(jié)合表結(jié)構(gòu)分析語句引用條件是否為索引。通過對表結(jié)構(gòu)進行查看，發(fā)現(xiàn)部分表雖然設置了索引，但在SQL語句中并沒有引用，也就是說索引并沒有起作用，有的表甚至沒有索引，這導致了在進行操作時，出現(xiàn)了數(shù)據(jù)庫查詢中最應避免的全表掃描現(xiàn)象發(fā)生，從而造成查詢速度降低。針對這些情況，對產(chǎn)銷系統(tǒng)內(nèi)經(jīng)常使用的表，特別是一些大表經(jīng)常使用的字段上添加或修改了索引，對一些沒有使用條件限制的語句，通過分析PC文件以及運銷系統(tǒng)上下之間的流程，對語句進行修改，增加索引的引用。

2.2.2 刪除不必要的索引

在Oracle數(shù)據(jù)庫中，索引也并不是萬能的，在對運銷系統(tǒng)的材料履歷表進行分析時，發(fā)現(xiàn)雖然查詢語句引用了索引，但通過模擬試驗結(jié)果發(fā)現(xiàn)該查詢花費了比全表掃描更多的響應時間，這是由于O－racle對這個索引進行了全索引掃描，造成物理讀寫數(shù)量與全表掃描相比，不僅沒有減少，反而大幅上升。究其根本是這個表中的記錄值幾乎都不相同，而查詢時不僅需要近乎全表掃描，而且還需要按索引進行排序，于是出現(xiàn)了速度更慢的問題。因此采用刪除索引，對數(shù)據(jù)量大的表采取分區(qū)的方法，來提高查詢效率。

2.2.3 將調(diào)整持久化

Oracle的執(zhí)行計劃是依據(jù)各種情況，如表的統(tǒng)計資料而變化的，但有時這種變化是不需要的。為了將已經(jīng)調(diào)整好的SQL執(zhí)行計劃固定，可以用Oracle的工具將執(zhí)行計劃持久化存儲。

3 結(jié)束語

調(diào)整后再次進入測試環(huán)境，對一些畫面進行測試，發(fā)現(xiàn)響應時間有了很大提高，絕大部分從十幾秒縮短到10 s以下，有的甚至降低至2～3 s，基本實現(xiàn)了產(chǎn)銷系統(tǒng)后臺Oracle數(shù)據(jù)庫的優(yōu)化目的，達到了預想效果。在經(jīng)過多方測試后，將優(yōu)化方法應用于正式環(huán)境，提高了用戶滿意率。