Oracle database performance suffering due to expensive query run by wasp probe in UMP 8.2

book

Article ID: 38159

calendar_today

Updated On:

Products

DX Infrastructure Management NIMSOFT PROBES

Issue/Introduction

Symptoms:

High CPU usage observed on an Oracle Database server where a UIM database is hosted.

DBA's may observe the following query running frequently and contributing to the CPU issue:

 

SELECT NULL AS table_cat, 

o.owner AS table_schem, 

o.object_name AS table_name, 

o.object_type AS table_type, 

NULL AS remarks 

FROM all_objects o 

WHERE o.owner LIKE :1 ESCAPE :"SYS_B_0" 

AND o.object_name LIKE :2 ESCAPE :"SYS_B_1" 

AND o.object_type IN (:"SYS_B_2", :"SYS_B_3", :"SYS_B_4") 

ORDER BY table_type, table_schem, table_name 

 

 

Environment:

Oracle 11gr2 or 12c database server with partitioning enabled on the UIM database and/or a shared DB environment with multiple databases on the server.

Cause:

The wasp probe performs a "validation query" when it makes a new connection to the database server in order to confirm that it has a valid connection and can retrieve results from the server.  The query above is the default validation query; it references the ALL_OBJECTS view which can contain a very large number of objects in a partitioned environment or an environment which hosts many databases.

Resolution:

We have released a hotfix for UMP 8.2 only as this seems to be the version most affected by this.    It is available here.

Other users who experience this issue in versions beyond 8.2 should contact support to request a hotfix, as it has currently not been ported to other versions due to lack of demand.

This issue will be fully resolved in UMP 8.5.

Environment

Release: CNMSPP99000-8.2-Unified Infrastructure Mgmt-Server Pack-- On Prem
Component: