Query On MGMT_METRIC_DEPENDENCY_DETAILS Having Performance Issues after Upgrading Exadata Plugin to 13.2.2 (Doc ID 2550388.1)

Last updated on NOVEMBER 16, 2022

Applies to:

Symptoms

After upgrading the Exadata plugin to 13.2.2, a huge increase of the number of records stored in MGMT_METRIC_DEPENDENCY_DETAILS/MGMT_METRIC_DEPENDENCY_DETAI_E view/table is causing performance issues in the repository database.

The slow pages identified due to this are under Performance metric -> I/O Response Time (MS) of the ASM service (generic_service target) which has wrongly being associated with all the available ASM disks in all the ASM instances.

The following statement is found to run extensively on the repository:

SQL_ID  9wsqfb7a218mb, child number 0
-------------------------------------
SELECT EM_SYSAVAIL_TGT_TZ(EDEP_TARGET_GUID, EDEP_TIMEZONE_REGION) FROM
MGMT_METRIC_DEPENDENCY_DETAILS WHERE TARGET_GUID = :B1

Also, this query is returning huge values for the keys (disks) associated with the "I/O Response time" metric for one ASM instance:

select target_guid, key_value, dep_coll_name, count(1) from mgmt_metric_dependency_details group by target_guid,dep_coll_name,key_value order by 4 desc;

Changes

Cause

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