Oracle 10g SQL Access Advisor：索引建議自動生成實戰指南

Oracle 10g 引入 Advisory Framework，其中 SQL Access Advisor (Access Advisor) 可自動分析工作負載，推薦索引與物化視圖建立/刪除建議。10gR2 新增 DBMS_ADVISOR.QUICK_TUNE 單一程序即可完成任務建立、負載收集、分析與建議生成，大幅降低使用門檻。

快速上手：QUICK_TUNE 單條 SQL 分析

-- 1. 授予權限
GRANT ADVISOR TO scott;

-- 2. 執行單條 SQL 調優
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 orders WHERE customer_id = :b1 AND order_date > SYSDATE-30',
    attr2             => 'SCOTT',  -- 執行用戶
    attr3             => 'TASK'    -- 模板
  );
END;
/

查看建議結果

-- 任務日誌
SELECT task_name, status, recommendation_count
FROM dba_advisor_log
WHERE task_name = 'MY_QUICK_TUNE_TASK';

-- 具體建議
SELECT rec_id, rank, benefit, type, attr1 AS action
FROM dba_advisor_recommendations
WHERE task_name = 'MY_QUICK_TUNE_TASK'
ORDER BY rank;

-- 建議執行腳本
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('MY_QUICK_TUNE_TASK') FROM dual;

完整工作負載分析流程（生產級）

1. 建立任務：DBMS_ADVISOR.CREATE_TASK('SQLACCESS_ADVISOR', 'MY_FULL_TASK')
2. 設定參數：DBMS_ADVISOR.SET_TASK_PARAMETER('MY_FULL_TASK', 'ANALYSIS_SCOPE', 'INDEX')、STORAGE_CHANGE、WORKLOAD_SCOPE
3. 建立工作負載：DBMS_ADVISOR.CREATE_SQLWKLD('MY_WKLD')，或從 AWR/_cursor_cache/用戶表匯入
4. 連結負載到任務：DBMS_ADVISOR.ADD_SQLWKLD_REF('MY_FULL_TASK', 'MY_WKLD')
5. 執行分析：DBMS_ADVISOR.EXECUTE_TASK('MY_FULL_TASK')
6. 查看/實施建議：DBA_ADVISOR_RECOMMENDATIONS、GET_TASK_SCRIPT

實戰案例解讀

針對查詢：

SELECT * FROM sales WHERE amount_sold > 10000;

Advisor 輸出建議：

• 建立 Bitmap Index：CREATE BITMAP INDEX sales_amt_bmx ON sales(amount_sold)
• 理由：amount_sold 重複值多、選擇度低、適合 Bitmap Index，避免 Full Table Scan。
• 預估效益：BENEFIT 欄位量化 IO 減少量。

Advisor 類型速查

避坑指南

1. 權限：分析用戶需有查詢涉及表的 SELECT 權限，否則建議不準。
2. 統計資訊：執行前確保相關表統計資訊最新（DBMS_STATS.GATHER_TABLE_STATS），Advisor 依賴統計資訊估算成本。
3. 工作負載代表性：單條 SQL 用 QUICK_TUNE；全系統優化必須收集完整業務高峰期工作負載（AWR Snapshot 範圍）。
4. 建議審核：Advisor 建議僅供參考，上線前必須在測試環境驗證寫入效能影響、空間增長、應用相容性。

SQL Access Advisor 是「顧問」，不是「執行者」。DBA 的價值在於結合業務語境、寫入模式、維護窗口，判斷哪些建議值得採納、哪些需修改參數、哪些應拒絕。