My Oracle Support Banner

S_CONTACT / S_POSTN / S_POSTN_CON Related Query Is Showing High CPU Consumption At DB Server Due To Bad Execution Plan (Doc ID 2469333.1)

Last updated on NOVEMBER 21, 2019

Applies to:

Siebel CRM - Version 16.19 [IP2016] and later
Information in this document applies to any platform.

Symptoms

On : 16.19

ACTUAL BEHAVIOR
---------------
The Oracle database optimizer is choosing suboptimal execution plan at times leading to general Siebel application slowness when doing queries on Contacts.
It is observed that increase logging level to 5 for all events helps to avoid the issue, and at times it is decreasing the logging that helps.

Below is the query impacting the application:

SELECT
... <lines were suppressed>

FROM 

      SIEBEL.S_ORG_EXT T1,
      SIEBEL.S_CON_ADDR T2,
      SIEBEL.S_MED_SPEC T3,
      SIEBEL.S_CONTACT_TNTX T4,
      SIEBEL.S_POSTN T5,
      SIEBEL.S_PARTY T6,
      SIEBEL.S_PARTY T7,
      SIEBEL.S_CONTACT_SS T8,
      SIEBEL.S_ORG_EXT_FNX T9,
      SIEBEL.S_USER T10,
      SIEBEL.S_CONTACT_LOYX T11,
      SIEBEL.S_CONTACT_UCMX T12,
      SIEBEL.S_PRI_LST T13,
      SIEBEL.S_PARTY T14,
      SIEBEL.S_INDUST T15,
      SIEBEL.S_DQ_CON_KEY T16,
      SIEBEL.S_CONTACT_X T17,
      SIEBEL.S_POSTN T18,
      SIEBEL.S_EMP_PER T19,
      SIEBEL.S_ORG_EXT T20,
      SIEBEL.S_CONTACT_NAME T21,
      SIEBEL.S_PS_CONTACT T22,
      SIEBEL.S_USER T23,
      SIEBEL.S_USER T24,
      SIEBEL.S_CONTACT_FNX T25,
      SIEBEL.S_PARTY T26,
      SIEBEL.S_USER T27,
      SIEBEL.S_POSTN_CON T28,
      SIEBEL.S_POSTN_CON T29,
      SIEBEL.S_POSTN T30,
      SIEBEL.S_ADDR_PER T31,
      SIEBEL.S_CONTACT2_FNX T32,
      SIEBEL.S_CONTACT T33
WHERE
T33.PR_DEPT_OU_ID = T1.PAR_ROW_ID (+) AND
T1.PR_POSTN_ID = T30.PAR_ROW_ID (+) AND
T33.CURR_PRI_LST_ID = T13.ROW_ID (+) AND
T33.PR_POSTN_ID = T18.PAR_ROW_ID (+) AND
T7.ROW_ID = T29.CON_ID (+) AND T29.POSTN_ID (+) = :2 AND
T30.PR_EMP_ID = T23.PAR_ROW_ID (+) AND
T18.PR_EMP_ID = T27.PAR_ROW_ID (+) AND
T33.PR_PER_ADDR_ID = T31.ROW_ID (+) AND
T12.PR_NAME_ID = T21.ROW_ID (+) AND
T29.CON_ID = T2.CONTACT_ID (+) AND T29.PR_ADDR_ID = T2.ADDR_PER_ID (+) AND
T7.ROW_ID = T16.CONTACT_ID (+) AND
T33.MED_SPEC_ID = T3.ROW_ID (+) AND
T33.PR_INDUST_ID = T15.ROW_ID (+) AND
T7.ROW_ID = T25.PAR_ROW_ID (+) AND
T7.ROW_ID = T33.PAR_ROW_ID AND
T7.ROW_ID = T19.PAR_ROW_ID (+) AND
T7.ROW_ID = T4.PAR_ROW_ID (+) AND
T7.ROW_ID = T11.PAR_ROW_ID (+) AND
T7.ROW_ID = T17.PAR_ROW_ID (+) AND
T7.ROW_ID = T32.PAR_ROW_ID (+) AND
T7.ROW_ID = T12.PAR_ROW_ID (+) AND
T7.ROW_ID = T22.PAR_ROW_ID (+) AND
T7.ROW_ID = T8.PAR_ROW_ID (+) AND
T33.PR_POSTN_ID = T28.POSTN_ID AND T33.ROW_ID = T28.CON_ID AND
T28.POSTN_ID = T26.ROW_ID AND
T28.POSTN_ID = T5.PAR_ROW_ID (+) AND
T5.PR_EMP_ID = T24.PAR_ROW_ID (+) AND
T33.PR_DEPT_OU_ID = T6.ROW_ID (+) AND
T33.PR_DEPT_OU_ID = T20.PAR_ROW_ID (+) AND
T33.PR_DEPT_OU_ID = T9.PAR_ROW_ID (+) AND
T33.PR_SYNC_USER_ID = T14.ROW_ID (+) AND
T33.PR_SYNC_USER_ID = T10.PAR_ROW_ID (+) AND
((T7.ED_DELETED_FLG = 'N') AND
(T33.PRIV_FLG = 'N' AND T7.PARTY_TYPE_CD != 'Suspect')) AND
(T7.ROW_ID = :3)
ObjMgrSqlLog Detail 4 0003c8a95bb812ba:0 2018-10-09 16:00:36 Bind variable 1: FINSObjMgr_esn,siebapp01,27263360,L9O1H7R4,0003c8a95bb812ba:0,,Contact,Contact Summary View
ObjMgrSqlLog Detail 4 0003c8a95bb812ba:0 2018-10-09 16:00:36 Bind variable 2: 1-6O0X2S
ObjMgrSqlLog Detail 4 0003c8a95bb812ba:0 2018-10-09 16:00:36 Bind variable 3: 1-28K-16707
ObjMgrSqlLog Debug 5 0003c8a95bb812ba:0 2018-10-09 16:00:36 User search spec: 1-28K-16707

When the S_POSTN_CON_M3 is chosen the execution is good, but when the S_POSTN_CON_M51 or S_POSTN_CON_M53 are chosen the execution is bad.


EXPECTED BEHAVIOR
-----------------------
The Oracle database optimizer should choose the optimal execution plan for the same query independently of the bind variables values

Changes

 

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
Changes
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.