High CPU consumption in AWR Report EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS()

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS dbms_job is used for Enterprise Manager (EM) Database Control in 10g and above versions of the database.
NOTE: The DBMS job EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS is present in the database running EM Database Control and the note does not apply to EM Grid Control.

In AWR report shows a large no of executions and consumes a High CPU

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;

On checking the dbms_jobs table:

select job, schema_user, last_date, next_date, interval, what from dba_jobs;

 JOB NEXT_DATE NEXT_SEC DESCRIPTION                                                                                                                                                  
---- --------- -------- ----------------------------------------                                                                                                                     
4001 29-AUG-17 18:43:01 wwv_flow_cache.purge_sessions(p_purge_se                                                                                                                     
4002 29-AUG-17 11:50:43 wwv_flow_mail.push_queue(wwv_flow_platfo                                                                                                                     
  23 29-AUG-17 11:41:43 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROC 

JOB SCHEMA_USER WHAT                                         INTERVAL
--- ----------- -------------------------------------------- -----------------------
 23 SYSMAN      EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); sysdate + 1 / (24 * 60)

Solution:

Option 1: If enterprise manager database control is not needed.
If EM database control is not used, we did not configure it then we removed the job which caused a performance issue.
For Remove the job:

execute dbms_job.remove(21);

OR

exec sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> commit;

Option 2nd: If Enterprise Manager database console is needed.
To improve the performance we need to rebuild the index and reorganize the related tables as below steps:

1. Stop the DB Console service on all the nodes (if it’s running)
2. Stop the EM jobs by logging in as SYSMAN and run:

exec emd_maintenance.remove_em_dbms_jobs;
commit;

3. Reorganise the table as SYS or SYSMAN:

CREATE TABLE temp_dep AS SELECT * FROM MGMT_METRIC_DEPENDENCY_DETAILS WHERE 1=2;

exec dbms_redefinition.start_redef_table ('SYSMAN','MGMT_METRIC_DEPENDENCY_DETAILS','temp_dep');

DROP MATERIALIZED VIEW temp_dep;

CREATE TABLE temp_avail AS SELECT * FROM MGMT_CURRENT_AVAILABILITY WHERE 1=2;
exec dbms_redefinition.start_redef_table ('SYSMAN','MGMT_CURRENT_AVAILABILITY','temp_avail');
DROP MATERIALIZED VIEW temp_avail;

4. Rebuild the index used:

ALTER INDEX SEVERITY_PRIMARY_KEY REBUILD;
ALTER INDEX CUR_AVAIL_PRIMARY_KEY REBUILD;
ALTER INDEX PK_MGMT_METRIC_DEP_DETAILS REBUILD;

5. Resubmit the EM jobs:

exec emd_maintenance.submit_em_dbms_jobs;
commit;