Saved Query Performance Degradation for Shipments.
Last updated on MARCH 01, 2018
Applies to:Oracle Transportation Management - Version: 126.96.36.199
This problem can occur on any platform.
-- Problem Statement:
When running saved queries on the Shipment UI, it is taking 10 minutes for the data to return. If you do not use a saved query, the data returns in seconds.
-- Steps To Reproduce:
Generate save query based on shipment with following criteria. Below is the SQL it saves to the
select sh.shipment_gid from SHIPMENT sh, SHIPMENT_TYPE stp, LOCATION ls where
(sh.source_location_gid=ls.location_gid) and (sh.start_time between trunc(TO_DATE('2007-11-06
04:00:00','YYYY-MM-DD HH24:MI:SS') + 0, 'MI') and trunc(TO_DATE('2007-11-06 04:00:00','YYYY-MM-DDHH24:MI:SS') + 5, 'MI')+0.99999) and (sh.shipment_gid in (select ss1.shipment_gid from SHIPMENT_STATUS ss1, STATUS_VALUE sv1 where (ss1.status_value_gid=sv1.status_value_gid) and
(sv1.status_value_xid in ('SECURE RESOURCES_DECLINED','SECURE RESOURCES_NO RESOURCES','SECURE RESOURCES_NO RESPONSE','SECURE RESOURCES_NOT STARTED','SECURE RESOURCES_TENDERED','SECURE RESOURCES_WITHDRAWN')))) and (sh.perspective='B') and (sh.shipment_type_gid=stp.shipment_type_gid) and (stp.shipment_type_xid!='NON FREIGHT RELATED CHARGE') and (ls.location_xid like '938581%')
-- Business Impact:
Planners/Auditors are unable to perform their duties.
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