Pick List Generation Program Runs for a Long Time unless Statistics Gathered on WSH_DELIVERY_DETAILS (Doc ID 2535238.1)

Last updated on JULY 09, 2021

Applies to:

Symptoms

On :  12.2.4 version, Pick Release

ACTUAL BEHAVIOR  
---------------
Unless it has been loaded into memory, or unless statistics have been gathered on wsh_delivery_details,  the following SQL statement runs for a very long time

EXPECTED BEHAVIOR
-----------------------
Expect this statement to be executed in less than 1 second.   If it has been loaded in memory, or just after gathering status on wsh_delivery_details, it is executed in less than one second

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. User runs Pick Release as soon as he logs into Oracle Applications, and does NOT select any organization
2. Pick release runs for a very long time, it appears to hang
3. Customer can recover by either selecting an organization using Change Organization, or by gathering statistics on wsh_delivery_details
4. But if another user tries to run pick release without selecting an organization, same issue occurs

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users are forced to kill the process, gather statistics on wsh_delivery_details, then re-run Pick Release.

Cause

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