EM 12c, EM 13c : EM_PING.RECORD_BATCH_HEARTBEAT (SQL ID - 2t7d73gqhp4j0) Causing Performance Problems In Repository Database (Doc ID 2241672.1)

Last updated on OCTOBER 13, 2019

Applies to:

Symptoms

Queries executed by EM_PING.RECORD_BATCH_HEARTBEAT were found to be causing performance problems in repository database.
The problematic SQL's were:

1. SQL ID 2t7d73gqhp4j0 - BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1 , :2 , :3 ); END;

This procedure was executed 9 times in 30 minutes window and every execution on an average took 850 seconds. It accepts the bunch of agents to record their heartbeat in repository.

2. SQL ID d84n442yrnwq3 - SELECT /*+ CARDINALITY(lockedtgt, 100) */ T.TARGET_GUID,
T.TARGET_TYPE FROM MGMT_TARGETS T, MGMT_CURRENT_AVAILABILITY A,
TABLE(CAST(:B1 AS MGMT_TARGET_GUID_ARRAY)) LOCKEDTGT WHERE
T.TARGET_GUID = A.TARGET_GUID AND A.CURRENT_STATUS = :B2 AND
T.REP_SIDE_AVAIL = 0 AND T.TARGET_GUID = LOCKEDTGT.COLUMN_VALUE ORDER
BY T.TARGET_GUID

The first SQL execute this SQL in a loop for every agent it receives. 

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.