Performance Issue Running BDM Job Due To SELECT COUNT(*) Query By BS UCM Batch Manager (Doc ID 1955077.1)

Last updated on MARCH 02, 2017

Applies to:

Siebel Universal Customer Master - Version 8.1.1.10 [23021] and later
Information in this document applies to any platform.

Symptoms

FUNCTIONALITY:
---------------------
Siebel UCM Batch Process processing (performing delta load for master data updates) with exact match functionality enabled

ISSUE:
---------
Poor Performance while running the batch job, due to "SELECT COUNT(*)" query generated by the "UCM Batch Manager" BS.
This happen when Exact Matching functionality is enabled and no data is passed to the exact match fields.

e.g.

1 - Change BS "UCM Data Quality Manager" and added the user property: "Contact ExactMatch Field" with value "Alias:Alias;" and compiled it to the SRF.
2 - Insert a record into S_UCM_CONTACT without alias information
3 - Execute the UCM Batch Process with exact match enabled and inspect the logs

In the logs I could observe the select count(*) with no filter:
SELECT
     COUNT (*),
     :1
  FROM
      SIEBEL.S_PARTY T1,
      SIEBEL.S_PARTY T2,
      SIEBEL.S_POSTN_CON T3,
      SIEBEL.S_CONTACT T4
  WHERE
     T1.ROW_ID = T4.PAR_ROW_ID AND
     T3.POSTN_ID = :2 AND T4.ROW_ID = T3.CON_ID AND
     T2.ROW_ID = T3.POSTN_ID AND
     ((T1.ED_DELETED_FLG = 'N') AND
     (T4.PRIV_FLG = 'N' AND T1.PARTY_TYPE_CD != 'Suspect') AND
     (T3.CON_LAST_NAME >= :3))

ObjMgrSqlLog Detail 4 00000006538c14f4:0 2014-06-01 23:10:35 Bind variable 1: UCMBatchProcess,sia81,18874370,SADMIN,00000006538c14f4:0,,Contact,
ObjMgrSqlLog Detail 4 00000006538c14f4:0 2014-06-01 23:10:35 Bind variable 2: 0-5220
ObjMgrSqlLog Detail 4 00000006538c14f4:0 2014-06-01 23:10:35 Bind variable 3:


IMPACT:
----------
This causes a performance issue when loading data into Siebel.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms