SQL query containing LIKE Predicate is SLOW (Doc ID 762156.1)

Last updated on JULY 20, 2016

Applies to:

Siebel Sales Enterprise - Version 8.0.0.2 [20412] and later
Siebel CRM - Version 8.1.1.4 [21225] to 8.1.1.4 [21225] [Release V8]
Information in this document applies to any platform.
'Checked for relevance on 21-02-2013

Symptoms


The following  sql query containing a like clause is intermittently slow:

It has  returned response times in the range between 2 seconds and more than 10 minutes:

 
SELECT
T17.CONFLICT_ID,
T17.LAST_UPD,
T17.CREATED,
T17.LAST_UPD_BY,
T17.CREATED_BY,
T17.MODIFICATION_NUM,
T17.ROW_ID,
T8.BASE_CURCY_CD,
T17.TARGET_OU_ID,
T8.LOC,
T8.NAME,
T11.ACTIVE_FLG,
T17.SRA_DEFECT_ID,
T17.SRA_SR_ID,
T17.ACTIVITY_UID,
T17.AGREEMENT_ID,
T17.ALARM_FLAG,
T17.APPT_ALARM_TM_MIN,
T3.PIM_APPT_FLG,
T3.SEBL_APPT_FLG,
T3.UNSPRTD_REPT_FLG,
T17.BILLABLE_FLG,
T17.SRC_ID,
T17.COMMENTS_LONG,
T17.COST_CURCY_CD,
T17.NAME,
T17.CAL_TYPE_CD,
T17.TODO_ACTL_END_DT,
T17.DONE_FLG,
T17.APPT_START_DT,
T17.TODO_DUE_DT,
T17.APPT_DURATION_MIN,
T13.FORMAT_TYPE_CD,
T16.ENTLMNT_ID,
T17.TODO_AFTER_DT,
T12.NAME,
T12.CURCY_CD,
T17.OPTY_ID,
T17.APPT_REPT_APPT_ID,
T17.APPT_REPT_REPL_CD,
T4.SRV_REGN_ID,
T17.PCT_COMPLETE,
T17.TODO_PLAN_START_DT,
T17.TODO_PLAN_END_DT,
T17.PREV_ACT_ID,
T17.PR_ATT_ID,
T5.FST_NAME,
T17.TARGET_PER_ID,
T5.LAST_NAME,
T17.PR_ORDER_ID,
T17.OWNER_LOGIN,
T17.OWNER_PER_ID,
T17.PR_SYMPTOM_CD,
T17.EVT_PRIORITY_CD,
T17.PRIV_FLG,
T10.BL_CLASS_CD,
T10.BL_CURCY_CD,
T17.PROJ_ID,
T9.ACTL_END_DT,
T9.ACTL_START_DT,
T9.PLAN_END_DT,
T9.PLAN_START_DT,
T10.NAME,
T10.PROJ_NUM,
T17.APPT_REPT_FLG,
T17.APPT_REPT_END_DT,
T17.APPT_REPT_TYPE,
T15.OWN_INST_ID,
T15.INTEGRATION_ID,
T11.SRM_REQUEST_ID,
T17.TARGET_OU_ADDR_ID,
T11.STAGE_ID,
T17.TODO_ACTL_START_DT,
T17.EVT_STAT_CD,
T17.CAL_DISP_FLG,
T17.TEMPLATE_FLG,
T17.TODO_CD,
T7.LOGIN,
T2.ROW_ID,
T6.ROW_STATUS,
T14.ROW_ID,
T1.LAST_NAME,
T1.WORK_PH_NUM,
T13.ROW_ID,
T13.PAR_ROW_ID,
T13.MODIFICATION_NUM,
T13.CREATED_BY,
T13.LAST_UPD_BY,
T13.CREATED,
T13.LAST_UPD,
T13.CONFLICT_ID,
T13.PAR_ROW_ID,
T15.ROW_ID,
T15.PAR_ROW_ID,
T15.MODIFICATION_NUM,
T15.CREATED_BY,
T15.LAST_UPD_BY,
T15.CREATED,
T15.LAST_UPD,
T15.CONFLICT_ID,
T15.PAR_ROW_ID,
T16.ROW_ID,
T16.PAR_ROW_ID,
T16.MODIFICATION_NUM,
T16.CREATED_BY,
T16.LAST_UPD_BY,
T16.CREATED,
T16.LAST_UPD,
T16.CONFLICT_ID,
T16.PAR_ROW_ID,
T11.ROW_ID,
T11.PAR_ROW_ID,
T11.MODIFICATION_NUM,
T11.CREATED_BY,
T11.LAST_UPD_BY,
T11.CREATED,
T11.LAST_UPD,
T11.CONFLICT_ID,
T11.PAR_ROW_ID,
T3.ROW_ID,
T3.PAR_ROW_ID,
T3.MODIFICATION_NUM,
T3.CREATED_BY,
T3.LAST_UPD_BY,
T3.CREATED,
T3.LAST_UPD,
T3.CONFLICT_ID,
T3.PAR_ROW_ID,
T6.ROW_ID,
T2.ROW_ID,
T14.ROW_ID
FROM
siebel.S_CONTACT T1,
siebel.S_PARTY T2,
siebel.S_EVT_CAL T3,
siebel.S_EMP_PER T4,
siebel.S_CONTACT T5,
siebel.S_ACT_EMP T6,
siebel.S_USER T7,
siebel.S_ORG_EXT T8,
siebel.S_PROJITEM T9,
siebel.S_PROJ T10,
siebel.S_EVT_MKTG T11,
siebel.S_OPTY T12,
siebel.S_EVT_MAIL T13,
siebel.S_PARTY T14,
siebel.S_EVT_ACT_SS T15,
siebel.S_SRV_ACT T16,
siebel.S_EVT_ACT T17
WHERE
T17.OWNER_PER_ID = T4.PAR_ROW_ID (+) AND
T17.TARGET_PER_ID = T5.PAR_ROW_ID (+) AND
T17.OPTY_ID = T12.ROW_ID (+) AND
T17.TARGET_OU_ID = T8.PAR_ROW_ID (+) AND
T17.PROJ_ID = T10.ROW_ID (+) AND
T17.PROJ_ITEM_ID = T9.ROW_ID (+) AND
T17.ROW_ID = T13.PAR_ROW_ID (+) AND
T17.ROW_ID = T15.PAR_ROW_ID (+) AND
T17.ROW_ID = T16.PAR_ROW_ID (+) AND
T17.ROW_ID = T11.PAR_ROW_ID (+) AND
T17.ROW_ID = T3.PAR_ROW_ID (+) AND
T17.OWNER_PER_ID = T6.EMP_ID (+) AND T17.ROW_ID = T6.ACTIVITY_ID (+) AND
T6.EMP_ID = T2.ROW_ID (+) AND
T6.EMP_ID = T7.PAR_ROW_ID (+) AND
T17.TARGET_PER_ID = T14.ROW_ID (+) AND
T17.TARGET_PER_ID = T1.PAR_ROW_ID (+) AND
((T17.APPT_REPT_REPL_CD IS NULL) AND
(T17.TEMPLATE_FLG != 'Y' AND T17.TEMPLATE_FLG != 'P' OR T17.TEMPLATE_FLG IS NULL) AND
(T17.PRIV_FLG = :1 OR T17.PRIV_FLG IS NULL OR T17.OWNER_PER_ID = :2)) AND
(T17.ROW_ID LIKE :3)
ORDER BY
T17.ACTIVITY_UID

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