今天同事反映，周末新切换的一个数据库CPU load定期出现高峰，图形怪异：
检查了一下系统的定时任务：
09:41:05 sys@DSEDI>select job_name,state,JOB_CREATOR,JOB_ACTION,NUMBER_OF_ARGUMENTS from dba_scheduler_jobs order by 2;
JOB_NAME STATE JOB_CREATOR JOB_ACTION NUMBER_OF_ARGUMENTS
------------------------------ --------------- ------------------------------ ------------------------------------------------------- -------------------
XMLDB_NFS_CLEANUP_JOB DISABLED SYS xdb.dbms_xdbutil_int.cleanup_expired_nfsclients 0
HM_CREATE_OFFLINE_DICTIONARY DISABLED SYS dbms_hm.create_offline_dictionary 0
FILE_WATCHER DISABLED SYS
FGR$AUTOPURGE_JOB DISABLED SYS sys.dbms_file_group.purge_file_group(NULL); 0
BSLN_MAINTAIN_STATS_JOB SCHEDULED SYS
DRA_REEVALUATE_OPEN_FAILURES SCHEDULED SYS dbms_ir.reevaluateopenfailures 4
RLM$EVTCLEANUP SCHEDULED SYS begin dbms_rlmgr_dr.cleanup_events; end; 0
ORA$AUTOTASK_CLEAN SCHEDULED SYS
SM$CLEAN_AUTO_SPLIT_MERGE SCHEDULED SYS sys.dbms_streams_auto_int.clean_auto_split_merge; 0
PURGE_LOG SCHEDULED SYS
MGMT_STATS_CONFIG_JOB SCHEDULED SYS ORACLE_OCM.MGMT_CONFIG.collect_stats 0
MGMT_CONFIG_JOB SCHEDULED SYS ORACLE_OCM.MGMT_CONFIG.collect_config 0
RSE$CLEAN_RECOVERABLE_SCRIPT SCHEDULED SYS sys.dbms_streams_auto_int.clean_recoverable_script; 0
RLM$SCHDNEGACTION SCHEDULED SYS begin dbms_rlmgr_dr.execschdactions('RLM$SCHDNEGACTION' 0
); end;
14 rows selected.
09:41:58 sys@DSEDI>
10:00:07 sys@DSEDI>select log_date,owner,job_name,job_class 10:00:07 2 from (select log_date,owner,job_name,job_class,status from dba_scheduler_job_log order by log_date desc) 10:00:07 3 where rownum<=50 10:00:08 4 / LOG_DATE OWNER JOB_NAME JOB_CLASS --------------------------------------------------------------------------- ------------------------------ ----------------------------------- ------------------------------ 27-OCT-14 09.04.33.045472 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 09.00.15.621893 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 27-OCT-14 08.06.57.024628 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 08.00.15.642695 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 27-OCT-14 07.09.21.050342 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 07.00.15.639026 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 27-OCT-14 06.11.45.031676 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 06.00.15.641999 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 27-OCT-14 05.14.09.051620 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 05.00.15.641029 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 27-OCT-14 04.16.33.073416 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 04.00.15.639421 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 27-OCT-14 03.18.57.061664 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 03.00.15.649560 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 27-OCT-14 02.21.21.051382 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 02.00.15.638944 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 27-OCT-14 01.23.45.050094 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 01.00.15.631578 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 27-OCT-14 12.26.09.060165 AM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 27-OCT-14 12.00.15.642808 AM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 26-OCT-14 11.28.33.024408 PM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 26-OCT-14 11.00.15.620321 PM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 26-OCT-14 10.30.57.059413 PM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 26-OCT-14 10.10.54.204352 PM +08:00 SYS ORA$AT_OS_OPT_SY_609 ORA$AT_JCNRM_OS 26-OCT-14 10.10.54.203731 PM +08:00 SYS ORA$AT_OS_OPT_SY_609 ORA$AT_JCNRM_OS 26-OCT-14 10.10.54.202868 PM +08:00 SYS ORA$AT_OS_OPT_SY_609 ORA$AT_JCNRM_OS 26-OCT-14 10.10.21.146054 PM +08:00 SYS ORA$AT_SA_SPC_SY_610 ORA$AT_JCNRM_SA 26-OCT-14 10.10.21.145627 PM +08:00 SYS ORA$AT_SA_SPC_SY_610 ORA$AT_JCNRM_SA 26-OCT-14 10.10.21.145174 PM +08:00 SYS ORA$AT_SA_SPC_SY_610 ORA$AT_JCNRM_SA 26-OCT-14 10.10.06.680690 PM +08:00 SYS ORA$AT_SA_SPC_SY_610 ORA$AT_JCNRM_SA 26-OCT-14 10.10.06.675891 PM +08:00 SYS ORA$AT_OS_OPT_SY_609 ORA$AT_JCNRM_OS 26-OCT-14 10.10.06.668888 PM +08:00 SYS ORA$AT_SA_SPC_SY_610 ORA$AT_JCNRM_SA 26-OCT-14 10.10.06.661406 PM +08:00 SYS ORA$AT_OS_OPT_SY_609 ORA$AT_JCNRM_OS 26-OCT-14 10.10.06.654541 PM +08:00 SYS ORA$AT_OS_OPT_SY_609 ORA$AT_JCNRM_OS 26-OCT-14 10.10.06.651496 PM +08:00 SYS ORA$AT_OS_OPT_SY_609 ORA$AT_JCNRM_OS 26-OCT-14 10.00.15.624049 PM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 26-OCT-14 09.33.21.031707 PM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 26-OCT-14 09.00.15.622406 PM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 26-OCT-14 08.35.45.055836 PM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 26-OCT-14 08.00.15.682260 PM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 26-OCT-14 07.38.09.027828 PM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 26-OCT-14 07.00.15.649926 PM +08:00 EXFSYS RLM$EVTCLEANUP DEFAULT_JOB_CLASS 26-OCT-14 06.40.33.055890 PM +08:00 EXFSYS RLM$SCHDNEGACTION DEFAULT_JOB_CLASS 26-OCT-14 06.14.24.513691 PM +08:00 SYS ORA$AT_OS_OPT_SY_607 ORA$AT_JCNRM_OS 26-OCT-14 06.14.24.513393 PM +08:00 SYS ORA$AT_OS_OPT_SY_607 ORA$AT_JCNRM_OS 26-OCT-14 06.14.24.512879 PM +08:00 SYS ORA$AT_OS_OPT_SY_607 ORA$AT_JCNRM_OS 26-OCT-14 06.10.10.444293 PM +08:00 SYS ORA$AT_SA_SPC_SY_608 ORA$AT_JCNRM_SA 26-OCT-14 06.10.10.443353 PM +08:00 SYS ORA$AT_SA_SPC_SY_608 ORA$AT_JCNRM_SA 26-OCT-14 06.10.10.442373 PM +08:00 SYS ORA$AT_SA_SPC_SY_608 ORA$AT_JCNRM_SA 26-OCT-14 06.09.55.786026 PM +08:00 SYS ORA$AT_SA_SPC_SY_608 ORA$AT_JCNRM_SA 50 rows selected. 10:00:09 sys@DSEDI>
主要是resource manager的定时维护任务，因此手工关闭定是维护任务。
这里，EXFSYS是Oracle Expression Filter 组件的owner，根据mos的建议，可以卸载该组件：
[oracle@v5ecdb2 trace]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 27 09:28:57 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 09:28:57 sys@DSEDI> 11:02:00 sys@DSEDI> 11:02:01 sys@DSEDI> 11:02:01 sys@DSEDI>@$ORACLE_HOME/rdbms/admin/catnoexf.sql PL/SQL procedure successfully completed. User dropped. Package dropped. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. 11:02:32 sys@DSEDI>
然后停止相关的自动维护的job：
11:05:34 sys@DSEDI>select OWNER,job_name,PROGRAM_NAME,ENABLED from dba_scheduler_jobs where owner='ORACLE_OCM';
OWNER JOB_NAME PROGRAM_NAME ENABL
------------------------------ ------------------------------ ----------------------------------- -----
ORACLE_OCM MGMT_CONFIG_JOB FALSE
ORACLE_OCM MGMT_STATS_CONFIG_JOB FALSE
11:05:37 sys@DSEDI>select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor DISABLED
sql tuning advisor DISABLED
11:05:42 sys@DSEDI>
11:05:42 sys@DSEDI>
11:06:40 sys@DSEDI>select * from v$rsrc_plan;
ID NAME IS_TO CPU INS PARALLEL_SERVERS_ACTIVE PARALLEL_SERVERS_TOTAL
---------- -------------------------------- ----- --- --- ----------------------- ----------------------
PARALLEL_EXECUTION_MANAGED
--------------------------------
12540 INTERNAL_PLAN TRUE OFF OFF 0 640
FIFO
11:06:49 sys@DSEDI>
从zabbix上观察，11点02分操作完成后，到现在为止，系统已经平稳了，O(∩_∩)O哈哈~
