在最近的一次優化過程中發現了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;
