Slow Performance in Pick Release After Upgrade to RDBMS (11gr2)

(Doc ID 1087099.1)

Last updated on JANUARY 05, 2018

Applies to:

Oracle Shipping Execution - Version to [Release 11.5.10]
Information in this document applies to any platform.
Executable:WSHREL - Pick Release
ConcurrentProgram:WSHPRS - Pick Release - SRS
Report:WSHRDPIK.RDF - Pick Slip Report


After upgrading the RDBMS from to (11gr2), some queries are taking extremely long time to complete.  The most expensive query found is part of the Pick Release process: This is the problematic SQL:

UPDATE wsh_delivery_assignments wda
SET wda.parent_delivery_detail_id = NULL,
wda.delivery_id = NULL
WHERE wda.delivery_id = :b1
AND wda.delivery_id IS NOT NULL
AND wda.delivery_detail_id NOT IN (
SELECT wda1.delivery_detail_id
FROM wsh_delivery_assignments wda1
START WITH wda1.delivery_detail_id IN (
SELECT wda2.delivery_detail_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda2
WHERE wda2.delivery_id = wda.delivery_id
AND wda2.delivery_detail_id =
AND wdd.container_flag = 'N')
CONNECT BY wda1.delivery_detail_id =
PRIOR wda1.parent_delivery_detail_id)


If there are no slowly performing SQL's in the TKPROF file, then verify if the SQL*Net message from client has a long wait time as follows:




Upgrade the RDBMS to


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