Collect Shipment And Booking History - Performance Problem in same MERGE Command - Missing Index (Doc ID 1322837.1)

Last updated on AUGUST 16, 2023

Applies to:

Goal

Customer is running Collect Booking and Shipment in Net Change mode but the process runs forever and gets stuck on the same MERGE command (see line 3502 in the attached tkprof). They created a functional index in the format
(<date column>, 'fmdd') on both oe_order_lines_all and oe_order_lines_all but that did not seem to make a difference.

This issue is similar to <bug 8327905>. Customer in this case created an index on booked date but they asked me the following:

I understand that we have to create Index on date columns of order header and/or order line tables, based on the data streams we choose in Demantra collections. Please confirm if we have to create normal index or functional index
CREATE INDEX <index name> on <order table> (<column name>) or
CREATE INDEX <index name> on <order table> (trunc(<column name>, 'DD'))

I checked with the developer of <bug 8327905> and it was suggested that the customer should create a functional index on the date columns -
CREATE INDEX <index name> on <order table> (trunc(<column name>, 'DD')).

Developer also asked for
1. Exact definition of the indexes they created.
2. What all series are they collecting from the 8 series available in the collect shipment and booking history program.

Reply:
The scripts used to create index are

Please note the date format to be used is 'dd' and not 'fmdd'.  So the customer tried creating indexes with format 'dd' instead of 'fmdd'.  But by default its creating index with 'fmdd'.

Please provide script to create index as mentioned format 'dd'

See research section in bug on the failing sql statements.

Solution

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