Prepayment processes are stuck

[kahmed92]

Hi Erman,

We are facing database issues since one week. Last few days, some of concurrent requests are completing with success and some are stuck. We have raised SR and engr recommended Patch 21950151:R12.AR.C, on clone instance, prepayment working fine. But on production instance, after applying patch, we're on same state.

Like wise other requests, for example autoinvoice, sale order, switch responsibility taking time.

On an another SR, engr requested hang analysis report, that showed that system is in waiting state while there is sufficient provision available for user requests execution.

AWR reports showed that high sequential file read. Can you help me to diagnose and resolve this issue.

Regards,
Kaleem Ahmed

[ErmanArslansOracleBlog]

I will try to help you, but first; I need you to send me the hang analyze and that AWR report.

[kahmed92]

AWR Report:
https://drive.google.com/open?id=1wtmM-kiOvSClLpFibiFOo7m8_KfAkzoS
Hang Analysis report:
https://drive.google.com/open?id=1BkVTKb_cbiwnda5gRS3puQ3dHCuwCNY-

[ErmanArslansOracleBlog]

Kaymed92,

You have heavy processes that do lots of db file sequential reads..
Also you have concurrency on them..
 I mean, they wait eachother..
You need to  concantrate on them..
What we see in the AWR is aligned with the Hang analyze.
Concantratre on the AWR.
Concantrate on the section named: SQL ordered by Gets
Start with the sqls generated from the module named BCL_EBS_TMX_INTEGRATION_GIN
this is probably a custom one?

Start with checking and tuning the BCL_EBS_TMX_INTEGRATION_GIN. Then proceed with the other sqls listed in the AWR section named: SQL ordered by Gets..

Also check insert statement coming from e:AR:bes:oracle.apps.ar.applications.CashApp.app..

Here is a note for you ->

AutoInvoice Performance Issue: Poor Performance of AutoInvoice, Issue Inserting Into RA_CUST_TRX_LINE_GL_DIST (Doc ID 470537.1)