12.2.4 - Pycorefx.sql - Performance Issue with hrglobal (Doc ID 1926138.1)

Last updated on JANUARY 13, 2017

Applies to:

Oracle Human Resources - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

On upgrade to Oracle EBS Applications 12.2.4, hrglobal performance is poor.


Steps to Reproduce:


After completing 12.2.4 upgrade (12.1.3 > 12.2.0 > 12.2.4), customer ran datainstall utility to select Global legislation and then applied $PER_TOP/patch/115/hrglobal.drv using adop.

Applying hrglobal driver took around 5 hours in our environment. pycorefx.sql ran for 4.5 hours. Here is the sql that was running for hours:

delete from ff_database_items_tl
  where user_name = t_dbi_del(i).user_name
  and user_entity_id = t_dbi_del(i).user_entity_id;

  

Here is the row count in ff_database_items and ff_databas_items_tl tables in before and after applying hrglobal driver:
Before:
ff_database_items_tl - 26326285
ff_database_items - 1548618

After:
ff_database_items_tl - 11660980
ff_database_items - 685953


WHAT IS WORKING
----------------------------
Customer did not experience this issue in earlier upgrades (12.1.3->12.2.0->12.2.3->12.2.4). This is the 1st time we upgraded to 12.2.4 directly from 12.2.0.
All their earlier 12.2.4 upgrades were done from 12.2.3.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms