My Oracle Support Banner

Performance Issue While Fetching Candidate Records Using Join Of S_DEDUP_RESULT and S_DQ_ORG_KEY (Doc ID 2771584.1)

Last updated on APRIL 27, 2021

Applies to:

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

Symptoms

During the execution of Organization/Person match service, to fetch candidate records, separate query with join of S_DEDUP_RESULT and S_DQ_ORG_KEY was triggered for all matching account/contact

ids(with IN Clause) which is causing performance issue. Sample SQL is as mentioned below.

SELECT ....
  FROM
  SIEBEL.S_ADDR_PER T1,
  SIEBEL.S_CON_ADDR T2,
  SIEBEL.S_ADDR_PER T3,
  SIEBEL.S_ORG_EXT T4,
  SIEBEL.S_ORG_EXT_X T5,
  SIEBEL.S_PARTY T6,
  SIEBEL.S_DEDUP_RESULT T7,
  SIEBEL.S_CON_ADDR T8,
  SIEBEL.S_PARTY T9,
  SIEBEL.S_PRI_LST T10,
  SIEBEL.S_FN_RELTN T11,
  SIEBEL.S_INV_PROF T12,
  SIEBEL.S_ORG_EXT T13,
  SIEBEL.S_CON_ADDR T14,
  SIEBEL.S_USER T15,
  SIEBEL.S_POSTN T16,
  SIEBEL.S_ADDR_PER T17,
  SIEBEL.S_ORG_EXT_FNX T18,
  SIEBEL.S_ADDR_PER T19,
  SIEBEL.S_POSTN T20,
  SIEBEL.S_ORG_EXT T21,
  SIEBEL.S_PRI_LST T22,
  SIEBEL.S_ORG_EXT_SS T23,
  SIEBEL.S_ORG_BU T24,
  SIEBEL.S_PARTY T25,
  SIEBEL.S_DQ_ORG_KEY T26,
  SIEBEL.S_ORG_EXT T27,
  SIEBEL.S_ORG_EXT_LSX T28,
  SIEBEL.S_ACCNT_POSTN T29,
  SIEBEL.S_ADDR_PER T30,
  SIEBEL.S_ORG_PRTNR T31,
  SIEBEL.S_ORG_EXT T32
  WHERE
  T32.MASTER_OU_ID = T4.PAR_ROW_ID AND
  T32.PAR_OU_ID = T21.PAR_ROW_ID (+) AND
  T32.PR_POSTN_ID = T20.PAR_ROW_ID (+) AND
  T20.OU_ID = T27.PAR_ROW_ID (+) AND
  T32.PR_ADDR_ID = T30.ROW_ID (+) AND
  T9.ROW_ID = T7.DUP_OBJ_ID (+) AND
  T9.ROW_ID = T26.ACCNT_ID (+) AND
  T32.REL_ID = T11.ROW_ID (+) AND
  T32.CURR_PRI_LST_ID = T10.ROW_ID (+) AND
  T32.TAX_LIST_ID = T22.ROW_ID (+) AND
  T9.ROW_ID = T32.PAR_ROW_ID AND
  T9.ROW_ID = T28.PAR_ROW_ID (+) AND
  T9.ROW_ID = T18.PAR_ROW_ID (+) AND
  T9.ROW_ID = T31.PAR_ROW_ID (+) AND
  T9.ROW_ID = T5.PAR_ROW_ID (+) AND
  T9.ROW_ID = T23.PAR_ROW_ID (+) AND
  T32.PR_POSTN_ID = T29.POSITION_ID AND T32.ROW_ID = T29.OU_EXT_ID AND
  T29.POSITION_ID = T25.ROW_ID AND
  T29.POSITION_ID = T16.PAR_ROW_ID (+) AND
  T16.PR_EMP_ID = T15.PAR_ROW_ID (+) AND
  T32.PR_ADDR_ID = T8.ADDR_PER_ID (+) AND T32.ROW_ID = T8.ACCNT_ID (+) AND
  T32.PR_ADDR_ID = T3.ROW_ID (+) AND
  T32.PR_SHIP_ADDR_ID = T14.ADDR_PER_ID (+) AND T32.ROW_ID = T14.ACCNT_ID (+) AND
  T32.PR_SHIP_ADDR_ID = T1.ROW_ID (+) AND
  T32.PR_ADDR_ID = T19.ROW_ID (+) AND
  T32.PR_BL_ADDR_ID = T2.ADDR_PER_ID (+) AND T32.ROW_ID = T2.ACCNT_ID (+) AND
  T32.PR_BL_ADDR_ID = T17.ROW_ID (+) AND
  T32.BU_ID = T24.BU_ID (+) AND T32.ROW_ID = T24.ORG_ID (+) AND
  T24.BU_ID = T6.ROW_ID (+) AND
  T24.BU_ID = T13.PAR_ROW_ID (+) AND
  T32.PR_BILL_PRFL_ID = T12.ROW_ID (+) AND
  ((T32.INT_ORG_FLG != 'Y' OR T32.PRTNR_FLG != 'N') AND T32.ACCNT_FLG != 'N') AND
  (T32.ROW_ID = :2)

1: EAIObjMgr_enu,siebsrvr1,31457432,SADMIN,00000dc05f4332c2:0,,Account,
2: 1-ABCD
SQL Cursor with ID C2591788 Buscomp: Account, Forward Only: 0, Fetch Rows: 32620, Fetch All Time: 45.855 sec

 

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.