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:Siebel Marketing - Version 16.10 [IP2016] and later
Information in this document applies to any platform.
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
The issue can be reproduced at will with the following 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 */
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)
SQLSummary BindVar 4 000000395dd310ac:0 2019-11-18 22:28:00
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.
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