萬盛學電腦網

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

Oracle SQL 效能調優實戰技巧大全(含分頁查詢優化)

SQL 調優是 Oracle 效能優化投資報酬率最高的領域。以下彙整實戰驗證的核心技巧,涵蓋存取路徑、連接順序、函數使用、大量資料處理等面向。

1. 表存取順序與驅動表選擇(RBO 環境)

Oracle 解析器以「由右至左」處理 FROM 子句,最後列出的表為驅動表。多表連接時,應將筆數最少的表放在最後(作為驅動表);三表以上連接,優先選擇交叉表(被其他表引用最多的表)作為驅動表。

2. WHERE 條件撰寫順序

解析器以「自下而上」解析 WHERE 子句。原則:表連接條件寫在前面,過濾性最強(回傳行數最少)的條件寫在最後,讓優化器盡早縮小結果集。

3. 避免 SELECT *

* 需透過資料字典展開所有欄位,增加解析負擔與網路傳輸量。明確列出所需欄位,亦利於計畫穩定。

4. 減少資料庫往返次數

每次 SQL 執行皆伴隨解析、綁定、執行、擷取等開銷。批次作業宜使用陣列繫結、FORALL、BULK COLLECT 等批量介面。

5. 調大 ARRAYSIZE 參數

在 SQL*Plus、SQL*Forms、Pro*C 中將 ARRAYSIZE 設為 200(預設 15),單次擷取行數增大,網路往返大幅減少。

6. 善用 DECODE / CASE 避免重複掃描

單次掃描搭配 DECODE 或 CASE 完成多條件分支聚合,替代多次掃描同一張表或自連接。

7. 整合獨立查詢

多個無關聯的簡單查詢可合併為單一 SQL(如 UNION ALL 或條件聚合),減少解析與往返。

8. 高效刪除重複資料

DELETE FROM emp e
WHERE e.rowid > (SELECT MIN(x.rowid)
                 FROM emp x
                 WHERE x.emp_no = e.emp_no);

利用 ROWID 定位唯一保留行,避免全表掃描排序。

9. TRUNCATE 替代 DELETE(全表清空)

TRUNCATE 為 DDL,不產生 UNDO、不觸發 Trigger、瞬時釋放 High Water Mark,速度比 DELETE 快數個數量級。注意:不可回滾,不適用於部分刪除。

10. 分頁查詢最佳寫法(Oracle 12c+ / 11g 相容)

-- 12c+ 標準語法
SELECT * FROM t_order
ORDER BY order_date DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- 11g 相容寫法
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM t_order ORDER BY order_date DESC
  ) a WHERE ROWNUM <= 30
) WHERE rn > 20;

11. 統計資訊與執行計畫穩定

  • 定期 DBMS_STATS.GATHER_TABLE_STATS,啟用 AUTO_SAMPLE_SIZE 並收集 Histogram。
  • 關鍵 SQL 使用 SQL Plan Baseline 或 OPT_ESTIMATE Hint 固定優質計畫。

12. 索引設計黃金法則

  • 選擇度高(唯一值/總筆數 > 1%)優先建 B*Tree。
  • 低選擇度欄位(如性別、狀態)考慮 Bitmap Index(僅限 DW/OLAP)。
  • 複合索引遵循「最左前綴」與「等值在前、範圍在後」原則。
  • 定期執行「索引監控」清理無用索引(參考文章 147581)。

掌握上述技巧並結合 EXPLAIN PLAN、SQL_TRACE、AWR/ASH 實測驗證,即能系統性解決 80% 以上的 SQL 效能問題。

copyright © 萬盛學電腦網 all rights reserved