Slow Processing for Inbound and Outbound Emails Due Slow Query on S_COMM_REQ or S_COMM_REQ_PKG Tables (Doc ID 537987.1)

Last updated on JANUARY 01, 2017

Applies to:

Siebel Email Response - Version 7.7.2.10 SIA [18385] to 8.2.2.4.15 [IP2013] [Release V7 to V8]
All Platforms

Symptoms


When CommOutboundManager component launch more than one Tasks (5 or 10) it impacts the overall Siebel Database performance leading to bad performance of Siebel Application.

The CommOutboundManager is executing a query on S_COMM_REQ for example that takes up to 1 minute to be executed.

SELECT
      T2.LAST_UPD_BY,
      T2.ROW_ID,
      T2.CONFLICT_ID,
      T2.CREATED_BY,
      T2.CREATED,
      T2.LAST_UPD,
      T2.MODIFICATION_NUM,
      T2.RECIP_SRCH_SPEC,
      T1.ROW_ID,
      T2.PROC_END_DT,
      T2.PAR_COMM_REQ_ID,
      T1.MODIFICATION_NUM,
      T2.NAME,
      T2.STATUS_CD,
      T2.STATUS_MSG,
      T2.COMMENTS,
      T1.LOGIN,
      T2.REQ_NUM,
      T2.ONLY_SND_PREF_FLG,
      T2.PROC_START_DT,
      T2.RECIP_BC_NAME,
      T2.CM_SRVR_COMP_NAME,
      T2.DFLT_MEDIUM_CD,
      T2.BUS_OBJ_NAME
   FROM
       SIEBEL.S_USER T1,
       SIEBEL.S_COMM_REQ T2
   WHERE
      T2.CREATED_BY = T1.PAR_ROW_ID (+)
   ORDER BY
      T2.REQ_NUM

The Table S_COMM_REQ has couple of million records most of them with Status: "Done".

Similar performance problems can be observed on table S_COMM_REQ_PKG that also has couple of millions records.
 

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