萬盛學電腦網

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

Oracle 調優入門與進階:外部環境、Row Resequencing、SQL、排序與爭用

調優核心原則

Oracle 不獨立運行,外部資源(CPU、記憶體、網路)不足時,任何內部調優無效。優先排查:

  • CPU:運行隊列 > CPU 核心數 → 受限。對策:加 CPU、關閉 Parallel Query。
  • 記憶體:發生分頁 → 不足。對策:加記憶體、縮減 SGA、關閉 MTS。
  • 網路:大量 Net8 通訊拖慢 SQL。

工具:vmstat、glance、top、sar。

Row Resequencing(行重排序)減少磁碟 I/O

I/O 是響應時間最大成分,磁碟 I/O 比記憶體慢 10,000 倍。若交易透過主鍵索引範圍掃描存取表,將表按主鍵順序物理重排(CTAS),可大幅減少區塊讀取。

範例

SELECT salary FROM employee WHERE last_name LIKE 'B%';
  • 未重排:100 筆需 ≥100 次物理讀(資料分散不同區塊)。
  • 重排後:同樣查詢僅需 3 次讀(1 次索引 + 2 次資料區塊)。

效能提升視原亂序程度與存取範圍而定,大表範圍查詢可提升 3 倍。

判斷依據:DBA_INDEXES.CLUSTERING_FACTOR 接近區塊數=已排序;接近筆數=亂序。

工具:CREATE TABLE AS SELECT (CTAS)、Oracle 9i+ 內建重組工具。

SQL 調優四大鐵律(佔 90% 工作量)

  1. 消除不必要的大表全表掃描:有序表回傳 <40%、無序表 <7% 可改用索引。手段:加索引(B-tree/bitmap/function-based)、Hint 強制索引。
  2. 小表全表掃描入緩存:Oracle 7 用 ALTER TABLE ... CACHE;8i+ 用 KEEP 池。
  3. 確保最優索引使用:多索引時驗證 Oracle 選擇正確索引。
  4. 確保最優 Join 方法:Nested Loop / Hash / Sort-Merge 視資料量與索引而定。

調整排序操作

  • 觸發排序:ORDER BY、GROUP BY、CREATE INDEX、索引不足導致 Merge Sort Join。
  • 專用連接:PGA 大小由 SORT_AREA_SIZE 決定;共享伺服器:Large Pool。
  • 所有 Session 排序區大小固定,需平衡避免磁碟排序(比記憶體排序慢 14,000 倍)。
  • 磁碟排序發生於 TEMP 表空間。

調整爭用

表/索引參數(PCTFREE、INITRANS、MAXTRANS、FREELISTS)直接影響 INSERT/UPDATE 效能與爭用。

copyright © 萬盛學電腦網 all rights reserved