Oracle 10g R2+：SQL Tuning Set 跨實例匯出/匯入實戰

SQL Tuning Set (STS) 是 SQL Tuning Advisor 的輸入載體，包含 SQL 文本、執行計畫、綁定變數、執行統計等上下文。Oracle 10g R1 僅能在同實例分析，R2 引入 DBMS_SQLTUNE 包裝/解包程序，支援跨實例遷移（如：生產庫建立 STS → 匯出 → 測試庫匯入分析），避免在生產環境直接調優造成負載與權限風險。

跨實例遷移完整流程

1. 建立 STS（來源實例）
2. 建立中轉表（來源實例）
3. 將 STS 打包進中轉表（來源實例）
4. 遷移中轉表（Data Pump / DB Link / 物理複製）
5. 解包 STS（目標實例）
6. 分析調優（目標實例）

詳細步驟與 PL/SQL 代碼

1. 建立 STS（以 AWR 快照為來源）

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name  => 'MY_STS',
    description  => '高負載 SQL 集合，供測試庫調優'
  );
END;
/

-- 從 AWR 快照範圍載入（snap_id 100~200）
DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) FROM TABLE(
      DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
        begin_snap => 100,
        end_snap   => 200,
        basic_filter => 'elapsed_time > 1000000'  -- 微秒，篩選慢 SQL
      )
    ) p;
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MY_STS', populate_cursor => cur);
  CLOSE cur;
END;
/

2. 建立中轉表

BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
    table_name => 'STS_STAGING_TABLE',
    schema_name => 'DBA_TOOLS'  -- 建議專用 schema
  );
END;
/

3. 打包 STS 進中轉表

BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
    sqlset_name      => 'MY_STS',
    staging_table_name => 'STS_STAGING_TABLE',
    staging_schema_owner => 'DBA_TOOLS'
  );
END;
/

4. 遷移中轉表（Data Pump 範例）

# 來源庫匯出
expdp dba_tools/pwd DIRECTORY=dp_dir DUMPFILE=sts_staging.dmp TABLES=STS_STAGING_TABLE

# 目標庫匯入
impdp dba_tools/pwd DIRECTORY=dp_dir DUMPFILE=sts_staging.dmp TABLES=STS_STAGING_TABLE REMAP_SCHEMA=DBA_TOOLS:DBA_TOOLS

5. 目標庫解包 STS

BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
    sqlset_name      => 'MY_STS',
    staging_table_name => 'STS_STAGING_TABLE',
    staging_schema_owner => 'DBA_TOOLS',
    replace          => TRUE  -- 同名 STS 覆蓋
  );
END;
/

6. 目標庫啟動調優分析

-- 透過 DBMS_SQLTUNE 執行調優任務
DECLARE
  tname VARCHAR2(100);
BEGIN
  tname := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sqlset_name  => 'MY_STS',
    task_name    => 'TUNE_MY_STS',
    scope        => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit   => 3600
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(tname);
END;
/

-- 查看報告
SET LONG 1000000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_MY_STS') FROM DUAL;

關鍵權限需求

• 來源/目標用戶需具備 ADMINISTER SQL TUNING SET 權限（通常由 DBA 角色包含）。
• Data Pump 需 DATAPUMP_EXP_FULL_DATABASE / DATAPUMP_IMP_FULL_DATABASE。

常見問題與對策

問題	原因	解決
UNPACK 後 STS 為空	PACK 時未指定正確 schema/table	核對 staging_schema_owner、staging_table_name 大小寫
目標庫調優建議無法應用	目標庫統計資訊/索引/參數與來源差異大	目標庫同步統計資訊、模擬生產參數、或僅參考建議人工驗證
跨版本遷移失敗	STS 內部格式版本不相容	同大版本遷移較安全；跨大版本建議在目標版本重新擷取 SQL

最佳實務建議

• 定期自動化：排程建立 STS → PACK → Data Pump → 目標庫 UNPACK → 執行調優任務 → 郵件發送報告。
• 最小權限原則：開發/調優人員僅在測試庫操作 STS，不接觸生產庫。
• 保留歷史 STS：不同時期的 STS 可對比調優效果，追蹤 SQL 演變。