Oracle SQL 調優集 (STS) 完整操作手冊：建立、載入 AWR/游標、篩選、傳輸、SPM 基線轉換

Oracle SQL 調優集 (STS) 完整操作手冊：建立、載入 AWR/游標、篩選、傳輸、SPM 基線轉換

SQL Tuning Set (STS) 是 Oracle 10g+ 提供的調優工作載體，可收集高耗資源 SQL、跨環境傳輸、轉換為 SQL Plan Baseline 固定計劃。本文整理從建立 STS、載入 AWR 快照/游標緩存 SQL、篩選刪除、跨庫傳輸（Data Pump/DB Link），到批量轉換 SPM Baseline 的完整 PL/SQL 範本，可直接套用於生產調優專案。

一、權限準備

GRANT ADMINISTER ANY SQL TUNING SET TO scott;

二、建立/刪除 STS

-- 刪除既有
BEGIN DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'OCPYANG_STS'); END;
/
-- 建立新 STS
BEGIN DBMS_SQLTUNE.CREATE_SQLSET(
  sqlset_name => 'OCPYANG_STS',
  sqlset_owner => 'SCOTT',
  description => 'ocpyang test workload');
END;
/
-- 查看所有 STS
SELECT OWNER, NAME, ID, CREATED, STATEMENT_COUNT FROM DBA_SQLSET;

三、從 AWR 載入高耗資源 SQL

1. 查看可用快照範圍

SELECT SNAP_ID, INSTANCE_NUMBER, END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID;

2. 篩選磁碟讀取前 15 的 SQL (快照 820-841)

SELECT SQL_ID, SUBSTR(SQL_TEXT,1,100), DISK_READS, CPU_TIME, ELAPSED_TIME
FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
  820, 841, NULL, NULL, 'DISK_READS', NULL, NULL, NULL, 15))
ORDER BY DISK_READS DESC;

3. 載入 STS

DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
      820, 841, NULL, NULL, 'DISK_READS', NULL, NULL, NULL, 15)) p;
  DBMS_SQLTUNE.LOAD_SQLSET(
    sqlset_owner => 'SCOTT',
    sqlset_name  => 'OCPYANG_STS',
    populate_cursor => cur);
END;
/
-- 權限不足報 ORA-13774 請用 SYS 執行 LOAD_SQLSET

四、從游標緩存載入即時高耗 SQL

DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
      'PARSING_SCHEMA_NAME <> ''SYS'' AND DISK_READS > 1000000',
      NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) p;
  DBMS_SQLTUNE.LOAD_SQLSET(
    sqlset_owner => 'SCOTT',
    sqlset_name  => 'OCPYANG_STS',
    populate_cursor => cur);
END;
/
-- 捕獲一段時間內所有游標 (3600 秒，每 20 秒採樣)
BEGIN
  DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
    sqlset_owner => 'SCOTT',
    sqlset_name  => 'PROD_WORKLOAD',
    time_limit   => 3600,
    repeat_interval => 20);
END;
/

五、查看 STS 內容

SELECT SQLSET_NAME, ELAPSED_TIME, CPU_TIME, BUFFER_GETS, DISK_READS, SQL_TEXT
FROM DBA_SQLSET_STATEMENTS
WHERE SQLSET_NAME = 'OCPYANG_STS';

六、篩選刪除低價值 SQL

BEGIN
  DBMS_SQLTUNE.DELETE_SQLSET(
    sqlset_owner => 'SCOTT',
    sqlset_name  => 'IO_STS',
    basic_filter => 'DISK_READS < 2000000');
END;
/

七、跨庫傳輸 STS (Data Pump / DB Link)

1. 來源庫：建立暫存表

   BEGIN
     DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
       sqlset_name => 'OCPYANG_STS',
       sqlset_owner => 'SCOTT',
       staging_table_name => 'BASELINE_STG01',
       staging_schema_owner => 'SCOTT',
       db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION);
   END;
   /
   

2. expdp 匯出 BASELINE_STG01 表，impdp 導入目標庫。
3. 目標庫：解包至新 STS

   BEGIN
     DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
       sqlset_name => 'OCPYANG_STS01',
       replace => TRUE,
       staging_table_name => 'BASELINE_STG01');
   END;
   /
   

八、批量轉換為 SQL Plan Baseline (SPM)

DECLARE
  ret NUMBER;
BEGIN
  ret := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'OCPYANG_STS01',
    sqlset_owner => 'SCOTT');
END;
/

九、常用 SELECT_CURSOR_CACHE 篩選範例

-- 非 SYS、CPU 時間最長前 10
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
  BASIC_FILTER => 'PARSING_SCHEMA_NAME <> ''SYS''',
  RANKING_MEASURE1 => 'CPU_TIME', RESULT_LIMIT => 10));

-- 執行超過 5 秒
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
  'ELAPSED_TIME > 5000000'));

-- 模組/動作標記
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
  'MODULE = ''MY_APP'' AND ACTION = ''MY_ACTION'''));

十、最佳實務檢核

• [ ] 專案開始前建立專屬 STS，命名規範：PROJ__STS。
• [ ] 載入 AWR 歷史 + 捕獲當前游標，覆蓋完整工作負載。
• [ ] 定期 DELETE_SQLSET 移除噪音 SQL，保持 STS 精簡。
• [ ] 跨環境傳輸使用 PACK/UNPACK_STGTAB_SQLSET + Data Pump，避免手工重建。
• [ ] 調優驗證通過後，LOAD_PLANS_FROM_SQLSET 固定優良計劃為 Baseline。

掌握 STS 全生命週期操作，讓 SQL 調優從「單條語句試錯」進化為「工作負載級系統性優化」。