Bad Performance During Campaign Load Process (Doc ID 1068492.1)

Last updated on MARCH 02, 2017

Applies to:

Siebel Marketing - Version 8.0.0.6 [20423] to 8.2.2.3 SIA [23021] [Release V8]
Information in this document applies to any platform.
Checked for relevance on 04-Sep-2013

Symptoms

Campaign load process shows bad performance.
 The following  query takes a long time to execute  ( during step 'Update Load Status (Complete)' )

SELECT /*+ ALL_ROWS */
T8.CONFLICT_ID,
T8.LAST_UPD,
T8.CREATED,
T8.LAST_UPD_BY,
T8.CREATED_BY,
T8.MODIFICATION_NUM,
T8.ROW_ID,
T2.PR_DEPT_OU_ID,
T8.CALL_COMPLETED,
T4.REVN_GOAL_CURCY_CD,
T8.SRC_ID,
T4.NAME,
T4.SRC_NUM,
T8.COMPLETED_DT,
T8.CON_PER_ID,
T4.OWNERSHIP_TYPE_CD,
T3.BASE_CURCY_CD,
T4.PREF_LANG_ID,
T8.LOAD_NUM,
T8.CAMP_LD_WAVE_ID,
T8.OPTY_ID,
T8.OUTCOME_CD,
T4.OWNER_EDIT_FLG,
T1.NAME,
T5.NAME,
T4.PARTNER_EDIT_FLG,
T8.POSTN_ID,
T4.PR_CS_PATH_ID,
T4.PR_DMND_CRT_PRG_ID,
T7.NAME,
T4.BU_ID,
T4.PR_PRTNR_ID,
T8.PRSP_CON_PER_ID,
T8.PR_COMM_ID,
T4.RESPONSE_TYPE,
T8.TARGET_NUM,
T6.WAVE_SETTING_NUM
FROM
PROD01.S_BU T1,
PROD01.S_CONTACT T2,
PROD01.S_ORG_EXT T3,
PROD01.S_SRC T4,
PROD01.S_POSTN T5,
PROD01.S_CAMP_LD_WAVE T6,
PROD01.S_DMND_CRTN_PRG T7,
PROD01.S_CAMP_CON T8
WHERE
T2.PR_DEPT_OU_ID = T3.PAR_ROW_ID (+) AND
T8.SRC_ID = T4.ROW_ID AND
T8.CON_PER_ID = T2.PAR_ROW_ID (+) AND
T4.PR_DMND_CRT_PRG_ID = T7.ROW_ID (+) AND
T8.BU_ID = T1.ROW_ID (+) AND
T8.POSTN_ID = T5.ROW_ID (+) AND
T8.CAMP_LD_WAVE_ID = T6.ROW_ID (+) AND
(T8.CON_PER_ID IS NOT NULL OR T8.PRSP_CON_PER_ID IS NOT NULL) AND
(T8.CAMP_LD_WAVE_ID = :1)
ORDER BY
T8.CAMP_LD_WAVE_ID, T8.EXT_SCORE

Changes

Index has been created on S_CAMP_CON using CAMP_LD_WAVE_ID and EXT_SCORE but performance is still bad.

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