產銷系統后臺Oracle數據庫優化設計與實現

劉澤輝

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

0 引言

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

1 目前產銷系統現狀

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

2 設計思路及技術實現

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

2.1 實例調整

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

2.1.1 內存區結構

SGA就是系統全局區，是指內存中允許多個進程相互通信的區域。在Oracle中，SGA對所有進程來說都是全局可用的。圖1為SGA結構圖。

2.1.2 調整結構

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

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

圖1 SGA結構圖

2.2 SQL語句調整

2.2.1 索引設置

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

2.2.2 刪除不必要的索引

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

2.2.3 將調整持久化

Oracle的執行計劃是依據各種情況，如表的統計資料而變化的，但有時這種變化是不需要的。為了將已經調整好的SQL執行計劃固定，可以用Oracle的工具將執行計劃持久化存儲。

3 結束語

調整后再次進入測試環境，對一些畫面進行測試，發現響應時間有了很大提高，絕大部分從十幾秒縮短到10 s以下，有的甚至降低至2～3 s，基本實現了產銷系統后臺Oracle數據庫的優化目的，達到了預想效果。在經過多方測試后，將優化方法應用于正式環境，提高了用戶滿意率。