Oracle 10g SQL Access Advisor 實戰：利用 QUICK_TUNE 獲取索引建議

背景

Oracle 10g 引入顧問框架，SQL Access Advisor (訪問顧問) 專注於索引與物化視圖推薦。
10gR2 新增 DBMS_ADVISOR.QUICK_TUNE 單步封裝，大幅降低使用門檻。

核心流程：三步獲取建議

1. 權限準備

GRANT ADVISOR TO app_dba;
-- 被分析 SQL 涉及的表需有 SELECT 權限

2. 調用 QUICK_TUNE

VARIABLE task_name VARCHAR2(100);
EXEC :task_name := 'IDX_ADVISOR_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI');

BEGIN
  DBMS_ADVISOR.QUICK_TUNE(
    advisor_name      => 'SQL Access Advisor',
    task_name         => :task_name,
    attr1             => 'SELECT /*+ FULL(t) */ * FROM large_table t WHERE filter_col = :b1',
    attr2             => 'EXAMPLE_TASK',
    template          => 'SQL_ACCESS_WORKLOAD'  -- 內置模板
  );
END;
/
-- 也可直接傳入 SQL 文本 (attr1)，或指定 SQLSET (attr2)

3. 查看結果

-- 任務日誌與建議數
SELECT task_name, status, recommendation_count
FROM dba_advisor_log
WHERE task_name = :task_name;

-- 具體建議 (索引/物化視圖/分區)
SELECT rec_id, rank, benefit, command, attr1 AS object_name, attr2 AS tablespace
FROM dba_advisor_recommendations
WHERE task_name = :task_name
ORDER BY rank;

-- 執行命令細節 (DDL)
SELECT rec_id, action_id, command, attr1 AS sql_text
FROM dba_advisor_actions
WHERE task_name = :task_name
ORDER BY rec_id, action_id;

-- 獲取完整執行腳本 (CLOB)
SET LONG 1000000
SET PAGESIZE 0
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name) FROM DUAL;

實戰案例解讀

輸入 SQL：SELECT * FROM sales WHERE amount_sold > 1000
Advisor 分析：

• 全表掃描成本高
• amount_sold 列無索引
• 數值分布有大量重複值 → 推薦位圖索引

生成建議：

CREATE BITMAP INDEX sales_amt_bmx ON sales(amount_sold) TABLESPACE idx_ts;

進階：自定義 Workload (SQL Tuning Set)

-- 1. 創建 STS
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'MY_WORKLOAD');
END;
/

-- 2. 從 AWR/游標緩存/用戶表加載 SQL
DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
      'parsing_schema_name = ''APP_OWNER''')) p;
  DBMS_SQLTUNE.LOAD_SQLSET('MY_WORKLOAD', cur);
  CLOSE cur;
END;
/

-- 3. 創建任務並關聯 STS
BEGIN
  DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', 'MY_IDX_TASK');
  DBMS_ADVISOR.SET_TASK_PARAMETER('MY_IDX_TASK', 'WORKLOAD_SCOPE', 'FULL');
  DBMS_ADVISOR.ADD_STS_REF('MY_IDX_TASK', 'MY_WORKLOAD');
  DBMS_ADVISOR.EXECUTE_TASK('MY_IDX_TASK');
END;
/

-- 4. 查看建議 (同前)

建議評估與落地清單

1. 收益值 (BENEFIT)：預估性能提升百分比，優先落地高收益建議。
2. 空間成本：索引佔用空間 vs 查詢提升，權衡寫入開銷。
3. DML 影響：高頻 INSERT/UPDATE 表慎加索引，考慮 INVISIBLE INDEX (11g+) 驗證。
4. 測試環境驗證：EXPLAIN PLAN 對比 Before/After，確認執行計劃變更符合預期。
5. 變更管理：納入發布流程，保留回滾腳本 (DROP INDEX)。

版本演進提示

• 11g+：SQL Access Advisor 整合入 DBMS_AUTO_TASK 自動運行 (維護窗口)，DBA_ADVISOR_TASKS 可見歷史。
• 12c+：引入 SQL Tuning Advisor (針對單條 SQL) 與 SPM (SQL Plan Management) 協同，建議可直接演化為 SQL Plan Baseline。
• 19c/21c：Autonomous Database 內建自動索引 (Auto Indexing)，全自動創建/重建/刪除。

總結： QUICK_TUNE 讓索引優化從「經驗猜測」走向「數據驅動」。建議將「收集代表性 Workload → 定期跑 Advisor → 人工評估 → 灰度發布」形成標準化優化閉環，而非臨時救火。