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.



Get notified when a new post is published!

 

Loading

Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.