BUG 14726136 – INCREASED REDO LOGGING ON REPOSITORY DB AFTER UPGRADING TO OEM 12.1.0.2

Following up on my previous post about increased redo logging on the repository DB after upgrading to OEM 12cR2, we now have bug 14726136 filed.

The heavy redo activity is coming from SYSMAN inserts into the EM_JOB_METRICS table. It’s doing 60 inserts a second, in batches of 10 per LOG_TIMESTAMP, where each insert looks like:

insert into "SYSMAN"."EM_JOB_METRICS"("SOURCE","LOG_TIMESTAMP","METRIC_NAME","METRIC_QUALIFIER","METRIC_VALUE") values
('omshost.domain.com:4890_Management_Service',TO_TIMESTAMP('26-SEP-12 03.09.25.215415 PM'),'DISP_REQ_STEPS','0','25');

The 10 inserts differ only in the values of the METRIC_QUALIFIER and METRIC_VALUE columns:

'0', '25'
'0', '0'
'1', '12'
'1', '0'
'2', '25'
'2', '0'
'3', '10'
'3', '0'
'4', '10'
'4', '0'

LogMiner shows this as a significant proportion of the total database activity, with the following extracts based on about 20 minutes (or about 200MB) of repository DB operation:

SQL> select TABLE_NAME, count(*) from v$logmnr_contents group by table_name order by 2 desc;

                                                       TABLE_NAME           COUNT(*)
                                                     EM_JOB_METRICS           201740
                                                                              163843
                                                    EM_METRIC_ITEMS            13374
                                                    EM_METRIC_VALUES           12524
                                                MGMT_AVAILABILITY_MARKER        3845
                                                       COL_USAGE$               2403
                                                          ROUT                  1453
                                                     SCHEDULER$_JOB             1067


SQL> select seg_owner, operation, count(*) from v$logmnr_contents group by seg_owner, operation order by 1;

                                              SEG_OWNER     OPERATION     COUNT(*)  
                                               DBSNMP        DELETE             13  
                                               DBSNMP        INSERT             13  
                                               DBSNMP        UPDATE              4  
                                                RCAT         DELETE            269  
                                                RCAT         INSERT           1372  
                                                RCAT    SELECT_FOR_UPDATE       50  
                                                RCAT         UPDATE            884  
                                                 SYS         DELETE             88  
                                                 SYS         INSERT           1041
                                                 SYS    SELECT_FOR_UPDATE      265
                                                 SYS         UPDATE           4802  
                                               SYSMAN        DELETE          97325  
                                               SYSMAN        INSERT         106610  
                                               SYSMAN       INTERNAL           196  
                                               SYSMAN       LOB_TRIM             5  
                                               SYSMAN       LOB_WRITE          326  
                                               SYSMAN   SELECT_FOR_UPDATE     1203  
                                               SYSMAN    SEL_LOB_LOCATOR       103  
                                               SYSMAN      UNSUPPORTED       13626  
                                               SYSMAN        UPDATE          21808
                                               UNKNOWN       DELETE             82  
                                               UNKNOWN       INSERT             80  
                                               UNKNOWN  SELECT_FOR_UPDATE       65
                                               UNKNOWN       UPDATE             65
                                                             COMMIT          19792
                                                          DPI SAVEPOINT         15
                                                            INTERNAL        124178
                                                            ROLLBACK            33
                                                              START          19825

This is not a particularly busy OMS, it’s monitoring fewer than 200 targets and only runs a single job every couple minutes.

Advertisements

One thought on “BUG 14726136 – INCREASED REDO LOGGING ON REPOSITORY DB AFTER UPGRADING TO OEM 12.1.0.2

  1. Pingback: Final (!) update/fix for EM12c increased redo from EM_JOB_METRICS after upgrade to R2 (bug/patch 14726136) | Pardy DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s