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

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;

<>

-- 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;

<>

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

供参考。

By eygle on 2012-11-19 08:59 |

Comments (0) |

Case | 3057 |