21-Oracle 23 ai-Automatic SQL Plan Management（SPM)

小伙伴們，有沒有遷移數據庫完畢后或是突然某一天在同一個實例上同樣的SQL，

性能不一樣了、業務反饋卡頓、業務超時等各種匪夷所思的現狀。

于是SPM定位開始，OCM考試中SPM必考。

其他的AWR、ASH、SQLHC、SQLT、SQL profile等換作下一個話題，下次填坑。

Oracle SQL Plan Management（SPM）是一種通過控制執行計劃穩定性來優化SQL性能的內置機制，其核心原理是通過基線（Baseline）機制管理執行計劃的演進，避免因計劃突變導致的性能下降。

一、使用場景

當SQL語句的執行計劃因統計信息更新、數據庫升級、參數調整或索引變更等因素發生變化時，

可能導致性能嚴重下降（比如沒有用索引，使用了全表掃描替代索引掃描）。

關鍵業務SQL保護?：為核心交易SQL綁定已驗證的高效執行計劃。

?灰度驗證新計劃?：通過演化機制（Evolution）測試新計劃性能，僅當優于基線時才啟用。

?遷移與升級保障?：在版本升級或硬件變更時維持執行計劃一致性。

SPM通過下面步驟定位和解決：

基線（Baseline）機制：記錄已知性能良好的執行計劃，新生成的計劃需驗證性能后才被采納。
演進控制：新計劃必須證明優于或等于基線計劃，否則仍使用原計劃。

二、關鍵組件?

?Plan History?：存儲SQL所有曾使用的執行計劃（包括未驗證的）。
?Plan Baseline?：Plan History的子集，僅包含已驗證（ACCEPTED）且穩定的高效計劃。
?SQL Management Base ：存儲SPM元數據的字典表（位于SYSAUX表空間）。

三、原理解析：工作流程?

?1. 計劃捕獲（Plan Capture）??

?自動捕獲? （需設置參數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE）：

首次執行的SQL生成計劃后，該計劃作為初始基線（標記為ENABLED和ACCEPTED）。
后續新計劃進入Plan History，但狀態為ENABLED, NOT ACCEPTED，需經性能驗證才可加入基線。

?手動捕獲?：
- 通過DBMS_SPM包從共享池、SQL調優集（STS）或存儲大綱導入計劃。

?2. 計劃選擇（Plan Selection）??

?優化器決策流程?（需啟用OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE）：

2.1 正常解析SQL，生成新執行計劃（成本最低）。

2.2 檢查是否存在匹配的SQL Plan Baseline：

- 若存在ACCEPTED計劃 → 直接使用該計劃。
- 若新計劃不在Baseline中 → 將其加入Plan History（狀態為NOT ACCEPTED）。

2.3實時回退機制（Oracle 23ai新特性）? ?：

- 若新計劃性能劣化（如邏輯讀激增），自動回退至Baseline中的最優計劃。

?3. 計劃演進（Plan Evolution）??

?自動演進?：
- 任務SYS_AUTO_SPM_EVOLVE_TASK定期檢查未ACCEPTED的計劃，通過性能對比（如CPU時間、I/O消耗）決定是否采納。
- 參數ACCEPT_PLANS控制是否自動接受更優計劃（默認TRUE）。
?手動演進?：
- 使用 DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE()測試并接受新計劃。

四、?Oracle 23 ai 的SPM特性增強：

Real-Time SQL Plan Management (實時SPM)

變化：新增實時檢測和修復性能退化的執行計劃，無需等待自動任務或手動干預。從捕獲、驗證到演進全程自動化，減少人工維護成本。
原理：持續監控SQL執行性能，若新計劃比基線計劃慢，自動回退到基線計劃并標記新計劃為"UNACCEPTED"。深度集成SQL Monitor?，秒級檢測執行計劃性能退化，自動切換至歷史最優計劃，無需DBA干預。
優勢：減少因計劃突變導致的性能風險，尤其適合關鍵業務SQL。

與AI Vector Search集成

變化：SPM可管理含AI向量搜索的SQL執行計劃（如"VECTOR_DISTANCE()"函數）。
原理：優化器為向量搜索SQL生成計劃時，SPM基線會記錄并驗證其效率。
示例場景：相似性搜索（如"WHERE VECTOR_DISTANCE(embedding, :vec) < 1"）的計劃穩定性增強。

