在最近的一次优化过程中发现了ORACLE 10g中一个作业EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS执行相当频繁，其实以前也看到过，只是没有做过多 的了解和关注。这个任务在某些版本或某些情况会引起一些性能问题。其实 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS这个作业是为Database Control收集相关数据的一个作业，如果没有使用Database Control，完全可以删除。下面是官方介绍资料
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
This job should be running every minute for performing all the above operations.
如下所示，它执行的频繁相当频繁，一分钟执行一次
SQL> SELECT SCHEMA_USER, WHAT, INTERVAL FROM DBA_JOBS
2 WHERE WHAT='EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();';
SCHEMA_USER WHAT INTERVAL
----------- -------------------------------------------- -------------------------
SYSMAN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); sysdate + 1 / (24 * 60)
SQL>
移除EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS
如何移除这个任务呢，一般情况下使用要用sysman用户登录操作，具体步骤如下所示：
1：首先检查用sysman账号是否锁定了，如果锁定了需要解锁，如果没有的话，直接跳过这一步
SQL> show user;
USER is "SYS"
SQL> select username,account_status from dba_users where username='SYSMAN';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYSMAN EXPIRED & LOCKED
SQL> alter user sysman account unlock;
User altered.
SQL> alter user sysman identified by newpassword;
User altered.
2:查看并设置参数job_queue_processes为0（当设定该值为0的时候则任意方式创建的job都不会运行）
SQL> show parameter job_queue_processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
SQL> alter system set job_queue_processes=0;
System altered.
SQL> select * from dba_jobs_running;
no rows selected
SQL> select * from dba_jobs_running;
no rows selected
SQL> select * from dba_jobs_running;
no rows selected
3. 以sysman登录执行下面脚本，移除该作业
SQL> exec sysman.emd_maintenance.remove_em_dbms_jobs;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
当然也可以执行下面脚本来移除任务
SQL> @<ORACLE_HOME>\sysman\admin\emdrep\sql\core\latest\admin\admin_remove_dbms_jobs.sql;
4：查询DBA_JOBS视图，确认任务是否移除，重设参数job_queue_processes值
If the EM jobs were submitted as SYS (or another SYSDBA account), the removal must be done as SYS (or that specific) account.
注意：如果EM的作业是以sys或者其他sysdba 提交的，则必须使用sys账号登录才能移除，上面以sysman登录执行的脚本并不能移除该任务。具体可以在查询作业的时候留意LOG_USER字段 （LOG_USER的值为sysman的才是sysman提交的，否则为其它sysdba）。切记切记。
重建EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS
1：以sysman用户登录，确认参数job_queue_processes不为0
SQL> show user;
USER is "SYSMAN"
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
SQL> alter system set job_queue_processes=10;
System altered.
2：执行下面脚本
SQL> exec emd_maintenance.submit_em_dbms_jobs;
PL/SQL procedure successfully completed.
或
SQL>@<ORACLE_HOME>\sysman\admin\emdrep\sql\core\latest\admin\
admin_submit_dbms_jobs.sql;
3：重编译无效对象
PL/SQL procedure successfully completed.
SQL> exec emd_maintenance.recompile_invalid_objects;
PL/SQL procedure successfully completed.
SQL>
For 11.1.0.7.0 and above databases:
SQL> exec emd_maint_util.recompile_invalid_objects;
