萬盛學電腦網

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

Oracle SQL 優化核心原則與實戰技巧速查表

共享池複用六大鐵律

  1. SQL 文本**逐字元完全相同**(含空白、大小寫)才能複用游標。
  2. 繫結變數名稱、數量、資料型別必須一致。
  3. 優先使用 ANSI JOIN 語法,減少外連接 `+` 導致的解析差異。
  4. 避免深層巢狀子查詢(>3 層),改用 `WITH` 子句或暫存表分解。
  5. 善用並行查詢(`PARALLEL` Hint/物件屬性)釋放 CPU 壓力。
  6. 大表查詢**必加索引提示或確保統計資訊新鮮**,避免全表掃描。

優化四階段法

階段動作工具/指令
1. SGA 調校調整 Shared Pool、Buffer Cache、Large Pool 大小`V$SGASTAT`、`V$SGA_TARGET_ADVICE`
2. SQL 語句優化改寫、加 Hint、建索引、統計資訊收集`EXPLAIN PLAN`、`SQL TRACE`、`DBMS_XPLAN`、`SQL Tuning Advisor`
3. 結構調整分區、MV、索引組織表、簇表`DBMS_REDEFINITION` 線上重定義
4. 應用架構調整快取層、讀寫分離、分庫分表架構師層面決策

寫 SQL 實戰黃金法則

#原則反例正例
1大表欄位**勿做隱性轉換**`WHERE TO_CHAR(dt) = '2024-01-01'``WHERE dt = DATE '2024-01-01'`
2優先走索引,條件順序置前`WHERE name='A' AND indexed_col=1``WHERE indexed_col=1 AND name='A'`
3避免全表掃描,加強過濾條件`SELECT * FROM big_table``SELECT col1,col2 FROM big_table WHERE dt>=...`
4慎用 IN/EXISTS,大集合改 JOIN`WHERE id IN (SELECT ... 10萬筆)``JOIN (SELECT ...)` 或 `GLOBAL TEMP TABLE`
5善用集合運算替代 OR`WHERE a=1 OR a=2 OR a=3``WHERE a IN (1,2,3)` 或 `UNION ALL`

進階技巧:綁定變數窺探與適應性游標共享

  • 11g+ 預設開啟 **Adaptive Cursor Sharing (ACS)**,同一 SQL 不同綁定值可生成多個執行計畫。
  • 若發現計畫翻車,可用 `OPT_PARAM('_optimizer_adaptive_cursor_sharing','FALSE')` 關閉單一 SQL 的 ACS。
  • 歷史惡劣計畫可用 `DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE` 固定為 SQL Plan Baseline。

結語

SQL 優化無銀彈,但遵循「共享池複用 → 執行計畫優化 → 物理結構調整 → 架構演進」四階段,配合上述實戰法則,能解決 90% 以上效能問題。建議團隊建立 **SQL Review Checklist**,上線前強制走檢。

copyright © 萬盛學電腦網 all rights reserved