Raft復制支持下的分布式SPM

變化：在Globally Distributed Database中，SPM基線支持跨分片同步。
原理：通過Raft共識協議復制基線計劃，確保分布式環境下計劃一致性。

增強與In-Memory的協同

變化：SPM優先選擇利用In-Memory列存儲（如內存連接組）的高效計劃。
原理：當"INMEMORY_AUTOMATIC_LEVEL=HIGH"時，SPM自動采納內存優化計劃。

五、配置和實操

?1. 啟用實時SPM?

-- 啟用自動捕獲SQL計劃基線
SYS@FREE> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
System altered
-- 啟用實時SPM（默認開啟，驗證狀態）
SYS@FREE> SELECT value FROM v$parameter WHERE name = 'optimizer_use_sql_plan_baselines';
VALUE
------------------------------------------------------------------------------------------------------------------------
TRUE
-- 返回值應為 TRUE

2. 驗證實時SPM回退機制?

-- 步驟1: 創建測試表
CREATE TABLE spm_test (id NUMBER, data VARCHAR2(100));
INSERT INTO spm_test SELECT rownum, 'Data'||rownum FROM dual CONNECT BY LEVEL <= 10000;
COMMIT;-- 步驟2: 首次執行（生成初始計劃）
SYS@CDB$ROOT> SELECT /*+ REAL_TIME_SPM_TEST */ * FROM spm_test WHERE id = 500;ID DATA
______ __________500 Data500-- 步驟3: 可以嘗試刪除索引
DROP INDEX IF EXISTS spm_test_idx;-- 先創建索引再刪除
CREATE INDEX spm_test_idx ON spm_test(id); -- 先創建索引再刪除，模擬計劃突變-- 步驟4: 再次執行相同SQL
SELECT /*+ REAL_TIME_SPM_TEST */ * FROM spm_test WHERE id = 500; 
-- 觀察執行計劃是否回退到全表掃描（原最優計劃為索引掃描）

3. 監控SPM狀態??

