Geo Name Referencing Worker Program is Running for a Long Time

(Doc ID 1990415.1)

Last updated on JANUARY 31, 2018

Applies to:

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

Symptoms

You are running the Geo Name Referencing Worker 10 (Geography Name Referencing Worker) program and it is running a long time in one of the R12 upgraded instances.
You have enabled the Geography validation structure and ran this program.

The Parent program 'Geography Name Referencing Program' completed immediately, but the child program is the one running long hours.

This program will run for total 1,244,200 LOCATION_IDs in HZ_LOCATIONS table.

The trace file shows the SQL to be running long.
This SQL executed for 41990 times and took 8278.05 sec for completed.
This is for one request.

Also observed INDEX FAST FULL SCAN on index HZ_GEOGRAPHY_IDENTIFIERS_N2 on table AR.HZ_GEOGRAPHY_IDENTIFIERS.
So, you created a functional based index on table AR.HZ_GEOGRAPHY_IDENTIFIERS with column (GEOGRAPHY_USE,GEOGRAPHY_TYPE,GEOGRAPHY_ID,UPPER(IDENTIFIER_VALUE)).
This has 46% improvement, 30 hours to 16 hours. As outage window is 24 hours, so within 16 hours it will complete as per current testing.

Changes

 

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