— 查看计划基线状态
SELECT sql_handle, plan_name, enabled, accepted, fixed
FROM dba_sql_plan_baselines;– 查看计划基线性能
SELECT * FROM dba_sql_plan_baseline_history;– 禁用计划基线
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => ‘sql_handle_value’,
plan_name => ‘plan_name_value’,
attribute_name => ‘ENABLED’,
attribute_value => ‘NO’
);– 删除计划基线
EXEC DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => ‘sql_handle_value’
);

— 创建测试表
CREATE TABLE fgsales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
amount NUMBER,
region VARCHAR2(50)
);– 插入测试数据
INSERT INTO fgsales VALUES (1, SYSDATE – 30, 1000, ‘North’);INSERT INTO fgsales VALUES (2, SYSDATE – 20, 2000, ‘South’);INSERT INTO fgsales VALUES (3, SYSDATE – 10, 1500, ‘East’);INSERT INTO fgsales VALUES (4, SYSDATE, 3000, ‘West’);COMMIT;– 执行SQL语句，捕获计划
SELECT /*+ MONITOR */ region, SUM(amount) AS total_sales
FROM fgsales
GROUP BY region;– 查看SQL ID
SELECT sql_id, sql_text FROM v$sql
WHERE sql_text LIKE ‘%region, SUM(amount)%’ AND rownum = 1;– 创建计划基线
DECLARE
l_plans_loaded PLS_INTEGER;BEGIN
l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => ‘sql_id_value’,
enabled => ‘YES’
);DBMS_OUTPUT.PUT_LINE(‘Plans loaded: ‘ || l_plans_loaded);END;/

SQL> SELECT /*+ MONITOR */ region, SUM(amount) AS total_sales
2 FROM fgsales
3 GROUP BY region;REGION TOTAL_SALES
——- ———–
North 1000
South 2000
East 1500
West 3000

SQL> SELECT sql_id, sql_text FROM v$sql
2 WHERE sql_text LIKE ‘%region, SUM(amount)%’ AND rownum = 1;SQL_ID SQL_TEXT
————- ————————————————–
abc123 SELECT /*+ MONITOR */ region, SUM(amount) AS total_sales FROM fgsales GROUP BY region

SQL> DECLARE
2 l_plans_loaded PLS_INTEGER;3 BEGIN
4 l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
5 sql_id => ‘abc123’,
6 enabled => ‘YES’
7 );8 DBMS_OUTPUT.PUT_LINE(‘Plans loaded: ‘ || l_plans_loaded);9 END;10 /Plans loaded: 1

— 固定最佳计划
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => ‘sql_handle_value’,
plan_name => ‘plan_name_value’,
attribute_name => ‘FIXED’,
attribute_value => ‘YES’
);– 演进计划基线
DECLARE
l_evolved_plans PLS_INTEGER;BEGIN
l_evolved_plans := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => ‘sql_handle_value’
);DBMS_OUTPUT.PUT_LINE(‘Evolved plans: ‘ || l_evolved_plans);END;/– 查看计划执行情况
SELECT * FROM table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
sql_handle => ‘sql_handle_value’
));

— 检查SQL计划管理是否启用
SELECT * FROM v$parameter WHERE name = ‘optimizer_capture_sql_plan_baselines’;– 查看计划基线状态
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM dba_sql_plan_baselines;– 检查计划选择情况
SELECT * FROM v$sql_plan
WHERE sql_id = ‘sql_id_value’;– 重新捕获计划
DECLARE
l_plans_loaded PLS_INTEGER;BEGIN
l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => ‘sql_id_value’,
enabled => ‘YES’
);END;/