Saved Query Performance Degradation for Shipments. (Doc ID 471031.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

-- 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
Saved_query table.

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.

Cause

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