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

Last updated on JULY 12, 2017

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

*** Checked for relevance on 17-Apr-2015 ***


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