Oracle 10g SQL Access Advisor 實戰：使用 QUICK_TUNE 快速獲取索引與物化視圖優化建議

背景：顧問框架與 SQL Access Advisor

Oracle 10g 引入 **Advisory Framework**，SQL Access Advisor (訪問顧問) 專門分析工作負載，推薦 **索引、物化視圖、物化視圖日志** 的創建/刪除/保留，以提升查詢性能。10gR1 需手動調用 DBMS_ADVISOR 多個過程 (CREATE_TASK, SET_TASK_PARAMETER, CREATE_SQLWKLD...)，繁瑣易錯。

10gR2 簡化神器：DBMS_ADVISOR.QUICK_TUNE

單一過程完成：創建任務 → 設置默認參數 → 創建工作負載 (可直接傳入單條 SQL) → 執行分析 → 生成建議。

前置條件

• 用戶擁有 ADVISOR 權限：GRANT ADVISOR TO user;
• 分析用戶對查詢涉及表擁有 SELECT 權限

調用示例

VARIABLE task_name VARCHAR2(30);
EXEC :task_name := 'MY_QUICK_TUNE_TASK';

BEGIN
  DBMS_ADVISOR.QUICK_TUNE(
    advisor_name      => DBMS_ADVISOR.SQLACCESS_ADVISOR,
    task_name         => :task_name,
    attr1             => 'SELECT * FROM sales WHERE amount_sold > 1000 AND cust_id = 123',
    attr2             => NULL,
    attr3             => NULL,
    template          => NULL,
    implement         => FALSE
  );
END;
/
-- attr1: 目標 SQL 文本 (單條)
-- implement: TRUE=自動實施建議 (慎用), FALSE=僅生成報告

結果查看視圖

查詢建議腳本 (可直接執行)

SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name) FROM dual;

返回 CLOB，包含完整 DDL 腳本。

實戰案例解讀

目標 SQL：SELECT * FROM sales WHERE amount_sold > 1000 AND cust_id = 123

輸出建議：

• 發現 amount_sold 列無索引，全表掃描代價高
• 推薦創建 **位圖索引** CREATE BITMAP INDEX sales_amt_bmx ON sales(amount_sold);
• 理由：amount_sold 重複值多 (低基數)，位圖索引適合星型查詢/數倉場景

進階用法：基於工作負載 (SQLWKLD) 分析