eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« ACOUG 2012广州站和中山大学深圳职院校园行 | Blog首页 | AWR报告分析之一:高 DB CPU 消耗的性能根源 »

EMD_MAINTENANCE 定时任务引起的资源消耗

在很多数据库中,尤其是Oracle 10g的版本,可能会经常看到一个显著的定时任务排在前列:EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS。

这个任务通常每分钟执行一次,其任务定义如下:

DECLARE

job BINARY_INTEGER := :job;

next_date DATE := :mydate;

broken BOOLEAN := FALSE;

BEGIN

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();

:mydate := next_date;

IF broken THEN :b := 1;

ELSE :b := 0;

END IF;

END;

这个任务和EM的定时操作相关。如果不使用EM,可以停用这个任务,可以消弭这个影响,常规的操作可能类似如下步骤

SQL> conn sysman
Enter password:
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter user sysman account unlock;

User altered.

SQL> conn / as sysdba
Connected.
SQL> alter user sysman identified by newpassword;

User altered.

SQL> conn sysman
Enter password:
Connected.
SQL> exec emd_maintenance.remove_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

如果需要重建这个任务,需要按照以下步骤操作:

1) Login to repository as sysman user.

2) Perform the following steps:

a) alter system set job_queue_processes=0;
b) select * from dba_jobs_running;
The above step should be executed until the output of the query returns "no rows selected".
c) Run the following SQL scripts:
\sysman\admin\emdrep\sql\core\latest\admin\admin_remove_dbms_jobs.sql;
\sysman\admin\emdrep\sql\core\latest\admin\admin_submit_dbms_jobs.sql;
d) exec emd_maintenance.recompile_invalid_objects;
e) alter system set job_queue_processes=10;
f) select job,what from dba_jobs;
With the above query, check whether the job
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS
is created successfully or not.
g) exec dbms_job.run();
where the jobid is returned from previous query output.

3) The job EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS will be successfully created

浏览其中的脚本,可以帮助我们进一步了解这个调度任务的内部操作,以判断停用会否影响我们期望的工作:

PROCEDURE execute_em_dbms_job_procs
  IS
      v_proc_start_date DATE DEFAULT SYSDATE;
      l_start_timestmp  TIMESTAMP := SYSTIMESTAMP;
  BEGIN

    -- Collections proc
    BEGIN
      EMD_COLLECTION.RUN_COLLECTIONS();
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
   
 
 
  -- PURPOSE
--   To determine and run all scheduled collections
PROCEDURE RUN_COLLECTIONS
IS
  iSQL           VARCHAR2(4000);
  metric_result  MGMT_METRIC_RESULTS;
  key_val        VARCHAR2(64) := ' ';
  tkey_val       VARCHAR2(64) := ' ';
  mguid          RAW(16);
  num_values     NUMBER := 0;
  tname          MGMT_TARGETS.target_name%TYPE;
  ttype          MGMT_TARGETS.target_type%TYPE;
  last_sev_code  NUMBER := 0;
  new_sev_code   NUMBER := 0;
  mtype          NUMBER := 0;
  warn_op        NUMBER;
  warn_threshold VARCHAR2(256);
  crit_op        NUMBER;
  crit_threshold VARCHAR2(256);
  m_label        VARCHAR2(64);
  c_label        VARCHAR2(64);
  start_time     DATE;
  end_time       DATE;
  last_time      DATE;
  num_oc         NUMBER := 1;
  num_WAs        NUMBER := 0;
  num_CAs        NUMBER := 0;
  message_template VARCHAR2(4000);
  message        VARCHAR2(4000);
  nls_params     VARCHAR2(4000);
  message_nlsid  VARCHAR2(64);
  perf_start     DATE;
  duration       NUMBER;
  cnt            NUMBER := 0;
  CURSOR collections IS
    SELECT c.target_guid, c.metric_guid, c.store_metric, c.schedule,
      c.coll_name, m.metric_name, m.eval_func
    FROM MGMT_METRIC_COLLECTIONS_REP r, MGMT_METRIC_COLLECTIONS c, MGMT_METRICS m
    WHERE c.suspended = 0 AND
          c.is_repository = 1 AND
          (c.last_collected_timestamp IS NULL
            OR  c.last_collected_timestamp + c.schedule / 1440 < SYSDATE) AND
          c.metric_guid = m.metric_guid AND
          r.target_guid = c.target_guid AND
          r.metric_guid = c.metric_guid AND
          r.coll_name = c.coll_name;

