EBusiness Suite RUP 12.2.10 Performance Issue

In the Q1 of 2022, I completed a major financial administration software upgrade involving Oracle EBS. As part of the project, EBS was upgraded to its latest version(R12.2.10). The project kicked off sometime in May 2021 and took close to a year. While there were numerous challenges during the project, there were two specific issues that I encountered and would like to talk about in this blog.

1. Batch job performance
Weekly reporting jobs(comprised of request sets that are scheduled from “AR Super User”) took 2x time compared to production

2. RUP 12.2.10 time was unpredictable
I remember while applying RUP 12.2.10(Patch 30399999), the patch completed but cutover(adworker was hanging) was running for several hours. The issue did not replicate in lower environments. I found below SQL from the trace, but not much information why it was hanging

HM: Current SQL: SELECT RIDX.OWNER OWNER , RIDX.INDEX_NAME REVISED_INDEX , RIDX.TABLE_OWNER TABLE_OWNER , RIDX.TABLE_NAME TABLE_NAME , OIDX.INDEX_NAME ORIGINAL_INDEX , CON.CONSTRAINT_NAME CONSTRAINT_NAME FROM DBA_INDEXES RIDX , DBA_INDEXES OIDX , DBA_CONSTRAINTS CON WHERE RIDX.OWNER IN ( SELECT ORACLE_USERNAME FROM SYSTEM.FND_ORACLE_US

*** 2022–02–25T13:00:50.368736–05:00 (CDB$ROOT(1))
HM: Early Warning — Session ID 1384 serial# 60363 OS PID 184855 (FG)
is waiting on ‘PX Deq: Parse Reply’ for 32 seconds, wait id 1190
p1: ‘sleeptime/senderid’=0x4f, p2: ‘passes’=0x4f, p3: ‘’=0x0
Final Blocker is Session ID 3723 serial# 37449 on instance 1
which is waiting on ‘PX Deq: Execution Msg’ for 32 seconds, wait id 41
p1: ‘sleeptime/senderid’=0x1001ffff, p2: ‘passes’=0x4f, p3: ‘’=0x16be05c630

HM: Dumping Short Stack of pid[160.184855] (sid:1384, ser#:60363)
Short stack dump:
ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-semtimedop()+10<-skgpwwait()+187<-ksliwat()+2293<-kslwaitctx()+200<-kxfpqidqr()+2478<-kxfpqdqr()+355<-kxfxgs()+71<-kxfxcp()+1904<-qerpxSendParse()+1286<-kxfpValidateSlaveGroup()+231<-kxfpgsg()+4604<-kxfrAllocSlaves()+319<-kxfrialo()+2874<-kxfralo()+450<-qerpxSetupAndAllocSlaves()+495<-qerpx_rowsrc_start()+1780<-qerpxStart()+541<-qerjoStart()+689<-qerflStart()+1133<-rwsstd()+295<-qeruaStart()+166<-qersoStart()+3308<-selexe0()+979<-opiexe()+6990<-opipls()+2427<-opiodr()+1202<-rpidrus()+198<-skgmstack()+65<-rpidru()+132<-rpiswu2()+543<-rpidrv()+1266<-psddr0()+467<-psdnal()+624<-pevm_EXECC()+306<-pfrinstr_EXECC()+56<-pfrrun_no_tool()+60<-pfrrun()+902<-plsql_run()+752<-peicnt()+279<-kkxexe()+720<-opiexe()+24859<-opiall0()+1382<-opikpr()+595<-opiodr()+1202<-rpidrus()+198<-skgmstack()+65<-rpidru()+132<-rpiswu2()+543<-kprball()+1046<-kkxspdbswitch_cbk()+460<-kkxsexr()+279<-kkxsexe()+661<-pevm_icd_call_common()+835<-pfrinstr_ICAL()+128<-pfrrun_no_tool()+60<-pfrrun()+902<-plsql_run()+752<-peicnt()+279<-kkxexe()+720<-opiexe()+24859<-kpoal8()+2226<-opiodr()+1202<-ttcpip()+1246<-opitsk()+1900<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+245

After some research & multiple iterations, I was able to pin down the issue.

Due to Online Patching Enablement and the underlying Edition-Based Redefinition, there are changes to fixed and dictionary objects. As a result, internal SQL in Online Patching Enablement may at time run longer. Gathering statistics for specific fixed or dictionary objects can help in these cases, particularly on editioning objects.

As soon as I ran the below, within mins the workers completed.

SQL> alter session set container=PROD;
SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> exec dbms_stats.gather_dictionary_stats;

Takeaway #1. To ensure good performance, run gather dictionary statistics after completing major upgrade(EBS & Database). It is recommended to run gather dictionary statistics both before and after upgrade, because Data Dictionary tables are modified and created during the upgrade

https://docs.oracle.com/en/database/oracle/oracle-database/19/spuss/regathering-fixed-objects-statistics.html#GUID-44B9B605-8FFE-4FFD-89A0-105CE4C4939E

Takeaway#2. Fixed object stats should be taken. Fixed object and dictionary statistics are gathered at the end of Online Patching Enablement (in script ADZDSTATS.sql). However, they are not gathered automatically at any other points during the Release 12.2 upgrade. DBA should ensure that the statistics are re-gathered during peak hours or right after the upgrade.

Therefore after Go Live, I performed below additional steps before releasing to end users.

$ sqlplus “/ as sysdba”
SQL>alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus “/ as sysdba”
SQL>alter system disable restricted session;
SQL>exit;