演进未接受的 SQL plan baseline 计划

命令

-- 1) Plans waiting for acceptance:
SELECT sql_handle, plan_name, origin, enabled, accepted, fixed,
       TO_CHAR(created,'YYYY-MM-DD HH24:MI') AS created
FROM   dba_sql_plan_baselines
WHERE  accepted = 'NO'
ORDER  BY created DESC;

-- 2) Evolve task: test-executes the candidate plan against the accepted plan
VAR tname VARCHAR2(128)
VAR ename VARCHAR2(128)
BEGIN
  :tname := DBMS_SPM.CREATE_EVOLVE_TASK(
              sql_handle => '<sql_handle>',
              time_limit => 600,
              task_name  => 'EVOLVE_<sql_handle>');
  :ename := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => :tname);
END;
/

SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 200 PAGESIZE 0
SELECT DBMS_SPM.REPORT_EVOLVE_TASK(
         task_name => :tname, execution_name => :ename,
         type => 'TEXT', level => 'TYPICAL', section => 'ALL')
FROM   dual;

-- 3) If the report has recommendations (plans proven better), accept them:
SET SERVEROUTPUT ON
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name => :tname);
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) accepted');
END;
/
EXEC DBMS_SPM.DROP_EVOLVE_TASK(task_name => :tname);

作用

存在 baseline 时，优化器发现的新计划以 ACCEPTED=NO 存储。evolve 任务会将这些候选计划与已接受计划进行测试执行对比，只接受明显更优的计划。

适用场景

对于已有 baseline 的 SQL，当新索引或新统计信息可能产生了更好的计划时；无需等待自动的 SYS_AUTO_SPM_EVOLVE_TASK（12c+）而手动决定时。

注意

需要 Enterprise Edition。evolve 会实际执行 SQL（DML 只执行查询部分）；对重型 SQL 请设置 time_limit 并避开高峰时段。需要 ADMINISTER SQL MANAGEMENT OBJECT 和 ADVISOR。以 SYS 解析的 SQL 无法演进（ORA-16963）；请以应用用户身份运行。