今天同事反映,周末新切换的一个数据库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哈哈~