BEGIN

  perf_start := SYSDATE;
  cnt := 0;

  FOR collection IN collections
  LOOP

  BEGIN

    IF collection.eval_func IS NULL OR LENGTH(collection.eval_func) = 0
    THEN
      -- Go onto the next collection
      GOTO next_collection;
    END IF;

    cnt := cnt + 1;

    -- Set up the SQL statement to call the evaluation procedure
    iSQL := 'CALL ' || collection.eval_func || '(:tguid, :mguid, :result)';

    dbms_output.put_line('RUN_COLLECTIONS: Calling ' || collection.eval_func );

    start_time := SYSDATE;

    -- Call the evaluation procedure
    EXECUTE IMMEDIATE iSQL USING IN collection.target_guid,
                                 IN collection.metric_guid,
                                 OUT metric_result;

    end_time := SYSDATE;

    -- If the time for the evaluation procedure to run is longer than its
    -- schedule, then set the last_collected_timestamp so that the
    -- evaluation procedure is called immediately again
    IF (end_time - start_time) > (collection.schedule / 1440)
    THEN
      last_time := start_time + (collection.schedule / 1440);
    ELSE
      last_time := start_time;
    END IF;

    IF metric_result IS NULL
    THEN
      num_values := 0;
    ELSE
      num_values := metric_result.COUNT;
    END IF;
    UPDATE MGMT_METRIC_COLLECTIONS SET status_message = collection.eval_func ||
                                       ' returned ' || num_values || ' values',
                                       last_collected_timestamp = last_time
    WHERE target_guid = collection.target_guid AND
          metric_guid = collection.metric_guid AND
          coll_name = collection.coll_name;

    IF metric_result IS NULL OR num_values = 0
    THEN
      -- Go onto the next collection
      GOTO next_collection;
    END IF;

    -- Get the target name and type
    SELECT target_name, target_type INTO tname, ttype
    FROM MGMT_TARGETS WHERE target_guid = collection.target_guid;

    -- Go through the results and check the thresholds
    FOR i IN metric_result.FIRST..metric_result.LAST
    LOOP

      last_sev_code := 0;
      new_sev_code := 0;
      num_oc := 1;
      num_WAs := 0;
      num_CAs := 0;

      -- This is set to the key value that was used to locate the threshold
      tkey_val := ' ';

      dbms_output.put_line('RUN_COLLECTIONS: Get Metric GUID for ' ||
                           tname || '/' || ttype || '/' ||
                           collection.metric_name || '/' ||
                           metric_result(i).metric_column || '/' ||
                           metric_result(i).key_column_value);

      -- Get the metric column's type and GUID
      SELECT decode(metric_type, 6, 0, 7, 1, metric_type), metric_guid,
             metric_label, column_label
      INTO mtype, mguid, m_label, c_label FROM MGMT_METRICS
      WHERE target_type = ttype AND
            metric_name = collection.metric_name AND
            metric_column = metric_result(i).metric_column;

      -- Check whether a key value is in use and whether a threshold
      -- has been defined for that particular key value
      IF metric_result(i).key_column_value IS NOT NULL
      THEN

        -- key_val is used when calling EMD_SCHEMA to insert a severity
        -- record
        key_val := metric_result(i).key_column_value;

        BEGIN
          SELECT t.warning_operator, t.warning_threshold,
                 t.critical_operator, t.critical_threshold,
                 t.num_occurences, t.num_warnings, t.num_criticals,
                 t.message, t.message_nlsid
          INTO warn_op, warn_threshold, crit_op, crit_threshold,
               num_oc, num_WAs, num_CAs, message_template, message_nlsid
          FROM MGMT_METRIC_THRESHOLDS t
          WHERE t.target_guid = collection.target_guid AND
                t.metric_guid = mguid AND
                t.coll_name = collection.coll_name AND
                t.key_value = metric_result(i).key_column_value ;

          tkey_val :=  metric_result(i).key_column_value;

        EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
          BEGIN
            -- Check if there are thresholds defined for this metric column
            SELECT t.warning_operator, t.warning_threshold,
                 t.critical_operator, t.critical_threshold,
                 t.num_occurences, t.num_warnings, t.num_criticals,
                 t.message, t.message_nlsid
            INTO warn_op, warn_threshold, crit_op, crit_threshold,
                 num_oc, num_WAs, num_CAs, message_template, message_nlsid
            FROM MGMT_METRIC_THRESHOLDS t
            WHERE
                 t.target_guid = collection.target_guid AND
                 t.metric_guid = mguid AND
                 t.coll_name = collection.coll_name AND
                 t.key_value = ' ';
          EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
              -- Go onto next metric
              dbms_output.put_line('RUN_COLLECTIONS: No thresholds defined');
              GOTO end_metric_result_loop;
          END;
        END;

        -- Get the current severity
        BEGIN
          -- Check for a severity for this particular key value
          SELECT severity_code INTO last_sev_code FROM MGMT_CURRENT_SEVERITY
          WHERE target_guid = collection.target_guid
            AND metric_guid = mguid
            AND key_value = metric_result(i).key_column_value;
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
            BEGIN
              -- Check for a severity for the metric column
              SELECT severity_code INTO last_sev_code FROM MGMT_CURRENT_SEVERITY
              WHERE  target_guid = collection.target_guid
                AND  metric_guid = mguid
                AND  key_value = ' ';
            EXCEPTION
              WHEN NO_DATA_FOUND
              THEN
                last_sev_code := 15;
            END;
        END;

      ELSE
        key_val := ' '; -- The default value in MGMT_METRIC_THRESHOLDS

        BEGIN
          -- Check if there are thresholds defined for this metric column
          SELECT t.warning_operator, t.warning_threshold,
                 t.critical_operator, t.critical_threshold,
                 t.num_occurences, t.num_warnings, t.num_criticals,
                 t.message, t.message_nlsid
          INTO warn_op, warn_threshold, crit_op, crit_threshold,
               num_oc, num_WAs, num_CAs, message_template, message_nlsid
          FROM MGMT_METRIC_THRESHOLDS t
          WHERE t.target_guid = collection.target_guid
            AND t.metric_guid = mguid
            AND t.coll_name = collection.coll_name
            AND t.key_value = ' ';
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
            -- Go onto next metric result
            dbms_output.put_line('RUN_COLLECTIONS: No thresholds defined');
            GOTO end_metric_result_loop;
        END;

        BEGIN
          -- Check for a severity for the metric column
          SELECT severity_code INTO last_sev_code FROM MGMT_CURRENT_SEVERITY
          WHERE target_guid = collection.target_guid
            AND metric_guid = mguid
            AND key_value = ' ';
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
            last_sev_code := 15;
        END;

      END IF;

      dbms_output.put_line('RUN_COLLECTIONS: Warning threshold: ' ||
                           warn_threshold || '(' || warn_op ||
                           ') Critical threshold: ' || crit_threshold ||
                           '(' || crit_op || '). Metric value: ' ||
                           metric_result(i).metric_value ||
                           ' Last severity: ' || last_sev_code);

      -- Get the new severity code based on the metric value returned. A
      -- metric_type of 0 indicates the metric value is a NUMBER, otherwise
      -- it is a STRING
      IF (mtype = 0)
      THEN
        new_sev_code := GET_NEW_SEVERITY_CODE(TO_NUMBER(metric_result(i).metric_value),
                                              warn_op, warn_threshold, crit_op,
                                              crit_threshold, last_sev_code,
                                              num_oc, num_WAs, num_CAs,
                                              mguid, tkey_val);
      ELSE
        new_sev_code := GET_NEW_SEVERITY_CODE(metric_result(i).metric_value,
                                              warn_op, warn_threshold, crit_op,
                                              crit_threshold, last_sev_code,
                                              num_oc, num_WAs, num_CAs,
                                              mguid, tkey_val);
      END IF;

      -- Check if the new code is a CRITICAL severity
      IF new_sev_code = 25
      THEN
        dbms_output.put_line('RUN_COLLECTIONS: Adding critical severity');

        FORMAT_SEVERITY_MESSAGE(collection.target_guid,
                                collection.metric_name,
                                metric_result(i).metric_column,
                                m_label, c_label,
                                key_val, warn_threshold, crit_threshold, num_oc,
                                metric_result(i).metric_value, 2,
                                message_template, metric_result,
                                message, nls_params);

        -- Insert a critical severity record
        EMD_SCHEMA.ADD_CRITICAL_SEVERITY(
         tname, ttype, collection.metric_name, metric_result(i).metric_column,
         key_val, 2, NULL, message, message_nlsid, nls_params);
      ELSIF new_sev_code = 20
      THEN
        dbms_output.put_line('RUN_COLLECTIONS: Adding warning severity');

        FORMAT_SEVERITY_MESSAGE(collection.target_guid,
                                collection.metric_name,
                                metric_result(i).metric_column,
                                m_label, c_label,
                                key_val, warn_threshold, crit_threshold, num_oc,
                                metric_result(i).metric_value, 1,
                                message_template, metric_result,
                                message, nls_params);

        -- Insert a warning severity record
        EMD_SCHEMA.ADD_WARNING_SEVERITY(
         tname, ttype, collection.metric_name, metric_result(i).metric_column,
         key_val, 2, NULL, message, message_nlsid, nls_params);
      ELSIF new_sev_code = 15
      THEN
        dbms_output.put_line('RUN_COLLECTIONS: Clearing severity');

        FORMAT_SEVERITY_MESSAGE(collection.target_guid,
                                collection.metric_name,
                                metric_result(i).metric_column,
                                m_label, c_label,
                                key_val, warn_threshold, crit_threshold, num_oc,
                                metric_result(i).metric_value, 0,
                                message_template, metric_result,
                                message, nls_params);

        -- Insert a clear severity record
        EMD_SCHEMA.ADD_SEVERITY_CLEAR(
         tname, ttype, collection.metric_name, metric_result(i).metric_column,
         key_val, 2, NULL, message, message_nlsid, nls_params);
      ELSIF new_sev_code = -1
      THEN
        dbms_output.put_line('RUN_COLLECTIONS: Bad threshold');
        mgmt_log.log_error(EMD_COLLECTION.MODULE_NAME,
                           MGMT_GLOBAL.BAD_THRESHOLD_ERR,
                           MGMT_GLOBAL.BAD_THRESHOLD_ERR_M ||
                           collection.metric_name || '/' || tname || '/' ||
                           ttype,null,null,null,null,'ERROR');
      ELSE
        dbms_output.put_line('RUN_COLLECTIONS: No change in severity');
      END IF;

      <<end_metric_result_loop>>

      -- Store the value in the mgmt_metrics_raw table
      IF collection.store_metric = 'Y' OR
         collection.store_metric = 'y'
      THEN
        IF mtype = 0
        THEN
          EMD_SCHEMA.ADD_METRIC_DATA (tname, ttype,
                                      collection.metric_name,
                                      metric_result(i).metric_column,
                                      key_val,
                                      TO_NUMBER(metric_result(i).metric_value),
                                      end_time);
        ELSE
          EMD_SCHEMA.ADD_METRIC_DATA (tname, ttype,
                                      collection.metric_name,
                                      metric_result(i).metric_column,
                                      key_val,
                                      metric_result(i).metric_value,
                                      end_time);
        END IF;
      END IF;

    END LOOP;

    -- Update the last_load_time for the target now that all metric
    -- values for this collection have been processed
    IF collection.store_metric = 'Y' OR
       collection.store_metric = 'y'
    THEN
      UPDATE MGMT_TARGETS SET LAST_LOAD_TIME = end_time
      WHERE TARGET_GUID = collection.target_guid AND
            (LAST_LOAD_TIME < end_time OR
             LAST_LOAD_TIME IS NULL);
    END IF;

    <<next_collection>>

    COMMIT;

  EXCEPTION
    WHEN OTHERS
    THEN
      mgmt_log.log_error(EMD_COLLECTION.MODULE_NAME,
                         MGMT_GLOBAL.COLLECTION_ERR,
                         MGMT_GLOBAL.COLLECTION_ERR_M ||
                         collection.metric_name || '/' || tname || '/' ||
                         ttype || '. ' || SQLERRM,null,null,null,null,'ERROR');
      ROLLBACK;
  END;
  END LOOP;

  IF cnt > 0
  THEN
    duration := SYSDATE - perf_start;
    duration := duration * (24 * 60 * 60 *1000);

    mgmt_log.log_performance(EMD_COLLECTION.MODULE_NAME, duration, SYSDATE,
                             'Y', 'Collections', cnt);
  END IF;

END RUN_COLLECTIONS;

以下一段文字概括了这些内部操作的功能:

The EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS job performs all the necessary maintenance tasks for the database control repository. These tasks include :

+ Agent Ping Verification (EM_PING.MARK_NODE_STATUS)
+ Job Purge (MGMT_JOB_ENGINE.APPLY_PURGE_POLICIES)
+ Metric Rollup (EMD_LOADER.ROLLUP)
+ Purge Policies (EM_PURGE.APPLY_PURGE_POLICIES)
+ Repository Metric Severity Calculation (EM_SEVERITY_REPOS.EXECUTE_REPOS_SEVERITY_EVAL)
+ Repository Side Collections (EMD_COLLECTION.RUN_COLLECTIONS)
+ Send Notifications

供参考。


历史上的今天...
    >> 2009-11-19文章:
    >> 2007-11-19文章:
           DBA警世录:where条件很重要
           儿子百天了
    >> 2006-11-19文章:
           知音如不赏 归卧故山秋
    >> 2005-11-19文章:
    >> 2004-11-19文章:
           Man Page Of gethrtime
           龙枪编年史

无觅

By eygle on 2012-11-19 08:59 | Comments (0) | Case | 3057 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com