Oracle 10g SQL 優化新特性：CBO 默認、CPU Costing、Hint 增強、Query Block Name 與 AWR 整合

前言

Oracle 10g 在優化器架構上實現重大躍遷：CBO 正式成為默認、引入 CPU 成本模型、大幅擴充 Hint 體系、支持 Query Block Name 精準控制、AWR/ASH/ADDM 三位一體診斷。本文系統解析這些新特性及實戰應用。

一、優化器模式變革

影響

• 無統計信息表不再走 RBO，改用動態採樣 (默認級別 2)
• RULE/CHOOSE Hint 僅用於調試對比，生產禁用
• 統計信息收集成為核心基建：DBMS_STATS.GATHER_*_STATS 定時作業

二、CPU Costing 成本模型

1. 9i vs 10g 成本計算

2. 系統統計收集 (關鍵前置)

-- 收集系統統計 (需代表性負載期間)
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');
-- 運行典型業務負載 30-60 分鐘
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

-- 或直接收集 NOWORKLOAD (無負載估算)
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');

3. 關鍵系統統計指標

• CPUSPEEDNW：CPU 速度 (百萬周期/秒)
• IOSEEKTIM：I/O 尋道時間 (毫秒，默認 10)
• IOTFRSPEED：I/O 傳輸速度 (字節/毫秒，默認 4096)
• MBRC：多塊讀計數 (實測值)
• MAXTHR：最大吞吐量
• SLAVETHR：並行從進程吞吐

4. 觀察執行計劃新視圖

-- 10g+ 推薦：顯示 CPU Cost、Time、Outline Data
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number, 'ALL'));

-- 關鍵欄位：
-- Cost (%CPU)  -- 百分比表示 CPU 佔比
-- Time         -- 預估耗時 (基於系統統計)
-- Outline Data -- 完整 Hint 等效形式

三、新增 Hint 體系 (10g 關鍵增強)

1. Index Skip Scan 相關

2. 否定類 Hint (精細控制)

3. 精準連接控制

-- USE_NL_WITH_INDEX(@block table [index])
-- 指定 NLJ 內層表及其索引，CBO 決定是否採用
SELECT /*+ USE_NL_WITH_INDEX(@sel$1 emp emp_pk) */ *
FROM dept d, emp e
WHERE d.deptno = e.deptno;

4. Query Block Name (QB_NAME) - 里程碑特性

為子查詢/視圖命名，實現 Hint 精準定位

語法

SELECT /*+ QB_NAME(main_q) */ *
FROM t1
WHERE id IN (SELECT /*+ QB_NAME(sub_q) FULL(@sub_q t1) */ id FROM t1 WHERE id > 100);

驗證效果

-- 未指定 QB_NAME 時：預設計劃 (Index Range Scan)
-- 指定 FULL(@sub_q t1)：子查詢強制全表掃描
-- 指定錯誤 FULL(@wrong_q t1)：忽略 Hint，恢復預設計劃

自動 QB_NAME

• 10g+ 優化器自動為每個查詢塊分配名稱：SEL$1, SEL$2, INS$1 等
• 可通過 DBMS_XPLAN.DISPLAY_CURSOR(..., 'OUTLINE') 查看

四、10gR2 進一步增強

1. AWR 報告增強

• 專用 ASH 報告腳本：$ORACLE_HOME/rdbms/admin/ashrpt.sql
• 類 Statspack 的單 SQL 歷史統計腳本

2. 參數自動調節

• DB_FILE_MULTIBLOCK_READ_COUNT：根據訪問模式自動調節，無需人工調優

3. SQL 自動優化框架

• SQL Tuning Advisor：自動分析高負載 SQL，推薦索引、統計、結構調整
• SQL Profile：持久化優化器輔助信息，修正估算偏差，無需改代碼
• Enterprise Manager 集成圖形化調優流程

4. 關鍵動態視圖新增

五、實戰遷移檢查清單

結語

Oracle 10g 確立了「統計驅動、CPU Costing、QB_NAME 精準控制、自動優化框架」的現代優化器範式。升級遷移的核心不是語法適配，而是思維轉變：從手工 Hint 微調轉向統計信息治理、系統統計收集、自動優化顧問協同的系統化調優體系。