How to improve the pl-sql Procedure code, 2 hours for 100k records

Question

How to improve the performance on below code? It takes 2 hours for 100k records.

create or replace PROCEDURE APO_RMS_E_PRIO_ITEM_REPL_P
    AS
       O_error_message  VARCHAR2(2000);
       L_program        VARCHAR2(100):= 'APO_RMS_E_PRIO_ITEM_REPL_P';
       L_SOH NUMBER(20,4);
       L_PRIO_ITEM_SOH NUMBER(20,4);
       L_SUM_SOH NUMBER(20,4);
       L_ITEM VARCHAR2(25);
       L_MIN_STOCK NUMBER(20,4);
       L_MAX_STOCK NUMBER(20,4);
       L_ORDER_ROQ NUMBER(20,4);

    --cursor to get all prio items............
    CURSOR CUR_1 IS
    SELECT OT.LOCATION,
           OT.ITEM
      FROM ITEM_MASTER IM,
           ORD_TEMP OT
     WHERE IM.ITEM = OT.ITEM
       AND EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 115 AND RUV.UDA_ITEM = IM.ITEM)
       AND EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 116 AND RUV.UDA_ITEM = IM.ITEM)
       AND EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 117 AND RUV.UDA_ITEM = IM.ITEM)
       AND EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 163 AND RUV.UDA_LOV_VALUE != 0 AND RUV.UDA_ITEM = IM.ITEM)
       AND NOT EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 122 AND RUV.UDA_ITEM = IM.ITEM)
       AND NOT EXISTS (SELECT 1 FROM RPM_UDA_VIEW RUV WHERE RUV.UDA_ID = 123 AND RUV.UDA_ITEM = IM.ITEM);

    --cursor to get all items for the prio item group........
    cursor cur_2 (l_item varchar2) is
    WITH RUV AS
    (
    SELECT UDA_ID, UDA_ITEM, UDA_LOV_VALUE FROM RPM_UDA_VIEW
    ),
    ARIIS as
    (
    SELECT * FROM APO_RMS_I_ITEM_STG
    )
    SELECT ITEM
      FROM ITEM_MASTER IM
       WHERE EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 115 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
       AND EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 116 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
       AND EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 117 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
       AND EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 163 AND RUV2.UDA_LOV_VALUE != 0 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_ID = RUV2.UDA_ID)
       AND NOT EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 122 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
       AND NOT EXISTS (SELECT 1 FROM RUV RUV1, RUV RUV2 WHERE RUV2.UDA_ITEM = L_ITEM AND RUV2.UDA_ID = 123 AND RUV1.UDA_ITEM = IM.ITEM AND RUV1.UDA_LOV_VALUE = RUV2.UDA_LOV_VALUE AND RUV1.UDA_ID = RUV2.UDA_ID)
       AND IM.ITEM IN (SELECT ITEM_PARENT FROM ITEM_MASTER WHERE ITEM IN (
    (
    (SELECT A.VARNUMMER
     FROM APO_RMS_I_ITEM_STG A
     WHERE a.date_received =
    (SELECT MAX(a1.date_received)
    FROM apo_rms_i_item_stg a1
    WHERE a1.varuid=a.varuid
    )
     AND a.ANTAL_NUMERISK *NVL(a.ANTAL_MULTIPEL_1, 1)*NVL(a.ANTAL_MULTIPEL_2, 1) BETWEEN
    (SELECT MAX(L.ANTAL_NUMERISK*NVL(L.ANTAL_MULTIPEL_1, 1)*NVL(L.ANTAL_MULTIPEL_2, 1)*0.88)
    FROM APO_RMS_I_ITEM_STG L
    WHERE L.date_received =
      (SELECT MAX(L1.date_received)
      FROM apo_rms_i_item_stg L1
      WHERE L1.varuid=L.varuid
      )
    AND L.VARNUMMER =(select item from item_master where ITEM_PARENT=L_ITEM and PRIMARY_REF_ITEM_IND='Y')
    )
     AND (SELECT MAX(H.ANTAL_NUMERISK*NVL(H.ANTAL_MULTIPEL_1, 1)*NVL(H.ANTAL_MULTIPEL_2, 1)*1.12)
    FROM APO_RMS_I_ITEM_STG H
    WHERE H.date_received =
      (SELECT MAX(H1.date_received)
      FROM apo_rms_i_item_stg H1
      WHERE H1.varuid=H.varuid
      )
    AND H.VARNUMMER=(select item from item_master where ITEM_PARENT=L_ITEM and PRIMARY_REF_ITEM_IND='Y'))
    AND IM.ITEM NOT IN
      (SELECT I.uda_ITEM FROM rpm_uda_view I WHERE I.UDA_ID in (122,123)
      )
    ))
    ));

    BEGIN

    FOR REC_1 IN CUR_1
    LOOP

    L_SUM_SOH := 0;

    FOR REC_2 IN CUR_2(REC_1.ITEM)

    LOOP

    --get stock_on_hand for each item in prio group.....
    SELECT IL.STOCK_ON_HAND
      INTO L_PRIO_ITEM_SOH
      FROM ITEM_LOC_SOH IL
     WHERE IL.LOC = REC_1.LOCATION
       AND IL.ITEM = REC_1.ITEM;

    --get total available stock for each item in prio group.....
    SELECT (CASE WHEN AVAILABLE_SOH < 0 THEN 0 ELSE AVAILABLE_SOH END) INTO L_SOH FROM
    (
    SELECT
         SUM(
         (NVL(ILS.TSF_EXPECTED_QTY, 0) + NVL(ILS.IN_TRANSIT_QTY, 0) + NVL(ILS.STOCK_ON_HAND, 0) + NVL(ON_ORDER_QTY, 0))
         -
         (NVL(ILS.TSF_RESERVED_QTY, 0) + NVL(ILS.RTV_QTY, 0) + NVL(ILS.NON_SELLABLE_QTY, 0) + NVL(ILS.CUSTOMER_RESV, 0))
         ) AVAILABLE_SOH
    FROM ITEM_LOC_SOH ILS,
         (SELECT L.ITEM,
                 NVL(SUM(L.QTY_ORDERED - NVL(L.QTY_RECEIVED, 0)), 0) ON_ORDER_QTY
            FROM ORDHEAD H,
                 ORDLOC L
           WHERE L.ITEM = REC_2.ITEM
             AND L.LOCATION = REC_1.LOCATION
             AND L.LOC_TYPE ='S'
             AND L.ORDER_NO = H.ORDER_NO
             AND H.STATUS = 'A'
             AND L.QTY_ORDERED > NVL(L.QTY_RECEIVED, 0)
             AND H.ORDER_TYPE != 'CO'
        GROUP BY L.ITEM) ORD
    WHERE ILS.ITEM = ORD.ITEM(+)
      AND ILS.ITEM = REC_2.ITEM
      AND ILS.LOC = REC_1.LOCATION);

    L_SUM_SOH := L_SUM_SOH + L_SOH;

    END LOOP;

    --get min max for the prio item....
    SELECT RL.MIN_STOCK,
           RL.MAX_STOCK
      INTO L_MIN_STOCK,
           L_MAX_STOCK
      FROM REPL_ITEM_LOC RL
     WHERE RL.LOCATION = REC_1.LOCATION
       AND RL.ITEM = REC_1.ITEM;

    IF (L_SUM_SOH < L_MIN_STOCK)

    Then

    SELECT OT.QTY_ORDERED
      INTO L_ORDER_ROQ
      FROM RMS13PRD.ORD_TEMP OT
     WHERE OT.LOCATION = REC_1.LOCATION
       AND OT.ITEM = REC_1.ITEM;

    INSERT INTO APO_RMS_E_PRIO_ITEM
     SELECT ORD_TEMP_SEQ_NO,
            CROSSDOCK_IND,
            CROSSDOCK_STORE,
            DEPT,
            SUPPLIER,
            ITEM,
            PACK_IND,
            ORIGIN_COUNTRY_ID,
            UNIT_COST,
            PICKUP_LEAD_TIME,
            SUPP_LEAD_TIME,
            LOCATION,
            LOC_TYPE,
            L_ORDER_ROQ,
            SUPP_PACK_SIZE,
            ORDER_STATUS,
            CONTRACT_NO,
            CONTRACT_TYPE,
            DUE_IND,
            L_SUM_SOH,
            L_PRIO_ITEM_SOH,
            SYSDATE,
            'update_prio_item'
        FROM RMS13PRD.ORD_TEMP OT
       WHERE OT.LOCATION = REC_1.LOCATION
         AND OT.ITEM = REC_1.ITEM;

    COMMIT;

    UPDATE ORD_TEMP OT
       SET OT.QTY_ORDERED = NVL(L_MAX_STOCK, 0) - NVL(L_SUM_SOH, 0)
     WHERE OT.LOCATION = REC_1.LOCATION
       AND OT.ITEM = REC_1.ITEM;

    COMMIT;

    ELSE

    INSERT INTO APO_RMS_E_PRIO_ITEM
     SELECT ORD_TEMP_SEQ_NO,
            CROSSDOCK_IND,
            CROSSDOCK_STORE,
            DEPT,
            SUPPLIER,
            ITEM,
            PACK_IND,
            ORIGIN_COUNTRY_ID,
            UNIT_COST,
            PICKUP_LEAD_TIME,
            SUPP_LEAD_TIME,
            LOCATION,
            LOC_TYPE,
            QTY_ORDERED,
            SUPP_PACK_SIZE,
            ORDER_STATUS,
            CONTRACT_NO,
            CONTRACT_TYPE,
            DUE_IND,
            L_SUM_SOH,
            L_PRIO_ITEM_SOH,
            SYSDATE,
            'delete_prio_item'
       FROM RMS13PRD.ORD_TEMP OT
      WHERE OT.LOCATION = REC_1.LOCATION
        AND OT.ITEM = REC_1.ITEM;

    COMMIT;

    DELETE FROM RMS13PRD.ORD_TEMP OT
     WHERE OT.LOCATION = REC_1.LOCATION
       AND OT.ITEM = REC_1.ITEM;

    COMMIT;

    END IF;

    END LOOP;

    EXCEPTION
       WHEN OTHERS THEN
       ROLLBACK;
       O_error_message := L_program||'-'||SQLCODE||SQLERRM;

    END APO_RMS_E_PRIO_ITEM_REPL_P;

