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 laterInformation in this document applies to any platform.
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
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 |