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

Last updated on AUGUST 11, 2022

Applies to:

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

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