--- 查看已捕獲的SQL計劃基線，查詢 SPM 捕獲結果
SELECT sql_handle, plan_name, enabled, accepted,optimizer_cost,origin AS capture_source  -- 顯示來源為 REAL-TIME
FROM dba_sql_plan_baselines 
WHERE sql_text LIKE '%REAL_TIME_SPM_TEST%';SQL_HANDLE              PLAN_NAME                         ENABLED    ACCEPTED       OPTIMIZER_COST CAPTURE_SOURCE
_______________________ _________________________________ __________ ___________ _________________ _________________
SQL_64b41bf95ca6b6c6    SQL_PLAN_69d0vz5faddq691cb0adf    YES        NO                          2 AUTO-CAPTURE
SQL_64b41bf95ca6b6c6    SQL_PLAN_69d0vz5faddq696d17023    YES        YES                         1 AUTO-CAPTURE
SQL_64b41bf95ca6b6c6    SQL_PLAN_69d0vz5faddq6f2fc655a    YES        NO                          9 AUTO-CAPTURE
SQL_b1986790bdca8230    SQL_PLAN_b3637k2ywp0jh6ded1a00    YES        YES                         2 AUTO-CAPTURE
SQL_6cf7d7301796c616    SQL_PLAN_6txyr60btdjhq6ded1a00    YES        YES                         2 AUTO-CAPTURE
SQL_9b8aec55051bcab5    SQL_PLAN_9r2rcan2jrkpp6ded1a00    YES        YES                         2 AUTO-CAPTURE
SQL_9cd99fe508c1b86c    SQL_PLAN_9tqczwn4c3f3cb73cade2    YES        YES                         0 AUTO-CAPTURE
--
-- 檢查實時回退事件
SELECT sql_id, PLAN_HASH_VALUE
FROM v$sql
WHERE sql_text LIKE '%REAL_TIME_SPM_TEST%';
--
SQL_ID              PLAN_HASH_VALUE
________________ __________________
aq357chxcs0kd             903671040
6xphsvkrns1q1            2664986145
g86t44cwf41r8            2664986145
g03qt7845c4pv             903671040
ajhtavdx2s5t9            2664986145
6kma5qad96t0n            2664986145
6kma5qad96t0n            2664986145
111gdsdj2ft3g            1155944573
6110vngy8zkm4             9036710409 rows selected.-- 方案1：SQL Monitor報告（需SQL_ID）
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(sql_id => '161f318vx0y63') FROM DUAL;
-- 報告中的Note部分會標注SPM回退事件
SYS@CDB$ROOT> SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR FROM DUAL;
REPORT_SQL_MONITOR
_______________________________________________________________________________________________________________________________
SQL Monitoring Report
SQL Text
------------------------------
begin dbms_swrf_internal.awr_imp(dmpfile=> :mpk_name, dmpdir=>:mbloc, new_dbid=>:dbid, mbtype=>:mbtype, mbcred=>:cred); end;
Global Information
------------------------------Status              :  DONEInstance ID         :  1Session             :  SYS (205:3673)SQL ID              :  161f318vx0y63SQL Execution ID    :  16777217Execution Started   :  06/08/2025 19:58:55First Refresh Time  :  06/08/2025 19:59:00Last Refresh Time   :  06/08/2025 19:59:01Duration            :  6sModule/Action       :  MMON_SLAVE/AWR Warehouse Auto-ImportService             :  SYS$BACKGROUNDProgram             :  oracle@OL96 (M003)Global Stats
===============================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
===============================================================================================================
|    6.65 |    5.70 |     0.07 |        0.00 |        0.75 |     0.12 |   108K | 1468 |  19MB |     1 |  8192 |
===============================================================================================================-- 方案2：檢查計劃基線狀態
SELECT sql_handle, plan_name, enabled, accepted, origin 
FROM DBA_SQL_PLAN_BASELINES 
WHERE sql_text LIKE '%REAL_TIME_SPM_TEST%'
AND origin = 'AUTO-CAPTURE';
-- 若accepted=YES且origin為自動捕獲，說明回退成功
----- 檢查演進任務報告
SYS@CDB$ROOT> SELECT DBMS_SPM.report_auto_evolve_task FROM dual;REPORT_AUTO_EVOLVE_TASK
________________________________________________________________________________________________
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------Task Information:---------------------------------------------Task Name            : SYS_AUTO_SPM_EVOLVE_TASKTask Owner           : SYSDescription          : Automatic SPM Evolve TaskExecution Name       : EXEC_280Execution Type       : SPM EVOLVEScope                : COMPREHENSIVEStatus               : COMPLETEDStarted              : 06/08/2025 14:00:13Finished             : 06/08/2025 14:00:14Last Updated         : 06/08/2025 14:00:14Global Time Limit    : 3600Per-Plan Time Limit  : UNUSEDNumber of Errors     : 0
---------------------------------------------------------------------------------------------SUMMARY SECTION
---------------------------------------------------------------------------------------------Number of plans processed  : 0Number of findings         : 0Number of recommendations  : 0Number of errors           : 0
---------------------------------------------------------------------------------------------
SYS@CDB$ROOT>

六、高級管理腳本?

?1. 手動固定最優計劃?

--- 查找SQL的SQL_HANDLE
DECLAREl_plans PLS_INTEGER;
BEGINl_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'XXXXXXX' -- 替換為實際SQL_ID);
END;
/

?2. 主動演化計劃基線

--- 測試并采納新計劃
SYS@CDB$ROOT> SET SERVEROUTPUT ON
SYS@CDB$ROOT> DECLARE2    r_report CLOB;3  BEGIN4    r_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(5      sql_handle => 'SQL_9cd99fe508c1b86c' -- 替換為實際SQL_HANDLE6    );7    DBMS_OUTPUT.PUT_LINE(r_report);8  END;9* /
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------Task Information:---------------------------------------------Task Name            : TASK_362Task Owner           : SYSExecution Name       : EXEC_322Execution Type       : SPM EVOLVEScope                : COMPREHENSIVEStatus               : COMPLETEDStarted              : 06/09/2025 14:58:35Finished             : 06/09/2025 14:58:35Last Updated         : 06/09/2025 14:58:35Global Time Limit    : 2147483646Per-Plan Time Limit  : UNUSEDNumber of Errors     : 0
---------------------------------------------------------------------------------------------SUMMARY SECTION
---------------------------------------------------------------------------------------------Number of plans processed  : 0Number of findings         : 0Number of recommendations  : 0Number of errors           : 0
---------------------------------------------------------------------------------------------PL/SQL procedure successfully completed.SYS@CDB$ROOT>