Slow performance of udm_manager in Oracle environment - udm_inventory queue backed up

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

Symptoms: 

Slow performance of udm_manager when using an Oracle database, and high database server load.
 
The queue "udm_inventory" on the primary hub will be seen to back up and grow to a large size.
 

Environment: 

Oracle 11.2g or higher, UIM 8.2 or higher
 

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 preferred resolution is to upgrade to CA UIM 8.4 and set NLS_COMP and NLS_SORT to BINARY.  If you have already upgraded to 8.4 from a previous version of UIM you may still have the older settings for NLS_COMP and NLS_SORT.  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; 
 
For those that are unable to upgrade to 8.4 or unable to revert the settings to BINARY, the workaround is listed below.
 

Workaround: 

Overview
To improve performance of udm_manager with Oracle, it is necessary to enable a logon trigger that will set NLS_SORT and NLS_COMP to BINARY. Here is an overview of the required steps: 
  1. Install an updated version of udm_manager that supports custom configuration of Oracle connections.
  2. Create an additional user in Oracle.
  3. Grant full rights for that Oracle user to the datomic_kvs table of the original UIM user, and add a synonym that will allow the second user to access that table.
  4. Install a revised logon trigger that sets all logons from that second user to Binary.
  5. Edit the udm_manager Configuration to enable the second user
 
Details for each step are provided below. 
Step 1: Install an updated version of udm_manager. 
Custom configuration of Oracle connections is added as a feature beginning with udm_manager 8.22. Acquire the udm_manager.zip file from support if needed, and install it to the local archive using Admin Console or Infrastructure Manager. Then deploy it to the same hub that is currently running udm_manager to update that installation. Detailed instructions for deploying probes is available in the UIM documentation.
 
Step 2: Create an additional user in Oracle. 
From Oracle SQL Developer, connect as the SYSDBA user, open a SQL Worksheet, and run the following command:
 
create user CA_UIM_UDM; 
 
CA_UIM_UDM may be replaced with a different username if desired.
 
Step 3: Grant rights to the datomic_kvs table, and set up a synonym for that table. 
Issue the following commands to Oracle as SYSDBA:
 
GRANT SELECT, UPDATE, INSERT, DELETE ON CA_UIM.datomic_kvs TO CA_UIM_UDM; 
GRANT CREATE SESSION TO CA_UIM_UDM; 
CREATE PUBLIC SYNONYM datomic_kvs FOR CA_UIM.datomic_kvs;
 
CA_UIM should be replaced with the original Oracle login assigned to CA UIM. CA_UIM_UDM is the name of the additional user.
 
Step 4: Install a logon trigger to set connections to BINARY. 
Paste the following script into the SQL worksheet screen in SQL Developer. Replace CA_UIM_UDM with the username of your alternate user if needed. When the script is in place, press the "run script" button. Note: Do not change the name of the trigger, as it is replacing an existing trigger installed by CA UIM.
 
create or replace trigger TRN_NIS_LOGON after logon on database
declare session_user varchar2(30);
begin
  select username into session_user
  from v$session where audsid=userenv('sessionid');
 
  if session_user='CA_UIM_UDM'
  then
  execute immediate 'ALTER SESSION SET NLS_SORT=BINARY';
  execute immediate 'ALTER SESSION SET NLS_COMP=BINARY';
  else
  execute immediate 'ALTER SESSION SET NLS_SORT=BINARY_CI';
  execute immediate 'ALTER SESSION SET NLS_COMP=LINGUISTIC';       
  end if;
end;
 
Step 5: Edit the udm_manager configuration to enable the second user 
From Admin Console or Infrastructure Manager, select the udm_manager probe and choose "Raw Configure". (Press the CTRL key and right click to see the Raw Configure menu item in Infrastructure Manager.)
 
On the raw configure screen, add the following lines to the <setup> section of the configuration file: 
   <jdbc>
      url = jdbc:oracle:thin:@192.168.0.1:1521:DEORCL
      db_server = 192.168.0.1
      service_name = DEORCL
      user = CA_UIM_UDM
      cleartext_password = password
      provider = oracle
      port = 1521
   </jdbc>
 
Replace the values on each line with the appropriate values for your installation. You may view your current values by double clicking on the data_engine probe and selecting the database tab. User should be set to the username of the additional user that was created above.
 
Instead of using "cleartext_password", you may optionally use "password" and provide the encrypted password from your data_engine.cfg file. udm_manager uses the same password encryption as data_engine, so an encrypted password copied over will resolve to the same database password. The additional user you have added should be set to the same password as the original user in this case.
 
After completing this configuration, save and exit the raw configure screen and restart udm_manager. The binary trigger should now take effect.
 

Additional Information: 

Reinstalling or upgrading UIM or data_engine will overwrite the trigger. If this occurs, repeating step 4 above will reapply the binary trigger.
If udm_manager is set to log level 3 or higher, an "Insufficient Privileges" error will be logged at startup. This is caused by the new user not having "create table" rights to the original user’s data, and should be ignored.