Slow performance of discovery_server in Oracle environment

Document ID : KB000047059
Last Modified Date : 14/02/2018
Show Technical Document Details

Symptoms: 

Slow performance of discovery_server when using an Oracle database, and high database server load.

The queue "probeDiscovery" on the primary hub will be seen to back up and grow to a large size.

 

Environment: 

Oracle 11.2g or higher

UIM 8.4

 

Cause: 

Earlier versions of UIM included steps to set NLS_SORT and NLS_COMP to BINARY_CI and LINGUISTIC, respectively.  This had the effect on udm_manager of disabling the use of binary indexes.  The setting can be verified with the following SQL query: 
SELECT  * 
FROM    NLS_INSTANCE_PARAMETERS 
WHERE   parameter IN ('NLS_COMP', 'NLS_SORT'); 
 
Any result other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.
 

Resolution: 

The resolution is to upgrade discovery_server to 8.43 and set NLS_COMP and NLS_SORT to BINARY.  Please work with your DBA to revert these settings back to BINARY as shown here:
ALTER SYSTEM SET NLS_COMP=BINARY SCOPE=SPFILE; 
ALTER SYSTEM SET NLS_SORT=BINARY SCOPE=SPFILE; 
 

Additional Information:

See TEC1552807 for a related issue regarding udm_manager