Performance of Shipping Purge Process Is Poor Even When Delete Empty Records Is Set to No (Doc ID 2028339.1)

Last updated on JUNE 09, 2017

Applies to:

Oracle Shipping Execution - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

 Shipping Purge program when run for a single sales order is taking up to 30 minutes for the program to complete. The following time
statistic was found in the tkprof output for a single sales order submission.

SELECT wdd.delivery_detail_id
                          FROM   wsh_delivery_details wdd
                          WHERE  wdd.source_code = 'OE'
                          AND    wdd.released_status = 'D'
                          AND    NOT EXISTS
                               ( SELECT 1
                                 FROM   wsh_delivery_details det
                                 WHERE  det.source_code = 'OE'
                                 AND    det.source_line_id =
wdd.source_line_id
                                 AND    det.released_status <> 'D' )
                          AND    NOT EXISTS
                               ( SELECT 1
                                 FROM   oe_order_headers_all oeh
                                 WHERE  oeh.header_id = wdd.source_header_id
)
  AND wdd.creation_date  < ''

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