Rollup Job Taking Long Time Resulting in High CPU and Concurrency Waits in Repository Database (Doc ID 1356195.1)

Last updated on DECEMBER 23, 2014

Applies to:

Enterprise Manager Base Platform - Version 10.2.0.5 to 10.2.0.5 [Release 10.2]
Enterprise Manager Base Platform - Version 11.1.0.1 to 11.1.0.1 [Release 11.1]
Information in this document applies to any platform.

Symptoms

Rollup job is taking more time to finish in grid control. From ADDM report, following query is causing the issue:

INSERT INTO MGMT_METRICS_1HOUR H SELECT T.TARGET_GUID, T.METRIC_GUID, T.KEY_VALUE, TRUNC(T.COLLECTION_TIMESTAMP, 'HH24'), COUNT(T.TARGET_GUID), AVG(T.VALUE), MIN(T.VALUE), MAX(T.VALUE), STDDEV(T.VALUE) FROM MGMT_METRICS_RAW T, MGMT_TARGET_LIST_TEMP P WHERE T.TARGET_GUID = P.TARGET_GUID AND T.COLLECTION_TIMESTAMP >= P.ROLLUP_TIMESTAMP AND T.COLLECTION_TIMESTAMP < P.CUR_HOUR_ROLLUP AND P.ROLLUP_TABLE_NAME= 'MGMT_METRICS_1HOUR' AND T.VALUE IS NOT NULL AND P.INST_ID = :B3 AND P.WORKER_ID = :B2 AND P.BATCH_ID = :B1 AND P.STATUS = 'R' GROUP BY T.TARGET_GUID, T.METRIC_GUID, T.KEY_VALUE, TRUNC(T.COLLECTION_TIMESTAMP, 'HH24')

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms