oracle效能最佳化簡介

最近這段時間剛做了一項效能最佳化的工作，根據一周的statspack報告，寫了一個最佳化方案。

一、系統現有的主要效能問題
從最近一周比較典型的STATSPACK報告來看，系統中主要的等待事件如下：

Top 5 Timed Events

~~~~~~~~~~~~~~

Event Waits Time (s) % Total Ela Time

-------------------------------------------- ------------ ----------- ----

CPU time 5,819 46.04

wait for unread message on broadcast channel 3,356 3,374 26.70

db file sequential read 1,100,056 2,454 19.41

buffer busy waits 51,054 274 2.16

db file scattered read 154,540 220 1.74

-------------------------------------------------------------

資料庫中有46％的時間花費在CPU相關的操作上。CPU time可以分為3個部分，即CPU time= parse time CPU + recursive CPU usage +other CPU usage，其中，parse time CPU表示解析SQL語句所花費的CPU時間，recursive CPU usage表示一些遞迴SQL操作花費的CPU時間，other CPU usage代表邏輯讀取花費的CPU時間。

變換一下上述公式，other CPU usage＝CPU time － parse time CPU － recursive CPU usage，將我們的STATSPACK報告的相關資料帶入該公式other CPU usage＝5819－584－109＝5126，由此可見，88％的CPU時間花費在了邏輯讀取資料的操作上，另外有10％的CPU時間花費在瞭解析SQL語句上。我們可以從最佳化SQL語句方面入手，降低邏輯讀取。另外，我們也可以在降低SQL解析數方面做些工作。

等待事件wait for unread message on broadcast channel是一個空閑事件，我們無須處理。

db file sequential read等待事件佔總時間的19％，表示物理讀取資料，減少該等待可以從兩個方面來考慮，首先是最佳化SQL，降低不必要的物理讀取；另外也可以適當的增大資料緩衝區，使儘可能多的資料緩衝到記憶體中。

另外一個等待事件buffer busy waits是由大量邏輯讀取所導致的記憶體等待，該事件也可以通過最佳化SQL語句來消除。

二、解決方案
根據系統中存在的上述問題，提出如下的最佳化方案：

1） 最佳化一些邏輯讀和物理讀較多的SQL，可以有效減少資料的讀取，也可以減少記憶體爭用；

2） 對一些執行頻繁的SQL使用綁定變數，減少花費在解析SQL語句上的CPU時間和記憶體栓鎖；

3） 適當增大資料緩衝區尺寸，減少物理讀。