CPU utilization is High while executing SQL query in webmethods

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

Hi Sandy,

please provide the exact version of IntegrationServer, TradingNetworks, JDBC Adapter with FixLevels.
What is your Oracle database and driver version?

Is there a specific reason you are using JDBC Adapter services for selecting data from these tables as they look like internal TradingNetworks tables?

You should be using TradingNetworks Built-In Services instead.

Is Archhiving for TradingNetworks activated to reduce the size of the runtime tables and move data to the archive tables (in the same schema but different table names)?

Refer to the TradingNetworks Built-In Services Guide for further informations.

Regards,
Holger

Hi Thomsen,

Thanks for the reply. Below are my comments.

We want to replicate the same issue in test env unfortunately it is not getting replicated. CPU utilization is normal, there is no long running query/session established.

The query which is running for days is provided in the earlier post were it is not at all coded in the flow services/adapter services. We are not sure which is that query :frowning: :frowning:

Hi Sandy,

you will find the driver version inside the driver jar in a file called META-IN/MANIFEST.MF.

This can be extracted by any zip-compatible program and viewed with any editor.

The mentioned query looks a little weird to me.
Do you know where the values like EE_OB10 are coming from?
This might help to identify the origin of the query.

Regards,
Holger

Hi,
I will provide you the version details

There is one partner profile by name EB/OB10 configured in MWS But in the query it shows with ‘_’ .
I checked the relevant service but there is no query defined for any of the service used by this profile. It’s an Rosettanet flow.

Thanks

Hi Sandy,

than this might be a RosettaNet issue.

As I have never worked with RosettaNet I am not able to assist further.
Maybe some other members can take over.

Regards,
Holger