Monday, September 19, 2011

DBMS_JOB MGMT_JOB_ENGINE.apply_purge_policies() causes high CPU

DBMS_JOB MGMT_JOB_ENGINE.apply_purge_policies() causes high CPU 




The dbms_job MGMT_JOB_ENGINE.apply_purge_policies() in the Repository Database consumes very high CPU resources when it is being executed. This can inturn affect other operations in the repository database like Loader, causing a high backlog.

This issue was investigated in Bug 8517252 : SQL FROM MGMT_JOB_ENGINE.APPLY_PURGE_POLICIES() CAUSING HIGH CPU USAGE.

The SQL causing the high cpu consumption was found to be : 
SELECT execution_id
FROM MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB j
WHERE e.job_id = j.job_id AND j.is_corrective_action = 0
AND status IN (5,4,3,18,8)
AND (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - e.start_time) > (:tf)
AND ROWNUM < 500 ORDER BY start_time desc


To resolve this issue, apply Patch 8517252 on top of the 10.2.0.5 OMS.
This is a generic patch applicable to any operating system.


Post a Comment