My Oracle Support Banner

Upgrade from 12.1.3 to 12.2.9, AD_ZD_TABLE_APPLY.sql Script Stuck on GMF Tables (Doc ID 2634643.1)

Last updated on JANUARY 31, 2020

Applies to:

Oracle Process Manufacturing Financials - Version 12.2.9 and later
Information in this document applies to any platform.

Symptoms

EBS upgrade to R12.2.9 seems to be stuck on AD_ZD_TABLE_APPLY.sql

During upgrade from EBS R12.1.3 to R12.2.9 we are installing <patch 28840850> in downtime mode as requested in Doc ID 2495027.1 - Path A.
After running for some hours, the patch installation seems to hang on the AD_ZD_TABLE_APPLY.sql script. We can see that quite a number of DBMS_SCHEDULER sessions are being spawned and each one seems to be performing the same update:

UPDATE /*+ rowid (tbl) */
  GMF.CM_ACST_LED tbl
  SET WHSE_CODE = WHSE_CODE
 WHERE ROWID BETWEEN :start_id AND :end_id
 
So far it has been stuck on this step for more than 6 hours. The server CPU is at 100% so even if I increase the job_queue_processes parameter to allow for more parallel DBMS_SCHEDULER sessions I will not get any benefit.


Here the problem is that in our case there are some tables being updated in this task, specifically those from the GMF schema, that are huge: like 500 to 700 milion rows.

Updating these tables using this query and the DBMS_SCHEDULER jobs takes an extremely long amount of time.

UPDATE /*+ rowid (tbl) */
GMF.CM_ACST_LED tbl
SET WHSE_CODE = WHSE_CODE
WHERE ROWID BETWEEN :start_id AND :end_id

AWR report shows that most of the top sql's by elapsed time are updates to GMF tables in which a column value is being set to itself, such as the above example of cm_acst_led, next one is:
update /*+ rowid (tbl) */ GMF.GMF_TRANSACTION_VALUATION tbl set PROGRAM_UPDATE_DATE=PROGRAM_UPDATE_DATE where rowid between :start_id and :end_id
etc.

Asked the customer to disable the jobs as per :
Note 1581549.1 - Best Practices for Minimizing Oracle E-Business Suite Release 12.1.3 and 12.2.n Upgrade Downtime (Doc ID 1581549.1)
7.4.1 Disable all DBMS scheduler, DBMS Job and Autotask activities

He did this, but still facing this performance issue.



Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.