Oracle 效能調優實戰：系統參數優化完整指南

前言

Oracle 作為高效能資料庫軟體，其效能表現高度依賴參數配置。效能調優主要分為兩大面向：資料庫管理員（DBA）透過系統參數調整達到優化，以及開發人員透過應用程式優化達到調整。本文專注於系統參數層面的調優策略，幫助 DBA 充分發揮硬體資源效能。

一、記憶體分配調整：SGA 核心配置

系統全域區（System Global Area，SGA）是 Oracle 實例啟動時分配的共享記憶體區域，包含資料庫緩衝區、共用池、大型池等關鍵元件。SGA 大小對系統效能影響極大，預設參數僅適用於低配置電腦，生產環境必須根據實際硬體調整。以 Alpha 1200 伺服器為例，建議 SGA 設定在 160 MB 左右。

關鍵初始化參數

• DB_BLOCK_BUFFERS：控制 SGA 中資料庫緩衝區快取的緩衝區數量，是決定 SGA 大小與資料庫效能最關鍵的參數。數值越高，命中率越高，磁碟 I/O 越少。每個緩衝區大小等於 DB_BLOCK_SIZE。
• SHARED_POOL_SIZE：分配給共用 SQL 區的位元組數，共用池包含函式庫快取、資料字典快取及會話資訊，是 SGA 最大的消耗成分。

二、函式庫快取調整

函式庫快取包含私有/共用 SQL 區與 PL/SQL 區。確保其足夠大，可使 Oracle 在共用池保留解析後的 SQL 陳述式，提升執行效率。透過查詢 V$LIBRARYCACHE 視圖計算命中率：

SELECT SUM(pins), SUM(reloads) FROM v$librarycache;

若 sum(reloads)/sum(pins) ≈ 0 表示命中率良好；若大於 1，需在初始化參數檔中增加 SHARED_POOL_SIZE。

三、資料字典快取調整

資料字典快取儲存資料庫結構、使用者、物件等中繼資料。查詢 V$ROWCACHE 計算命中率：

SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100 FROM v$rowcache;

命中率若大於 90% 為適宜，否則需擴大共用池。

四、資料庫緩衝區快取調整

透過 V$SYSSTAT 監控命中率：

SELECT name, value FROM V$SYSSTAT WHERE name IN ('db block gets','consistent gets','physical reads');

命中率公式：1 - physical reads / (db block gets + consistent gets)。若命中率低於 0.6~0.7，應增加 DB_BLOCK_BUFFERS。

五、磁碟 I/O 優化

磁碟 I/O 通常是效能瓶頸。查詢 V$FILESTAT 了解各資料檔讀寫頻率（phyrds/phywrts），針對高頻檔案採取策略：

• 將 I/O 均勻分散於多顆磁碟
• 為資料表與索引建立不同表空間
• 將資料檔與重做日誌檔分置不同磁碟
• 減少繞過 Oracle Server 的磁碟 I/O

六、競爭問題排查與解決

1. PROCESSES 參數調整

定義最大同時連線進程數，預設 50，含背景進程。建議調整為 200。

2. 調度程序競爭

查詢 V$DISPATCHER，若某協定忙碌比率超過 50%，增加 MTS_DISPATCHERS。

3. 多執行緒服務進程競爭

查詢 V$SYSSTAT，若平均等待時間持續增長且達到 MTS_MAX_SERVERS 上限，需調大該參數。

4. 重做日誌緩衝區競爭

查詢 redo log space requests，數值應接近 0，否則增加 LOG_BUFFER。

5. 回滾段競爭

查詢 V$WAITSTAT 與 V$SYSSTAT，若 class/sum(value) > 10%，考慮增加回滾段。設定原則：使用者數 <16 用 4 個；16~32 用 8 個；≥32 用 n/4 但不超過 50。

6. Free List 競爭

多進程同時插入資料時發生。查詢 V$WAITSTAT 的 free list 類別，若比率超過 1%，增加該表的 FREELISTS 參數。