Hi Team,
We are in wM 9.5 version and facing an strange issue in production.
Created a flow service in which dynamic query adapter service is being called and it will fetch the details from oracle DB.
The code is running fine in production and also alert being sent for the errors but unfortunately there is one more query triggered at the oracle end which is never being used in the code and it consuming lot of CPU and it is running for days.
Used query in code:
select b.doctimestamp,
td.typename Document_Type,
s.corporationname sender,
r.corporationname receiver,
b.routingstatus Processing_Status,
b.userstatus,
b.nativeid Document_ID,
b.groupid Group_ID,
b.conversationid ConversationID,
a.b2buser,
a.briefmessage,
a.fullmessage
from activitylog a,
bizdoc b,
partner s,
partner r,
bizdoctypedef td
where b.doctimestamp >(sysdate-4/24) and
b.docid=a.relateddocid and
a.entrytype=0 and
b.senderid = s.partnerid and
b.receiverid = r.partnerid and
b.doctypeid = td.typeid and
(
(b.routingstatus=‘DONE W/ ERRORS’) or
(b.routingstatus=‘ACCEPTED W/ ERRORS’) or
(b.routingstatus=‘REPROCESSED W/ ERROR’) or
(b.routingstatus=‘RESUBMITTED W/ ERROR’)
)
ORDER BY b.doctimestamp DESC;
Unix team has received the high CPU utilization alert and they have informed to us , while checking from DB end the below particualr Session/Query is running for a long time which is utilizing more CPU and so we have informed you .This paticular session which is runnig more than days since there is no blcokings on the same .
This below query is not configured in the code but it is generating by itself.
Generated query:
SQL_ID 566gvw5cg8s74, child number 0
SELECT DISTINCT p.PartnerID, p.CorporationName, p.OrgUnitName,
p.Status FROM IDType idt, IDType idt_0, IDType idt_1, IDType idt_2,
IDType idt_3, PartnerID pid, PartnerID pid_0, PartnerID pid_1,
PartnerID pid_2, PartnerID pid_3, Partner p WHERE p.Deleted = 0 AND
p.PartnerID <> ‘000000000000000000000000’ AND p.Status <> ‘Pending’
AND p.PartnerID = pid.InternalID(+) AND p.PartnerID =
pid_0.InternalID(+) AND p.PartnerID = pid_1.InternalID(+) AND
p.PartnerID = pid_2.InternalID(+) AND p.PartnerID =
pid_3.InternalID(+) AND pid.IDType = idt.Type(+) AND pid_0.IDType =
idt_0.Type(+) AND pid_1.IDType = idt_1.Type(+) AND pid_2.IDType =
idt_2.Type(+) AND pid_3.IDType = idt_3.Type(+) AND ( (
upper(p.CorporationName) LIKE upper(‘%EE_OB10%’) OR
upper(pid.ExternalID) LIKE upper(‘%EE_OB10%’) OR
upper(pid_0.ExternalID) LIKE upper(‘%EE_OB10%’) OR
upper(pid_1.ExternalID) LIKE upper(‘%EE_OB10%’) OR
upper(pid_2.ExternalID) LIKE upper(‘%EE_OB10%’) OR upper(pid_3.Exter
The same is not happening in the test env.
Please help here.
Regards
S
