After external contractor of the company where I’m working and Oracle Support were not able to find solution that would improve performance of the ReIM consolidated documents functionality, it’s time for me to take that challenge.
Very quickly I’ve found the problematic SQL.
Here I’ll provide only the important part, as the query is over 300 lines.
--part of the original SQL
SELECT CD.DOC_ID,
CD.ITEM,
IM.ITEM_DESC,
CD.CURRENCY_CODE,
OL.UNIT_COST_INIT,
OL.UNIT_COST,
CD.DOC_UNIT_COST,
OL.UNIT_COST - CD.DOC_UNIT_COST VARIANCE,
DECODE (OL.UNIT_COST, 0, 100, ( (OL.UNIT_COST - CD.DOC_UNIT_COST) / OL.UNIT_COST) * 100) VARIANCE_PC,
OS.REF_ITEM,
OL.COST_SOURCE,
SI.VPN,
CD.RESOLUTION_COST,
CD.DEBIT_MEMO_REASON_CODE,
CD.COST_DISCREPANCY_ID
FROM IM_COST_DISCREPANCY CD,
IM_DOC_HEAD DH,
SHIPMENT SH,
SHIPSKU SS,
WH,
V_IM_ORDLOC OL,
ORDSKU OS,
ITEM_MASTER IM,
ITEM_SUPPLIER SI
WHERE DH.DOC_ID = CD.DOC_ID
AND SH.RECEIVE_DATE BETWEEN DH.CONSOLIDATION_START_DATE AND DH.CONSOLIDATION_END_DATE
AND SH.BILL_TO_LOC = DH.LOCATION
AND SS.SHIPMENT = SH.SHIPMENT
AND SS.ITEM = CD.ITEM
AND WH.PHYSICAL_WH = SH.BILL_TO_LOC
AND OL.ORDER_NO = SH.ORDER_NO
AND OL.LOCATION = WH.WH
AND OL.ITEM = CD.ITEM
AND OL.LOC_TYPE = 'W'
AND OS.ORDER_NO = SH.ORDER_NO
AND OS.ITEM = CD.ITEM
AND CD.ITEM = SI.ITEM
AND (:B2 = 'Y'
AND EXISTS
(SELECT 1
FROM SUPS SP
WHERE CD.SUPPLIER = SP.SUPPLIER_PARENT
AND SP.SUPPLIER = SI.SUPPLIER
)
OR CD.SUPPLIER = SI.SUPPLIER
AND :B2 = 'N')
AND CD.ITEM = IM.ITEM
AND CD.DOC_TYPE = 'CONSI'
AND CD.COST_DISCREPANCY_ID = :B1
...
After taking a look at the execution plan, I spot the problematic part (see the picture below).
Looking back at the code, It’s clear that V_IM_ORDLOC view is somehow responsible for full table scan on ORDLOC table, which has many millions of rows.
--view V_IM_ORDLOC
CREATE OR REPLACE FORCE VIEW "RMS"."V_IM_ORDLOC" ("ORDER_NO", "ITEM", "LOCATION", "LOC_TYPE", "QTY_ORDERED", "UNIT_COST", "UNIT_COST_INIT", "COST_SOURCE") AS
SELECT order_no, item,
to_number(SUBSTR (ord_qty_cost, 1, INSTR (ord_qty_cost, '~', 1, 1) - 1))
location,
SUBSTR (ord_qty_cost,
INSTR (ord_qty_cost, '~', 1, 1) + 1,
INSTR (ord_qty_cost, '~', 1, 2)
- INSTR (ord_qty_cost, '~', 1, 1)
- 1
)
loc_type,
to_number(SUBSTR (ord_qty_cost,
INSTR (ord_qty_cost, '~', 1, 2) + 1,
INSTR (ord_qty_cost, '~', 1, 3)
- INSTR (ord_qty_cost, '~', 1, 2)
- 1
))
qty_ordered,
to_number(SUBSTR (ord_qty_cost,
INSTR (ord_qty_cost, '~', 1, 3) + 1,
INSTR (ord_qty_cost, '~', 1, 4)
- INSTR (ord_qty_cost, '~', 1, 3)
- 1
))
unit_cost,
to_number(SUBSTR (ord_qty_cost,
INSTR (ord_qty_cost, '~', 1, 4) + 1,
LENGTH (ord_qty_cost)
))
unit_cost_init,
cost_source
FROM (SELECT ordloc.order_no order_no, ordloc.item item,
(SELECT fn_im_ordloc_unit_cost (order_no,
item,
location,
loc_type,
unit_cost,
qty_ordered,
unit_cost_init,
cost_source
) FROM dual)ord_qty_cost ,
ordloc.cost_source cost_source
FROM ordloc);
As you can see from the view, inside the view there is function call to FN_IM_ORDLOC_UNIT_COST and inside that function there is a cursor.
--FUNCTION FN_IM_ORDLOC_UNIT_COST (only important part)
create or replace FUNCTION FN_IM_ORDLOC_UNIT_COST (
i_order_no ordloc.order_no%TYPE,
i_item ordloc.item%TYPE,
i_location ordloc.location%TYPE,
i_loc_type ordloc.loc_type%TYPE,
i_unit_cost ordloc.unit_cost%TYPE,
i_qty_ordered ordloc.qty_ordered%TYPE,
i_unit_cost_init ordloc.unit_cost_init%TYPE,
i_cost_source ordloc.cost_source%TYPE
)
RETURN VARCHAR2
IS
CURSOR C
IS
SELECT UNIQUE oh.order_no order_no, i_item item,
CASE
WHEN oh.import_id IS NOT NULL
AND oh.import_type IN ('M','X')
THEN oh.import_id
ELSE i_location
END LOCATION,
CASE
WHEN oh.import_id IS NOT NULL
AND oh.import_type IN ('M','X')
THEN 'W'
ELSE i_loc_type
END loc_type,
(i_qty_ordered * pi.pack_qty) qty_ordered,
(i_unit_cost / NVL (pi.pack_qty, 1)) unit_cost,
(i_unit_cost_init / NVL (pi.pack_qty, 1)) unit_cost_init,
i_cost_source cost_source
FROM item_master im, packitem pi, ordhead oh
WHERE im.item = i_item
AND pi.pack_no = i_item
AND oh.order_no = i_order_no
AND im.catch_weight_type = '2'
UNION ALL
SELECT oh.order_no order_no, i_item item,
CASE
WHEN oh.import_id IS NOT NULL AND oh.import_type IN ('M','X')
THEN oh.import_id
ELSE i_location
END LOCATION,
CASE
WHEN oh.import_id IS NOT NULL AND oh.import_type IN ('M','X')
THEN 'W'
ELSE i_loc_type
END loc_type,
(i_qty_ordered * iscd.net_weight / NVL (isc.supp_pack_size, 1)
) qty_ordered,
(i_unit_cost / NVL (iscd.net_weight, 1) * isc.supp_pack_size
) unit_cost,
(i_unit_cost_init / NVL (iscd.net_weight, 1) * isc.supp_pack_size
) unit_cost_init,
i_cost_source cost_source
FROM item_supp_country_dim iscd,
item_supp_country isc,
ordhead oh,
ordsku os,
item_master im
WHERE im.item = i_item
AND im.item = iscd.item
AND im.item = isc.item
AND oh.order_no = i_order_no
AND oh.supplier = isc.supplier
AND os.item = isc.item
AND os.origin_country_id = isc.origin_country_id
AND os.order_no = oh.order_no
AND iscd.supplier = oh.supplier
AND iscd.origin_country = os.origin_country_id
AND iscd.dim_object = 'CA'
AND im.catch_weight_type = '4'
UNION ALL
SELECT oh.order_no order_no, i_item item,
CASE
WHEN oh.import_id IS NOT NULL AND oh.import_type IN ('M','X')
THEN oh.import_id
ELSE i_location
END LOCATION,
CASE
WHEN oh.import_id IS NOT NULL AND oh.import_type IN ('M','X')
THEN 'W'
ELSE i_loc_type
END loc_type,
(i_qty_ordered) qty_ordered, (i_unit_cost) unit_cost,
(i_unit_cost_init) unit_cost_init, i_cost_source cost_source
FROM item_master im, ordhead oh, sups s
WHERE im.item = i_item
AND ( im.catch_weight_type = '3'
OR im.catch_weight_type = '1'
OR im.catch_weight_type IS NULL
)
AND oh.order_no = i_order_no
AND oh.supplier = s.supplier
AND s.sup_qty_level <> 'CA'
UNION ALL
SELECT oh.order_no order_no, i_item item,
CASE
WHEN oh.import_id IS NOT NULL AND oh.import_type IN ('M','X')
THEN oh.import_id
ELSE i_location
END LOCATION,
CASE
WHEN oh.import_id IS NOT NULL AND oh.import_type IN ('M','X')
THEN 'W'
ELSE i_loc_type
END loc_type,
(i_qty_ordered) / os.supp_pack_size qty_ordered,
(i_unit_cost) * os.supp_pack_size unit_cost,
(i_unit_cost_init) * os.supp_pack_size unit_cost_init,
i_cost_source cost_source
FROM item_master im, ordhead oh, ordsku os, sups s
WHERE im.item = i_item
AND ( im.catch_weight_type = '3'
OR im.catch_weight_type = '1'
OR im.catch_weight_type IS NULL
)
AND oh.order_no = i_order_no
AND oh.order_no = os.order_no
AND os.item = i_item
AND oh.supplier = s.supplier
AND sup_qty_level = 'CA';
Although the function has many input parameters, cursor inside the function does not take full advantage of it.
There will also be context switches and Oracle is not able to calculate cardinality correctly.
For all those reasons, Oracle uses FTS on the ORDLOC table.
But how to avoid that?
Slow query basically returns only a couple of rows.
That means, If executed in correct order, the query would have only a few function calls.
I rewrite the query by using query sub factoring, and by controlling the order of execution, I managed to force query to return the results in couple of seconds, where previously the old version of code was running for many hours.
with temp as
(
select /*+ materialize */
ol.unit_cost_init, ol.unit_cost, ol.cost_source, ol.order_no, ol.location, ol.loc_type, ol.qty_ordered,
cd.doc_id,
cd.item,
im.item_desc,
cd.currency_code,
-- ol.unit_cost_init,
-- ol.unit_cost,
cd.doc_unit_cost,
-- ol.unit_cost - cd.doc_unit_cost variance,
-- DECODE(
-- ol.unit_cost,
-- 0,
-- 100,
-- ( (ol.unit_cost - cd.doc_unit_cost) / ol.unit_cost) * 100
-- ) variance_pc,
os.ref_item,
-- ol.cost_source,
si.vpn,
cd.resolution_cost,
cd.debit_memo_reason_code,
cd.cost_discrepancy_id
FROM
im_cost_discrepancy cd,
im_doc_head dh,
shipment sh,
shipsku ss,
wh,
ordloc ol,
-- v_im_ordloc ol,
ordsku os,
item_master im,
item_supplier si
WHERE
dh.doc_id = cd.doc_id
and
-- sh.receive_date BETWEEN dh.consolidation_start_date AND dh.consolidation_end_date
sh.receive_date between dh.consolidation_start_date and to_date(to_char(dh.consolidation_end_date, 'dd.mm.rrrr') || '23:59:59', 'dd.mm.rrrr hh24:mi:ss')
AND
sh.bill_to_loc = dh.location
AND
ss.shipment = sh.shipment
AND
ss.item = cd.item
AND
wh.physical_wh = sh.bill_to_loc
AND
ol.order_no = sh.order_no
AND
ol.location = wh.wh
AND
ol.item = cd.item
AND
ol.loc_type = 'W'
AND
os.order_no = sh.order_no
AND
os.item = cd.item
AND
cd.item = si.item
AND (
'Y' = 'Y'
AND
EXISTS (
SELECT
1
FROM
sups sp
WHERE
cd.supplier = sp.supplier_parent
AND
sp.supplier = si.supplier
)
OR
cd.supplier = si.supplier
AND
'Y' = 'N'
) AND
cd.item = im.item
AND
cd.doc_type = 'CONSI'
and
cd.cost_discrepancy_id =:l_cost_discrepancy_id
UNION
select
ol.unit_cost_init, ol.unit_cost, ol.cost_source, ol.order_no, ol.location, ol.loc_type, ol.qty_ordered,
cd.doc_id,
cd.item,
im.item_desc,
cd.currency_code,
-- ol.unit_cost_init,
-- ol.unit_cost,
cd.doc_unit_cost,
-- ol.unit_cost - cd.doc_unit_cost variance,
-- DECODE(
-- ol.unit_cost,
-- 0,
-- 100,
-- ( (ol.unit_cost - cd.doc_unit_cost) / ol.unit_cost) * 100
-- ) variance_pc,
os.ref_item,
-- ol.cost_source,
si.vpn,
cd.resolution_cost,
cd.debit_memo_reason_code,
cd.cost_discrepancy_id
FROM
im_cost_discrepancy cd,
im_doc_head dh,
shipment sh,
shipsku ss,
ordloc ol,
-- v_im_ordloc ol,
ordsku os,
item_master im,
item_supplier si
WHERE
dh.doc_id = cd.doc_id
and
-- sh.receive_date BETWEEN dh.consolidation_start_date AND dh.consolidation_end_date
sh.receive_date between dh.consolidation_start_date and to_date(to_char(dh.consolidation_end_date, 'dd.mm.rrrr') || '23:59:59', 'dd.mm.rrrr hh24:mi:ss')
AND
sh.bill_to_loc = dh.location
AND
ss.shipment = sh.shipment
AND
ss.item = cd.item
AND
ol.order_no = sh.order_no
AND
ol.location = sh.bill_to_loc
AND
ol.item = cd.item
AND
ol.loc_type = 'S'
AND
os.order_no = sh.order_no
AND
os.item = cd.item
AND
cd.item = si.item
AND (
'Y' = 'Y'
AND
EXISTS (
SELECT
1
FROM
sups sp
WHERE
cd.supplier = sp.supplier_parent
AND
sp.supplier = si.supplier
)
OR
cd.supplier = si.supplier
AND
'Y' = 'N'
) AND
cd.item = im.item
AND
cd.doc_type = 'CONSI'
and
cd.cost_discrepancy_id =:l_cost_discrepancy_id
),
temp2 as
(
select /*+ materialize */ temp.*,
(SELECT fn_im_ordloc_unit_cost (order_no,
item,
location,
loc_type,
unit_cost,
qty_ordered,
unit_cost_init,
cost_source
) from dual)ord_qty_cost
from temp
),
temp3 as
(
select /*+ materialize */
unit_cost_init,
unit_cost,
cost_source,
order_no,
-- location,
to_number(substr (ord_qty_cost, 1, instr (ord_qty_cost, '~', 1, 1) - 1))
as location,
loc_type,
qty_ordered,
--------
doc_id,
item,
item_desc,
currency_code,
-- ol.unit_cost_init,
-- ol.unit_cost,
doc_unit_cost,
-- ol.unit_cost - cd.doc_unit_cost variance,
-- DECODE(
-- ol.unit_cost,
-- 0,
-- 100,
-- ( (ol.unit_cost - cd.doc_unit_cost) / ol.unit_cost) * 100
-- ) variance_pc,
ref_item,
-- ol.cost_source,
vpn,
resolution_cost,
debit_memo_reason_code,
cost_discrepancy_id
from temp2
)
select * from temp3;
As you have see, by understanding what the real issue is, you are on half way to solve it.
In 12c there are improvements related to SQL – PL/SQL engine calls, but this is 11g database and I can’t use those features.
Anyway, this shouldn’t prevent you to solve the problem.
Comments