數據庫優化技術研究

柳培林

[摘? ? 要] Oracle數據庫是目前油田使用最為廣泛的數據庫。Oracle數據庫性能直接影響到應用系統的運行效率，其性能受到多方面因素的影響。對Oracle數據庫性能優化是一項連續復雜的系統工作。文章首先簡要介紹了影響Oracle數據庫性能的主要因素，接著從4個方面介紹了Oracle數據庫優化調整思路和方法。

[關鍵詞] Oracle;性能優化;SGA

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2019. 03. 056

[中圖分類號] TP311? ? [文獻標識碼]? A? ? ? [文章編號]? 1673 - 0194（2019）03- 0140- 02

1? ? ? 問題的提出

隨著油田信息化應用系統數量不斷增加、數據庫整合進程的不斷推進，大慶油田第二采油廠目前在運行Oracle數據庫110個，分布在兩臺Sun M4000服務器和一臺虛擬服務器上。隨著信息化應用的不斷發展，用戶數量也不斷增加。但廠內數據庫服務器的硬件水平尚停滯多年前水平，數據庫運行壓力較大。因此對數據庫進行優化變得重要起來。通過對其優化，可以緩解數據庫系統運行過程中性能的漸進性下降或性能突降等問題，以保證信息系統良好的運行狀態。下面將針對采油廠數據庫服務器分別闡述影響Oracle性能的主要因素和相應的優化手段。

2? ? ? 影響數據庫性能的主要因素

2.1? ?階段性數據處理壓力大

數據庫備份、存儲過程執行、數據回遷、物化視圖同步等數據庫事務占用服務器較大資源，會引起I/O沖突，降低整個系統的速度。

2.2? ?CPU占用過高

CPU是數據庫服務器中一個重要的資源，CPU資源被某個應用占用或被某個數據庫事務占用，會導致其他數據庫事務運行停滯，導致數據庫響應遲鈍。低效率的SQL語句、鎖沖突、SQL語句的重解析等原因都會引起CPU資源不足。

2.3? ?內存分配不合理

內存分配不合理將會減少Oracle用于存放最近訪問過的數據的緩沖區控件，并導致操作系統頻繁進行頁面或內存交換，從而導致系統I/O壓力大。

2.4? ?Oracle配置不合理

每一個Oracle實例都是由一組Oracle后臺進程和系統全局區的一個內存區所組成，正確調整Oracle配置將會對系統性能產生重大的影響。

2.5? ?網絡速度低

網絡的帶寬會在一定程度上影響系統的整體性能，網速過低會增加網絡I/O的負載量，從而降低數據庫系統的吞吐量并延長用戶相應時間。

3? ? ? 性能優化與調整技術

3.1? ?調整數據處理事務時間

針對數據庫備份、存儲過程執行、數據回遷、物化視圖同步等占用服務器較大資源的事務合理分配時間，利用任務計劃對備份、存儲過程進行定時運行，物化視圖可利用Alter命令對啟動時間進行調整，讓這些事務在夜間無人應用數據庫時啟動執行，不影響白天的數據庫訪問。

3.2? ?充分利用系統全局區域SGA

SGA區是由Oralce分配的共享內存結構，包含一個數據庫實例共享的數據和控制信息，是Oracle數據庫中進行內存管理的關鍵模塊。SGA區由高速緩沖區、共享池、重做日志緩沖區、大型池和其他結構組成。根據Oracle官方指導文件說明，SGA管理是Oracle數據庫性能優化的主要方面。

Oracle的信息存儲在內存和磁盤上，由于訪問內存比訪問磁盤快得多，在大量并發用戶下，如果Oracle內存不夠會降低程序的處理效率，延緩數據庫的響應時間，提高數據庫性能需要設置合適的內存尺寸。

根據數據庫運行狀況可重新調整SGA的大小，對單節點修改SGA大小的方法如下：

SQL>alter system set sga_target=600m scope=both sid='ora815'

3.2.1? ?數據高速緩沖區

存儲的是最近從數據文件中檢索出來的數據，供所有用戶共享。當用戶要操作數據時，先由服務器進程將數據從磁盤的數據文件中讀取到數據高速緩沖區，然后在緩沖區中進行處理。為減少系統磁盤I/O開銷，可以調整數據緩沖區的尺寸，使服務器進程盡量在緩沖區中找到所需的數據，盡量減少等待數據塊或空閑緩沖區的時間。

（1）加大buffer cache的大小：可以通過調整DB_CACHE_SIZ

E參數的值增大Buffer cache。

（2）使用多個緩沖池提高buffer cache 的命中率：通過修改參數_kghdsidx_count值增減子池數量。

3.2.2? ?重做日志緩沖區

用于緩存用戶對數據庫進行修改操作時生成的重做記錄。重做日志緩沖區是一個循環緩沖區，從頂端向低端寫入數據，然后再返回到緩沖區的頂端循環寫入。重做日志緩沖區的大小對數據庫性能有較大影響。較大的重做日志緩沖區，可以減少對重做日志文件寫的次數，適合長時間運行的、產生大量重做記錄的事務。日志緩沖區的大小 log_buffer，默認值為512 k或128*cpu_count ，取較大的一個值。通過修改log_buffer參數可設置日志緩沖區大小。

3.2.3共享池

