【Oracle Database 12c新特性】SYS_AUTO_SPM_EVOLVE_TASK 自动作业

﹏ヽ暗。殇╰゛Y 2022-10-02 09:48 134阅读 0赞

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                 UNLIMITED
    END_SNAPSHOT                   UNUSED
    END_TIME                       UNUSED
    INSTANCE                       UNUSED
    JOURNALING                     INFORMATION
    MODE                           COMPREHENSIVE
    START_SNAPSHOT                 UNUSED
    START_TIME                     UNUSED
    TARGET_OBJECTS                 1
    TIME_LIMIT                     3600
    DEFAULT_EXECUTION_TYPE         SPM EVOLVE
    CON_DBID_MAPPING               UNUSED
    ORA_EM_PARAM1                  UNUSED
    ORA_EM_PARAM2                  UNUSED
    ORA_EM_PARAM3                  UNUSED
    ORA_EM_PARAM4                  UNUSED
    ORA_EM_PARAM5                  UNUSED
    ORA_EM_PARAM6                  UNUSED
    ORA_EM_PARAM7                  UNUSED
    ORA_EM_PARAM8                  UNUSED
    ORA_EM_PARAM9                  UNUSED
    ORA_EM_PARAM10                 UNUSED
    EXECUTION_DAYS_TO_EXPIRE       30
    SQLSET_NAME                    UNUSED
    SQLSET_OWNER                   UNUSED
    ACCEPT_PLANS                   TRUE
    _SPM_VERIFY                    TRUE
    APPLY_CAPTURED_COMPILENV       UNUSED
    LOCAL_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) pl
    ORDER 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

转载于:https://blog.51cto.com/maclean/1278588