您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

Oracle SQL Tuning Set (STS) 跨實例遷移完整流程：打包、導出、導入、分析

編輯：MySQL 資料庫相關知識

SQL Tuning Set (STS) 是 Oracle 10g SQL Tuning Advisor 的核心輸入對象，包含 SQL 文本、執行計劃、綁定變量、執行統計等上下文。Oracle 10gR2 引入 DBMS_SQLTUNE 套件支援 STS 跨實例遷移，本文詳細講解**中轉表法**完整流程：建立 STS → 打包進中轉表 → Data Pump 導出導入 → 解包還原 → 目標實例分析。

一、遷移架構圖解

源實例 (生產)                    目標實例 (測試/開發)
┌─────────────────┐             ┌─────────────────┐
│ 1. CREATE STS   │             │                 │
│ 2. PACK_STGTAB  │  Data Pump  │ 4. UNPACK_STGTAB│
│    _SQLSET      │  導出/導入  │    _SQLSET      │
│ 3. EXPDP 中轉表 │  ───────►   │ 5. SQL Tuning   │
└─────────────────┘   中轉表    │    Advisor 分析  │
                          ┌─────────────────┐
                          │ 6. 實施優化建議  │
                          └─────────────────┘

二、詳細步驟與代碼

步驟 1：源實例建立 STS (可選來源：AWR、Cursor Cache、SQLset、手工)

-- 方式 A：從 AWR 快照範圍建立
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name  => 'PROD_STS_202504',
    description  => 'Production workload for tuning'
  );
END;
/

DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) FROM TABLE(
      DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
        begin_snap => 1000,
        end_snap   => 1100,
        basic_filter => 'parsing_schema_name = ''SCOTT'''
      )
    ) p;
  DBMS_SQLTUNE.LOAD_SQLSET('PROD_STS_202504', cur);
  CLOSE cur;
END;
/

步驟 2：建立中轉表並打包 STS

-- 2.1 建立中轉表 (需有 CREATE TABLE 權限)
BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
    table_name => 'STS_STAGING_TABLE',
    schema_name => 'SCOTT'
  );
END;
/

-- 2.2 打包 STS 進中轉表
BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
    sqlset_name      => 'PROD_STS_202504',
    staging_table_name => 'STS_STAGING_TABLE',
    staging_schema_owner => 'SCOTT'
  );
END;
/

步驟 3：Data Pump 導出導入中轉表

# 源實例導出
expdp scott/tiger@prod \
  tables=SCOTT.STS_STAGING_TABLE \
  directory=DATA_PUMP_DIR \
  dumpfile=sts_staging.dmp \
  logfile=sts_exp.log

# 目標實例導入
impdp scott/tiger@test \
  tables=SCOTT.STS_STAGING_TABLE \
  directory=DATA_PUMP_DIR \
  dumpfile=sts_staging.dmp \
  logfile=sts_imp.log \
  table_exists_action=REPLACE

步驟 4：目標實例解包還原 STS

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

步驟 5：目標實例執行 SQL Tuning Advisor 分析

DECLARE
  tname VARCHAR2(30) := 'TUNING_TASK_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI');
  tdesc VARCHAR2(100) := 'Tuning PROD_STS_202504 on test instance';
BEGIN
  DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sqlset_name  => 'PROD_STS_202504',
    task_name    => tname,
    description  => tdesc,
    scope        => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit   => 3600
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(tname);
END;
/

-- 查看報告
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK_20250420_143000') FROM DUAL;

三、關鍵參數與注意事項

參數/步驟	關鍵點
`CREATE_STGTAB_SQLSET`	中轉表建立在源實例，建議專用 schema 存放，避免污染業務表
`PACK_STGTAB_SQLSET`	可指定 `sqlset_owner` 打包他人 STS，需 `ADMINISTER SQL TUNING SET` 權限
Data Pump	跨版本遷移需加 `VERSION=12.1` 參數；大 STS 需並行度 `PARALLEL=4`
`UNPACK_STGTAB_SQLSET`	`replace=>TRUE` 覆蓋同名；可用 `sqlset_name` 重命名
權限	源/目標用戶需 `ADMINISTER SQL TUNING SET`、`ADVISOR`、Data Pump 全權限

四、替代方案：Oracle Enterprise Manager (EM) 圖形化操作

EM → Performance → SQL Tuning Sets → 選中 STS → **Export** → 生成 Data Pump 轉儲文件。
目標實例 EM → **Import** → 選中轉儲文件 → 還原 STS。
EM → SQL Tuning Advisor → 選中 STS → 執行分析。

適合不熟悉 PL/SQL 的 DBA，底層調用相同 DBMS_SQLTUNE API。

五、總結

STS 跨實例遷移**五步走**：建立 → 打包 → 導出導入 → 解包 → 分析。核心在於 DBMS_SQLTUNE 的 PACK/UNPACK_STGTAB_SQLSET 配合 Data Pump 實現元數據與數據的完整遷移。掌握此流程，即可在生產環境無侵入採集負載，安全地在測試環境深度調優，實現「生產採集、測試調優、生產實施」的閉環最佳實踐。