Oracle SQL 優化實戰：Stored Outline 固定執行計畫、Hints 與統計資訊管理

當核心 SQL 執行計畫因統計資訊變動而惡化，導致效能劇烈波動時，Stored Outline（預存大綱） 能以 Hints 形式鎖定最佳計畫，提供穩定效能保障。本文詳細解析 Outline 建立、啟用、私有大綱編輯、Hints 注入與統計資訊管理全流程。

一、 優化器模式選擇

• RULE (RBO)：規則基礎，僅為相容舊版，不建議新系統使用
• COST (CBO)：成本基礎，依統計資訊選擇最低成本路徑，**現代系統預設且推薦**

確認模式：SHOW PARAMETER optimizer_mode，應為 ALL_ROWS 或 FIRST_ROWS_n。

二、 Stored Outline 核心機制

• 本質：一組 Hints 集合，儲於 OUTLN schema
• 匹配規則：SQL 文本**完全一致**（含大小寫、空白、換行）才會套用，與 Shared Pool 游標匹配邏輯相同
• 分類管理：透過 CATEGORY 區分環境（如 PROD、TEST），預設為 DEFAULT

三、 建立 Outline 兩大途徑

1. 自動捕捉（Session 級）

ALTER SESSION SET CREATE_STORED_OUTLINES = 'PROD_CAT';

該 Session 後續執行的所有 SQL 皆會自動生成 Outline 至指定 Category。

2. 手工建立（精準控制）

CREATE OR REPLACE OUTLINE ol_co_cl_join
FOR CATEGORY PROD_CAT
ON
SELECT co.crs_id, cl.class_name
FROM   courses co, classes cl
WHERE  co.crs_id = cl.crs_id;

四、 啟用 Outline

ALTER SESSION SET USE_STORED_OUTLINES = 'PROD_CAT';

或資料庫層級：ALTER SYSTEM SET USE_STORED_OUTLINES = 'PROD_CAT';

五、 Private Outline：線上安全調優利器

• 將公開 Outline 複製為私有副本，僅當前 Session 可見
• 可自由編輯 Hints 進行實驗，**不影響生產環境**
• 驗證無誤後，發布為公開 Outline 替換舊版

-- 建立私有大綱
ALTER SESSION SET USE_PRIVATE_OUTLINES = TRUE;
-- 編輯私有大綱（透過 DBMS_OUTLN_EDIT）
-- 發布
EXEC DBMS_OUTLN_EDIT.PUBLICIZE_OUTLINE('OL_CO_CL_JOIN');

六、 SQL 中直接注入 Hints

無需 Outline 機制，單一 SQL 即時生效：

CREATE INDEX gen_idx ON customers(cust_gender);

SELECT /*+ INDEX(customers gen_idx) */
  cust_last_name, cust_street_address, cust_postal_code
FROM   sh.customers
WHERE  UPPER(cust_gender) = 'M';

常用 Hints：INDEX、FULL、LEADING、USE_NL/HASH/MERGE、OPT_PARAM。

七、 EXPLAIN PLAN 快速驗證

1. 建立計畫表（一次即可）：@$ORACLE_HOME/rdbms/admin/utlxplan.sql
2. 生成計畫：EXPLAIN PLAN FOR SELECT ...
3. 查看：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 或執行 utlxplp.sql（含平行查詢資訊）

八、 統計資訊管理策略

九、 統計資訊查詢視圖

• 表級：DBA_TABLES / USER_TABLES
• 索引級：DBA_INDEXES
• 欄位級：USER_TAB_COL_STATISTICS
• 直方圖：DBA_HISTOGRAMS / DBA_TAB_HISTOGRAMS

十、 實務建議

1. 優先用 DBMS_STATS 定期收集統計，避免 CBO 盲目決策
2. 核心 SQL 部署 Outline 鎖定計畫，升級/統計變更前先在測試環境驗證 3. 單一 SQL 臨時優化用 Hints，長期固定用 Outline 4. 建立 PLAN_TABLE 並善用 DBMS_XPLAN 視覺化執行計畫 5. 直方圖僅對傾斜欄位建立（SIZE AUTO 或 SIZE SKEWONLY），避免過度收集

結合 Outline、Hints、統計資訊三大工具，構建「監控→診斷→固定→驗證」的 SQL 效能穩定機制。

1. 上一篇文章： Oracle 徹底移除與重裝指南：13 步驟清除殘留避免安裝失敗
2. 下一篇文章： Oracle 區塊效能優化：動態擴展、HWM、統計資訊與區塊大小四大支柱

Oracle 資料庫基礎

Oracle 資料一致性與事務恢復核心機制：UNDO、SCN 與閃回查詢實戰

前言：一致性是資料庫的靈魂，恢復是最後防線 Oracle 透

Oracle 異構資料合併：ROWNUM 虛擬欄位 vs 序列產生合併鍵的效能與正確性對決

一、問題背景：無共同鍵的兩資料集如何合併？ 兩張表 TABL

Oracle 10g SQL MODEL 子句：跨行計算的強大利器

教學目標與大綱 本教程將引導您掌握 Oracle Datab

Oracle 10g R2 數據倉庫與整合新特性深度體驗

1. 分區更改追蹤（PCT）免 MV Log 快速刷新 物化