Oracle Database 12c中引入了一个新的自动系统作业，即SYS_AUTO_SPM_EVOLVE_TASK。 该作业将在每天的自动维护作业窗口中自动执行。 SYS_AUTO_SPM_EVOLVE_TASK负责检索和排序SPM中未被接受的执行计划non-accepted plan以便verification。 当此执行计划经过verified，过该计划满足性能阀值则将被自动接受accepted 。因此，当优化器将一个non-accepted的执行计划加入到SQL statement plan history中，在很多情况下若该计划确实是更好的，则会在第二天被接受并可以使用。 注意该自动task存在时间上的限制为一个小时(默认TIME_LIMIT=3600s)，因此可能造成部分计划未被verified。 在此种场景下，下一个维护窗口该task执行时将处理剩余的执行计划。
SYS_AUTO_SPM_EVOLVE_TASK 1 SELECT parameter_name, parameter_value 2 FROM dba_advisor_parameters 3* WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'SQL> /PARAMETER_NAME PARAMETER_VALUE------------------------------ ----------------------------------------DAYS_TO_EXPIRE UNLIMITEDEND_SNAPSHOT UNUSEDEND_TIME UNUSEDINSTANCE UNUSEDJOURNALING INFORMATIONMODE COMPREHENSIVESTART_SNAPSHOT UNUSEDSTART_TIME UNUSEDTARGET_OBJECTS 1TIME_LIMIT 3600DEFAULT_EXECUTION_TYPE SPM EVOLVECON_DBID_MAPPING UNUSEDORA_EM_PARAM1 UNUSEDORA_EM_PARAM2 UNUSEDORA_EM_PARAM3 UNUSEDORA_EM_PARAM4 UNUSEDORA_EM_PARAM5 UNUSEDORA_EM_PARAM6 UNUSEDORA_EM_PARAM7 UNUSEDORA_EM_PARAM8 UNUSEDORA_EM_PARAM9 UNUSEDORA_EM_PARAM10 UNUSEDEXECUTION_DAYS_TO_EXPIRE 30SQLSET_NAME UNUSEDSQLSET_OWNER UNUSEDACCEPT_PLANS TRUE_SPM_VERIFY TRUEAPPLY_CAPTURED_COMPILENV UNUSEDLOCAL_TIME_LIMIT UNUSED已选择 29 行。 select execution_name,status,execution_start,execution_end from dba_advisor_executions where task_name='SYS_AUTO_SPM_EVOLVE_TASK'; -- time_limit (IN) - Time limit in number of minutes. The time limit -- is global and it is used in the following manner. -- The time limit for first non-accepted plan is equal -- to the input value. The time limit for the second -- non-accepted plan is equal to (input value - time -- spent in first plan verification) and so on. The -- default DBMS_SPM.AUTO_LIMIT means let the system -- choose an appropriate time limit based on the -- number of plan verifications required to be done. -- The value DBMS_SPM.NO_LIMIT means no time limit.DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2 (30) := :job_name; job_subname VARCHAR2 (30) := :job_subname; job_owner VARCHAR2 (30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2 (14) := :chainid; credential_owner VARCHAR2 (30) := :credown; credential_name VARCHAR2 (30) := :crednam; destination_owner VARCHAR2 (30) := :destown; destination_name VARCHAR2 (30) := :destnam; job_dest_id VARCHAR2 (14) := :jdestid; log_id NUMBER := :log_id;BEGIN DECLARE ename VARCHAR2 (30); BEGIN ename := DBMS_SQLTUNE.execute_tuning_task ('SYS_AUTO_SQL_TUNING_TASK'); ename := DBMS_SPM.execute_evolve_task ('SYS_AUTO_SPM_EVOLVE_TASK'); END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF;END;/* Formatted on 2013/8/4 10:48:19 (QP5 v5.163.1008.3004) */ SELECT pl.signature, pl.category, pl.name, pl.plan_id, DECODE (BITAND (pl.flags, :1), 0, :2, :3) flags, pl.sql_handle, pl.sql_text, pl.comp_data, pl.optimizer_env, pl.bind_data, pl.parsing_schema_name, pl.creator, (CASE /* plan is already accepted */ WHEN (BITAND (pl.flags, :4) <> 0) THEN :5 /* plan has recently been verified */ WHEN (pl.is_auto IS NOT NULL AND pl.last_verified > SYSTIMESTAMP - :6) THEN :7 /* plan's SQL statement hasn't been recently executed */ WHEN (pl.is_auto IS NOT NULL AND pl.last_verified IS NOT NULL AND pl.sql_last_executed < SYSTIMESTAMP - :8) THEN :9 ELSE :10 END) pruned FROM (SELECT so.signature, so.category, so.name, so.plan_id, so.flags, st.sql_handle, st.sql_text, (DECODE ( BITAND (so.flags, 128), 128, (SELECT EXTRACT (XMLTYPE (pl.other_xml), '/*/outline_data').getClobVal () FROM sys.sqlobj$plan pl WHERE pl.signature = so.signature AND pl.category = so.category AND pl.obj_type = so.obj_type AND pl.plan_id = so.plan_id AND pl.other_xml IS NOT NULL), (SELECT sod.comp_data FROM sys.sqlobj$data sod WHERE sod.signature = so.signature AND sod.category = so.category AND sod.obj_type = so.obj_type AND sod.plan_id = so.plan_id))) comp_data, sox.optimizer_env, sox.bind_data, sox.parsing_schema_name, sox.creator, sox.last_verified, sox.optimizer_cost, sox.created, :11 is_auto, (SELECT MAX (last_executed) FROM sys.sqlobj$ ob WHERE ob.signature = so.signature AND ob.obj_type = so.obj_type AND ob.category = so.category) sql_last_executed FROM sys.sqlobj$ so, sys.sqlobj$auxdata sox, sys.sql$text st WHERE (:12 IS NULL OR so.name IN (SELECT EXTRACTVALUE (VALUE (p), '/plan') pname FROM TABLE ( XMLSEQUENCE ( EXTRACT (XMLTYPE (:13), '/plan_list/*'))) p)) AND (:14 IS NOT NULL OR (BITAND (so.flags, :15) <> 0 AND BITAND (so.flags, :16) = 0)) AND so.obj_type = :17 AND so.signature = sox.signature AND so.category = sox.category AND so.obj_type = sox.obj_type AND so.plan_id = sox.plan_id AND so.signature = st.signature) plORDER BY DECODE (:18, 0, NULL, pl.name), pl.last_verified NULLS FIRST, pl.sql_last_executed DESC NULLS LAST, pl.optimizer_cost, pl.created, pl.name