Shipping Purge Performance Is Not Acceptable (Doc ID 2690279.1)

Last updated on SEPTEMBER 18, 2023

Applies to:

Symptoms

On : 12.2.7 version, Performance Issues

ACTUAL BEHAVIOR
---------------
Shipping purge performance is not acceptable as purging 750 records took 3 hours. This is the first time the Customer has attempted the Shipping Purge.


EXPENSIVE SQL:
-----------------------
WITH trip_inner_query AS
(SELECT /*+ opt_param('_optimizer_cost_based_transformation','off') opt_param('_optimizer_use_feedback','false') */
inner.trip_id trip_id,
COUNT ( inner.dd_id ) dd_cnt
FROM
wsh_purge_set_v inner WHERE
inner.del_ship_from_org = 0AND 1 = 1 AND inner.dd_source_code <> 'OKE' AND inner.trip_status IN ('CL') AND DECODE(inner.dd_source_code,
'OE',(DECODE((SELECT count(oe.order_number)
FROM oe_order_headers_all oe
WHERE oe.header_id= inner.dd_source_header_id),0,'FALSE','TRUE')),
'PO',( DECODE((SELECT count(po.po_header_id)
FROM po_headers_all po
WHERE po.po_header_id= inner.dd_source_header_id),0,'FALSE','TRUE')),
'RTV',(Decode(inner.dd_po_shipment_line_id,NULL,
decode((select count(mmt.transaction_id)
from mtl_material_transactions mmt
WHERE mmt.picking_line_id = inner.dd_id
),0,'FALSE','TRUE'
),
decode((select count(rt.interface_source_line_id)
from rcv_transactions rt
WHERE rt.interface_source_line_id= inner.dd_id
),0,'FALSE','TRUE'
)
)
),
'FALSE') = 'FALSE'
AND NOT EXISTS
(SELECT 1 FROM
wsh_delivery_details dd
WHERE
dd.delivery_detail_id = inner.dd_id AND
(
Nvl(dd.released_status,'N') NOT IN ('C','L') OR
dd.oe_interfaced_flag IN ('N','P') OR
dd.inv_interfaced_flag IN ('N','P')
)
) GROUP BY inner.trip_id) SELECT outer.trip_id , outer.trip_name , 'NON_EMPTY' purge_set_type
FROM wsh_purge_set_v outer ,trip_inner_query
where outer.trip_id = trip_inner_query.trip_id
GROUP BY outer.trip_id , outer.trip_name
HAVING count ( outer.dd_id ) = (select dd_cnt from trip_inner_query where trip_id = outer.trip_id)

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Order Management Responsibility
2. Shipping
3. Purge SRS

Cause

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