环境:oracle 10.2.0.3+AIX 5L

客户一台生产系统,配置了EM,在做awr时,发现刷新em dbconsole的job排在buffer gets 第一位,而且导致系统负载增高

  Elapsed      CPU                  Elap per  % Total
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------
    26,012     25,719        1,440       18.1    36.0 6gvch1xu9ca3g
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

这个BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); job起的作用是：

每隔1分钟运行一下,收集下当前活动会话信息,包括top sql,cpu使用率等

查询metalink,确认是bug

Bug 8627066: JOB EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS() IS INCREASING LOAD ON DATABASE

Bug 属性

相关产品

Hdr: 8627066 10.2.0.3 OMS 10.2.0.3 PRODID-1370 PORTID-23
Abstract: JOB EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS() IS INCREASING LOAD ON DATABASE

*** 06/23/09 11:07 pm ***
TAR
---

Problem Description
-------------------
The dbconsole job EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); increasing
load on the database server. It is found in top session also.

Awr report shows following:

DB Name         DB Id    Instance     Inst Num Release     RAC Host
    ------------ ----------- ------------ -------- ----------- --- 
------------
    NMS-2531768489 nms2                2 10.2.0.3.0  YES telsimadb2
    
                  Snap Id      Snap Time      Sessions Curs/Sess
                --------- ------------------- -------- ---------
    Begin Snap:     16517 22-Jun-09 12:30:14       334       9.0
      End Snap:     16518 22-Jun-09 13:30:26       293       9.9
       Elapsed:               60.20 (mins)
       DB Time:              171.77 (mins)
    
    ..
    Load Profile
    ~~~~~~~~~~~~                            Per Second       Per Transaction
                                       ---------------       ---------------
                      Redo size:             83,350.30                333.99
                  Logical reads:             30,926.08                123.92
    
    ...
    ..
    Top 5 Timed Events                                         Avg %Total
    ~~~~~~~~~~~~~~~~~~                                        wait   Call
    Event                                 Waits    Time (s)   (ms)   Time 
Wait Class
    ------------------------------ ------------ ----------- ------ ------ 
----------
    CPU time                                          6,684          64.9
    log file sync                        65,785         816     12    7.9     
Commit
    db file parallel read                58,267         583     10    5.7   
User I/O
    ARCH wait on SENDREQ                    346         209    604    2.0    
Network
    gc current block 2-way              160,223         146      1    1.4    
Cluster
    
    ..
    
    SQL ordered by Elapsed Time             DB/Inst: NMS/nms2  Snaps: 
16517-16518
    -> Resources reported for PL/SQL code includes the resources used by all 
SQL
       statements called by the code.
    -> % Total DB Time is the Elapsed Time of the SQL statement divided
       into the Total Database Time multiplied by 100
    
      Elapsed      CPU                  Elap per  % Total
      Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
    ---------- ---------- ------------ ---------- ------- -------------
         2,958      2,234           50       59.2    28.7 6gvch1xu9ca3g
    DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken 
BOOLEAN :
    = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := 
next_date
    ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
    

目前还没出补丁来修复这个bug,如果要解决这个bug，看来只能停用em dbconsole 了