I'm quite sure there is, but you need to include the tables, indexes, amount of data and sample data to begin with... — James Z, Sep 27 '18 at 22:44

score 1 · Answer 1 · edited Sep 30 '18 at 16:48

When I need to find which part of a procedure takes (too much) time to execute, I use a simple logging technique: a table which contains info about where I am and what am I doing along with a timestamp. It is populated through an AUTONOMOUS_TRANSACTION procedure (so that I can commit log info, without affecting the caller).

Here's the script (feel free to modify it):

CREATE TABLE a1_log
(
   id      NUMBER,
   datum   DATE,
   descr   VARCHAR2 (500)
);

CREATE SEQUENCE seqlognap START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE a1_p_log (par_descr IN VARCHAR2)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO a1_log
      SELECT seqlognap.NEXTVAL, SYSDATE, par_descr FROM DUAL;

   COMMIT;
END a1_p_log;

Then, in a procedure I'm working on, put a1_p_log calls, such as

begin
  a1_p_log('step 1: initializing');
  -- some init code here

  a1_p_log('step 2: an ugly query follows now');
  SELECT blabla --> very ugly query

  a1_p_log('step 3: updating xyz table');
  UPDATE xyz set ...

  a1_p_log('step 4: finish');
end;

That procedure can take some time to finish (minutes, hours ...), but I can query the a1_log table any time:

  SELECT id,
         datum,
         LEAD (datum) OVER (ORDER BY id) datum_next,
         (LEAD (datum) OVER (ORDER BY id) - datum) * 24 * 60 * 60 diff_sec,
         descr
    FROM a1_log
ORDER BY id;

Pay attention to DIFF_SEC value, which shows number of seconds between two steps. If you notice a large number here, well, you should try to optimize that query in order to make it run faster.

As of your code: too complex for me to say anything smart, except: you'd probably want to remove all those COMMIT calls from the loop. Use one COMMIT, at the end of the procedure.

score 0 · Answer 2 · answered Sep 28 '18 at 05:19

A solution would be to ask your DBA is they have a tool to identify which queries are fired during your procedure, how many times they take on average, and how many time for each one.

Then this will point to which queries in your procedure need to be optimized, and a relevant DBA will have some clues about it.

Here we sometimes need to use the reports generated by Automatic Workload Repository (AWR) from Oracle which gives you extensive statistics on what happens on your database. So if you manage to stop all other processing during your test phase, you will have clear view of what happens inside your procedure, without too much work for you.

(notice that I am not linked to Oracle in any "corporate" way).

can we put this into Pro*C and multi-thread based on Location, to improve the performance of the code — user81157, Oct 04 '18 at 08:00

How to improve the pl-sql Procedure code, 2 hours for 100k records

2 Answers2