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