用于緩存最近執行過的Sql語句、PL/SQL程序和數據字典信息，是對Sql語句、PL/SQL程序進行語法分析、編譯、執行的區域。合適的共享池大小可使編譯過的程序代碼常駐內存，大大降低重復執行相同的Sql語句、PL/SQL程序的系統開銷，從而可以提高數據庫的性能。

通過調整參數SHARED_POOL_SIZE的值，可以根據實際情況對共享池的大小進行調整。

在共享池空間管理中，并不是簡單地把共享池設置得非常“大。共享池過大或過小都會降低性能，嚴重情況下還會導致數據庫服務器夯機。此外，在共享池管理中經常會遇到ORA-4031報錯，表示在共享池中分配內存失敗。此時，可以查詢V￥SGASTAT視圖，查看shared pool的free空間是否接近于0。查詢語句是：select * from v$sgainfo where pool=′shared pool′and name=′free memory′。如果結果接近于0，表明分配給共享池的內存不足，需要增加內存。但是很多時候結果顯示有很多free空間，而仍然會報ORA-4031錯誤。主要原因是共享池空閑空間碎片過多。有太多的碎片，當申請大空間存儲塊時，每個碎片都不能滿足應用請求空間分配的大小。解決辦法是：修改初始化參數SHARED_POOL_RESERVED_SIZE，此參數默認為5%，可以適當增大。

3.2.4? ?大型池

大型池是一個可選的內存配置項，主要為Oracle多線程服務器、服務器I/O進程、數據庫備份與恢復操作、執行具有大量排序操作的SQL語句等需要大量緩存的操作提供內存空間。

對這些內存緩沖區的合理設置，可以大大加快數據庫查詢速度，一個足夠大的內存區可以把絕大多數數據存儲在內存中，只有那些不怎么頻繁使用的數據，采用磁盤讀取，這樣就可以大大提高內存區的命中率。

3.3? ?物理結構的優化

Oracle數據庫物理存儲結構的優化對于提高數據庫的性能也非常重要，物理結構的優化就是依靠磁盤的讀寫的并行度、減少存儲的動態擴展來實現數據庫系統的性能優化。

3.3.1? ?磁盤讀寫的并行優化

磁盤I/O是數據庫訪問開銷的重要指標之一，數據庫物理結構的優化雖然無法減少從磁盤中讀寫數據的次數，但是可以通過并行化的方式，減少對單節點磁盤讀寫的競爭，主流的用法是：（1）將系統的system、temp、rollback、rode log表空間盡量與數據表空間存放在不同的物理磁盤中，能夠減少系統進程與用戶進程對I/O的競爭。（2）同理，Oracle的數據文件和操作系統的其他文件也可存放在不同磁盤，這樣也可減少I/O爭用的概率。

3.3.2? ?減少存儲的動態擴展

當在數據庫建立表空間時，如果沒有分配足夠的存儲表空間以及合理的增幅長度，則存儲結構在運行階段會進行動態擴展。在動態擴展的過程中，需要讀取系統參數文件，實現對空閑空間的利用，在這個過程中需要消耗較長的時間，并會導致數據庫運行效率突然大幅下降，因此，在建立表空間時，就要合理計算并設置存儲空間大小、增幅長度、最大值參數。并要定期檢查表空間使用情況，對占用率高的表空間進行管理。

3.4? ?SQL語句優化

SQL 語句本身的執行效率直接影響Oracle數據庫執行效率，它消耗了數據庫系統70%～90%的資源，對SQL語句進行合理設計可以使其更高效地執行，以提高系統對資源的利用率，好的SQL語句可以加快執行速度，減少網絡傳輸，從而最大限度地發揮數據庫的性能。

（1）盡量減少對數據庫的查詢次數，對幾個表查詢時FROM子句的順序，按照由內及外的訪問順序應把可篩選出較少記錄的表放在前面，執行時最先查找出這個表的幾個記錄，再和其他表的記錄相連接。

（2）為了充分利用庫緩沖區的SQL解析信息，對于經常運行條件子句變量值不同的SQL 語句，應將這些變量改為統一的綁定變量。

（3）調整SQL 的關鍵是使數據庫尋找數據的路徑最簡化， 限制動態SQL 的使用， 優化操作符， 如in 或not in，is null 或is not null，like ，union等操作符，應盡量少用。

（4）避免不帶任何where 條件的SQL 語句的執行，使用order by、group by、union 等條件的SQL 語句會對查詢完的數據進行排序，增大了PGA 或TEMP 的負擔，優化這些語句時可在使用這些條件的列上加上有序索引。

（5）對SQL 語句的索引進行優化，如：在索引列使用is null 和is not null，或進行了顯式或隱式的運算時索引不被使用，采用函數處理的字段也不能利用索引等。

（6）避免相關子查詢，查詢嵌套層次越多，效率越低，為了加速查詢速度，可以使用臨時表。

4? ? ? 結? ? 論

Oracle數據庫的優化管理是一個長期不間斷的工作，隨著數據庫規模的擴大、用戶數據量的增加，oracle數據庫性能問題將會越來越突出，Oracle數據庫的性能優化涉及的方面很廣，優化和調整是一個需要不斷摸索、總結的過程。在實踐中，必須先了解影響數據庫系統性能的原因，針對不同的因素選擇合理的優化調整策略予以調整，同時也需要采取更加先進的技術來對數據庫進行調優，以保證廠數據庫安全高效穩定運行。

主要參考文獻

[1]路川，胡欣杰，等. Oracle11g寶典[M].北京：電子工業出版社，2014.