Geo Name Referencing Worker Program is Running for a Long Time

(Doc ID 1990415.1)

Last updated on AUGUST 04, 2016

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.

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