Slow Query Executed When Invoking Out-of-the-box Web Service Operation ResponseCreateResponse (Doc ID 2616480.1)

Last updated on MAY 05, 2020

Applies to:

Symptoms

On : 16.10 [IP2016] version, Siebel Marketing


Slow query executed when invoking out-of-the-box web service operation ResponseCreateResponse due to no proper where clause while it is querying on Response BC



STEPS
-----------------------
The issue can be reproduced at will with the following steps:

Functional Steps:-

1. Launch the marketing campaign

2. call center agents are calling campaign members

3. Agent need to create response in siebel after they talked to customer

4. Agents creating the response in siebel via web service

Steps to send request to siebel using webservice :-

1. Make sure that EAI component is up and running, increase EAIObjMgr_enu log level to 5
2. Generate WSDL for WebService : Response
3. Create new SOAP UI project with the WSDL generated in second step
4. Post sample request xml for Operation "ResponseCreateResponse"

5. Capture the logs and search for below line in log file
.CON_TYPE_CD IS NULL

Observed that below SQL which is retrieving all the responses

SQLSummary Stmt 4 000000395dd310ac:0 2019-11-18 22:28:00 SELECT /*+ ALL_ROWS */
  
  FROM
  SIEBEL.S_CS_PATH T1,
  SIEBEL.S_SRC T2,
  SIEBEL.S_MKTG_OFFR T3,
  SIEBEL.S_DMND_CRTN_PRG T4,
  SIEBEL.S_COMM_DTL T5,
  SIEBEL.S_ORG_EXT T6,
  SIEBEL.S_COMMUNICATION T7
  WHERE
  T4.MKTG_OFFR_ID = T3.ROW_ID (+) AND
  T2.PR_CS_PATH_ID = T1.ROW_ID (+) AND
  T7.ACCNT_ID = T6.PAR_ROW_ID (+) AND
  T7.SRC_ID = T2.ROW_ID (+) AND
  T7.DCP_ID = T4.ROW_ID (+) AND
  T7.ROW_ID = T5.PAR_ROW_ID (+) AND
  (T7.CON_TYPE_CD IS NULL)
  ORDER BY
  T7.COMM_UID

SQLSummary BindVar 4 000000395dd310ac:0 2019-11-18 22:28:00
1: EAIObjMgr_enu,srvr1,37748746,SADMIN,000000395dd310ac:0,,Response,



observed that each invocation of out-of-the-box web service operation ResponseCreateResponse (business service: Response, method: CreateResponse) executes a low-performing query on BC Response.

The query is executed with virtually no effective search spec which causes the DB to retrieve all Response records.

Changes

Cause

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