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

Last updated on JULY 12, 2017

Applies to:

Oracle Shipping Execution - Version 11.5.10.0 to 11.5.10.2 [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 ***


Symptoms

After upgrading the RDBMS from 10.2.0.4 to 11.2.0.1 (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 =
wdd.delivery_detail_id
AND wdd.container_flag = 'N')
CONNECT BY wda1.delivery_detail_id =
PRIOR wda1.parent_delivery_detail_id)

 

Addendum:
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:

 

 

Changes

Upgrade the RDBMS to  11.2.0.1.0

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