Oracle 效能優化小結

1 SQL優化方面

1.1  儘量減少硬解析

當一個使用者與資料庫建立連線後，會向資料庫發出操作請求，即向資料庫送過去SQL語句。伺服器程式會將SQL語句轉變為asc碼，並通過一個hash函式將asc碼生成出一個hash值，伺服器程式會到共享池中library cache查詢此hash是否存在，如果存在，伺服器程式會從library cache中讀取已經解析好的語句來執行（軟解析）。如果不存在，則需要做以下步驟：生成執行計劃和生成執行編碼。解析完成後，Oracle會將SQL語句本身程式碼、hash值、編譯程式碼、執行計劃和所有與該語句相關的統計資料存放到library cache中。（硬解析）。

在優化時要注意：
1.SQL書寫要規範，大小寫不同，空格數量的不同會生成不同的asc碼在解析時會被認為是不同的SQL語句。

2.對大量重複的SQL使用繫結變數。

 

1.2  選擇優化器

    優化器是SQL優化的關鍵。SQL在硬解析時會用到優化器，優化器分為RBO（Rule Based Optimizer）和CBO(Cost Based Optimizer)兩種 。RBO按固定的規則生成執行計劃，CBO更智慧 一些會生成消耗cup和O/I最少的執行計劃。推薦使用CBO優化器。

 

1.3  SQL語句優化

    Oracle資料庫80%的效能問題是由SQL引起的，儘量減少SQL語句消耗的邏輯讀，物理讀，CUP時間，熱點爭用。

在優化時要注意：

1.合理建立索引。

2.避免使用前置萬用字元如：like  ‘%ABC’。

3.用exists,和not exists 代替in 和not in。

4.避免使用*,因為在解析時會查詢資料字典將“*”轉換成所有列名。

2 記憶體優化方面

2.1 合理設定share pool
    shared pool 中library cache和data dictionary cache的大小，嚴重影響著解析過程；library cache越大，存放的執行計劃越多，從而減少硬解析。如果library cache過小，SQL語句會經常進行硬解析，導致SQL語句執行時間增加。增大share_pool_size可以保留更多的快取在記憶體中的sql語句執行計劃，也意味著共享SQL的可能性的增大。
    Oracle引入了data dictionary cache 元件以用來在記憶體中直接快取資料字典中的資訊，避免了系統從磁碟直接讀取資訊，提高了系統效能。如果data dictionary cache 元件記憶體很小，或者快取的資訊不是本次所需要的資料，就需要從磁碟讀取資訊，會直接影響系統效能。shared pool中library cache和data dictionary cache兩個緩衝區可動態的擴充套件或收縮。但是記憶體的擴充套件與收縮操作，也會產生cpu等開銷。為了避免兩者其一過大，導致另一個緩衝區過小的狀況，可以設定緩衝區的最小值閾值。

2.2 合理設定DB buffer cache
    儘量少讀資料檔案，提高命中率。在硬解析時，service process會試圖從DB buffer cache中讀取是否存在相關的快取資料。如果不存在相關資料，則伺服器程式會把相應的塊從datafiles裡讀入到DB buffer cache中，此時如果DB buffer cache空閒空間不足，則會觸發寫操作—DBWn。就會觸發DBWr程式，將DB buffer cache中髒資料寫入資料檔案。騰出來的空間寫入新資料。如果DB buffer cache太小命中率會降低，會增加物理I/O。

2.3 合理設定 redo log buffer 和redo日誌組
    當redo log files裡的一組日誌被寫滿時，在開啟歸檔的情況下會觸發ARCn程式對日誌進行歸檔，並切換到下一組日誌進行迴圈寫。當日志再次切換回該組日誌但還沒有完成歸檔時，資料庫就會等待其完成歸檔再寫入新的日誌，這將導致大量的效能下降。所以應該適當增加日誌檔案組的個數和每組日誌的大小。但是，當日志組個數很多時並且每組很小時會導致日誌組頻繁的進行切換，會觸發CKPT檢查點程式，檢查點程式會把scn寫進資料檔案頭和控制檔案中，當需要例項恢復時，會讀取控制檔案中的最後一個檢查點位置作為起點，運用redo進行前滾。檢查點程式會觸發DBWn程式將DB buffer cache中的髒塊頻繁寫到datafiles中，導取資料時先要將datafiles中的資料塊讀到DB buffer cache中，增長了SQL語句的執行時間。所以，正確的設定好日誌組的個數以及每組的大小很重要。

2.4 儘量減少排序
    如果面臨order by 排序時，將要進行PGA sort記憶體排，如果資料量很大，PGA不能滿足記憶體排所需空間時，就需要與磁碟上的臨時表空間進行儲存分配交換，這個磁碟分配交換將要對臨時檔案頻繁進行寫和讀，因為對磁碟頻繁操作，導致系統效能大大降低。此時，需要將PGA優化，如果不得不使用臨時檔案時，就需要建立多個臨時檔案，併發降低讀寫壓力，以達到優化效果。


DBA_建瑾
2013.11.20

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29324876/viewspace-777153/，如需轉載，請註明出處，否則將追究法律責任。