11.2定时任务引起的系统负载异常—案例1

联系:QQ(5163721)

标题:11.2定时任务引起的系统负载异常—案例1

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

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


无标题1


此条目发表在 Performence Tuning 分类目录,贴了 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注