EBS 12.2 19C DB environments -- Suggestions for some issues (including performance related ones) / based on a real life story

In my previous blog posts, I made some suggestions for dealing with problematic discoverer reports, which may have performance issues especially after 19C database upgrades. In fact, we recently worked in a production EBS environment where there were some serious performance problems.(performance problem after the upgrade.) 

I was like a post-upgrade consultant in that project and with the intense work we have done, we have made the environment work with acceptable performance in a short time.

I continue to share some of the problems we encountered in this type of a consultant work and the solutions or workarounds that have been implemented (in a short time and in pressure) to make the system acceptable in terms of functionality, continuity and performance .

Today I want to touch on 3 specific issues .. (which I also posted on my twitter account recently)

*Issue : We also saw occasional instance terminations in EBS 12.2 19C environments. Espically under high load.. Solution -> Patch 29838337: XDB STRESS TEST - HIT ORA 600 [KJBLREPLAY: DUP] (latest RU can also be considered)

*Issue: We saw continious updates on AR_TRX_BAL_SUMMAR_MAIN_SUM ... This updates were increase the load of the database nodes dramatically.. (although, the database was running on Exadata)

UPDATE AR_TRX_BAL_SUMMARY MAIN_SUM SET RECEIPTS_AT_RISK ...
Those looped updates place a serious load on the system.

So here is the recommendation; If you don't use Credit Management and Advanced Collections applications, these updates may be disabled.. See MOS note - 2445550.1

*Issue : Reverse Journals were taking a lot of time to complete..This was related with Reverse Journals/GLPREV - performance and we created a custom index on GL_JE_HEADERS for that..

Index ON GL_JE_HEADERS (ACCRUAL_REV_JE_HEADER_ID)

We have seen the benefit... Index on (ACCRUAL_REV_PERIOD_NAME, LEDGER_ID) -> can also increase the perf. of automatic reversal.. (also see Patch 29464680) 

-----------------------------------------------

Below, I  share the solution methods for different problems experienced in this environment, as well.

*In case you need to manage the database load manually ; read the following blog post -> 

http://ermanarslan.blogspot.com/2021/01/ebs-122-configuring-application.html

*Read the following blog post for Discoverer - related quick win that we have implemented in this environment-> 

http://ermanarslan.blogspot.com/2020/12/ebs-122-rdbms-optimizerfeaturesenable.html

That 's it for today :)  I hope, this will be helpful to you.