« ACOUG 2012广州站和中山大学深圳职院校园行 | Blog首页 | AWR报告分析之一：高 DB CPU 消耗的性能根源 »
EMD_MAINTENANCE 定时任务引起的资源消耗
链接：https://www.eygle.com/archives/2012/11/emd_maintenance.html
在很多数据库中，尤其是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文章:
>> 2006-11-19文章:
>> 2005-11-19文章:
>> 2004-11-19文章:
By eygle on 2012-11-19 08:59 | Comments (0) | Case | 3057 |