萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 效能調優五大面向:從外部環境到 SQL、排序、爭用的系統性優化框架

Oracle 調優非單一參數微調,而是跨越「外部環境 → 物理存取 → SQL → 排序 → 爭用」五大層次的系統工程。若外部資源(CPU/記憶體/網路)成瓶頸,任何內部調優皆事倍功半。

第一層:外部環境調優——調優的前提

資源瓶頸徵兆對策
CPURun Queue > CPU 核心數;vmstat sy/time 高增加 CPU、關閉 Parallel Query、優化高 CPU SQL
記憶體Paging/Swapping 頻繁;vmstat pi/po 持續非零加記憶體、縮減 SGA、關閉 MTS/Shared Server
網路Net8/TCP 封包重傳高;SQL*Net wait 事件佔比大啟用 SDU/TCPIP.NODELAY、就近部署、連線池

工具:vmstat、top、sar、glance、nmon。目標:確保 OS 層面資源充足、無爭用。

第二層:Row Re-sequencing(列重組)減少磁碟 I/O

將表資料依查詢順序(如索引鍵)重排,使相關行集中在同一區塊,單次多塊讀取即可滿足查詢,大幅降低 db file scattered read。手段:ALTER TABLE ... MOVE、CTAS、DBMS_REDEFINITION、分區表。

第三層:SQL 調優——最高 ROI 領域

  • 優化器模式:生產環境強制 OPTIMIZER_MODE=ALL_ROWS 或 FIRST_ROWS_n,避免 CHOOSE。
  • 統計資訊:DBMS_STATS 定期收集,Histogram 用於斜態欄位。
  • 存取路徑:消除不必要的 Full Table Scan、驅動表錯誤、索引失效(隱式轉換、函數包裹欄位)。
  • 連接方法:Nested Loops(小結果集)、Hash Join(大表等值連接)、Sort Merge Join(無索引大表)。
  • 改寫技巧:EXISTS 替代 IN、UNION ALL 替代 UNION、WITH 子查詢物化、避免 SELECT *。

第四層:排序調優

  • SORT_AREA_SIZE / PGA_AGGREGATE_TARGET 適度調大,減少磁碟排序。
  • 索引預排序:INDEX ... ORDER BY 消除 Sort 操作。
  • 避免大結果集 DISTINCT/GROUP BY,改用應用層去重或物化視圖。

第五層:爭用調優

調優閉環流程

  1. 確認外部資源無瓶頸。
  2. AWR/ASH 定位 Top Wait Events 與 Top SQL。
  3. 由淺入深:SQL 改寫 → 索引/統計 → 物理結構 → 參數調整。
  4. 變更後必須壓測驗證,防止副作用。
  5. 建立基線,持續監控,讓調優常態化。

遵循五層框架,由外而內、由粗到細,避免「頭痛醫頭」式隨機調參,才能持續釋放 Oracle 效能潛力。

copyright © 萬盛學電腦網 all rights reserved
爭用類型典型等待事件對策
Buffer Busy Waitsbuffer busy waits增加 freelists/freelist groups、使用 ASSM、分區
Enqueue/Lockenq: TX - row lock contention縮短交易、降低隔離級別、索引外鍵
Latchlatch: cache buffers chains減少熱塊競爭、調大 _db_block_hash_buckets
Redo Loglog file sync, log file switch調大 Redo Log、多組多工、快速儲存裝置