Skip to Main Content

Procedure taking too much time to complete

Sudhir_MeruApr 1 2014 — edited Apr 2 2014

Hi, 

I am doing a DML action on a table. below are the steps used to perform the action. 

There are two tables IB_SUPPORT_CONTRACTS_MV which has 174435 records and RENEWAL_ASSETS_TEMPS is a Temporary table  

Step 1: Created a Procedure ( I am inserting all the row from IB_SUPPORT_CONTRACTS_MV and Inserting NULL records into below columns )

create or replace PROCEDURE renewal_asset_one AS

  CURSOR s_cur

  IS

SELECT    

    SERIAL_NUMBER,

    NULL SERVICE_START_DATE,

    NULL SERVICE_END_DATE,

    NULL IB_CREATION_DATE,

    NULL AS MIN_SERVICE_START_DATE,

    NULL AS MAX_SERVICE_END_DATE,

    NULL   AS MIN_IB_CREATION_DATE,

    NULL CONTRACT_NUMBER,

    ITEM_DESCRIPTION PRODUCT_ID

    FROM IB_SUPPORT_CONTRACTS_MV

WHERE

LENGTH(SERIAL_NUMBER) > 10 ;

   

TYPE fetch_array

IS

  TABLE OF s_cur%ROWTYPE INDEX BY PLS_INTEGER;

  s_array fetch_array;

BEGIN

 

DELETE FROM RENEWAL_ASSETS_TEMPS;

COMMIT;

  OPEN s_cur;

  

    FETCH s_cur BULK COLLECT INTO s_array;

    FORALL i IN 1..s_array.COUNT

    INSERT INTO RENEWAL_ASSETS_TEMPS VALUES s_array

      (i

      );

   

  CLOSE s_cur;

  COMMIT

RENEWAL_ASSET_TWO();

RENEWAL_ASSET_THREE();

 

END renewal_asset_one;



Step 2:  I am updating table RENEWAL_ASSETS_TEMPS using BULK collect (This procedure is taking lot of time to update)


create or replace PROCEDURE RENEWAL_ASSET_TWO AS

  CURSOR s_cur

  IS

SELECT

    SERIAL_NUMBER,

    MIN(SERVICE_START_DATE) AS MIN_SERVICE_START_DATE,

      MAX(SERVICE_END_DATE) AS MAX_SERVICE_END_DATE,

    MIN(IB_CREATION_DATE)   AS MIN_IB_CREATION_DATE

    FROM IB_SUPPORT_CONTRACTS_MV

    WHERE

    LENGTH(SERIAL_NUMBER) > 10 

    GROUP BY SERIAL_NUMBER ;

   

TYPE fetch_array

IS

  TABLE OF s_cur%ROWTYPE INDEX BY PLS_INTEGER;

  s_array fetch_array;

 

BEGIN

  OPEN s_cur;

  

    FETCH s_cur BULK COLLECT INTO s_array;

    FORALL i IN 1..s_array.COUNT

    

     UPDATE RENEWAL_ASSETS_TEMPS

     SET

     MIN_SERVICE_START_DATE = s_array(i).MIN_SERVICE_START_DATE,

     MAX_SERVICE_END_DATE   = s_array(i).MAX_SERVICE_END_DATE,

     MIN_IB_CREATION_DATE   = s_array(i).MIN_IB_CREATION_DATE

     WHERE

     SERIAL_NUMBER = s_array(i).SERIAL_NUMBER;

   

  CLOSE s_cur;

  COMMIT

 

END;



Step 3: I am doing a final update on same table RENEWAL_ASSETS_TEMPS ( Even this Procedure is taking lot of time to update)


create or replace PROCEDURE RENEWAL_ASSET_THREE AS

CURSOR u_crr

  IS

SELECT

IBV.SERIAL_NUMBER,

RA.MAX_SERVICE_END_DATE,

IBV.CONTRACT_NUMBER

FROM

RENEWAL_ASSETS_TEMPS RA,

IB_SUPPORT_CONTRACTS_MV IBV

WHERE

LENGTH(IBV.SERIAL_NUMBER) > 10 AND

RA.SERIAL_NUMBER = IBV.SERIAL_NUMBER AND

RA.MAX_SERVICE_END_DATE = IBV.SERVICE_END_DATE;

TYPE s_curr

IS

  TABLE OF u_crr%ROWTYPE INDEX BY PLS_INTEGER;

  up_crr s_curr;

BEGIN

  OPEN u_crr;

    FETCH u_crr BULK COLLECT INTO up_crr ;

    FORALL i IN 1..up_crr.count

    UPDATE RENEWAL_ASSETS_TEMPS

    SET CONTRACT_NUMBER        = up_crr(i).CONTRACT_NUMBER

    WHERE SERIAL_NUMBER        = up_crr(i).SERIAL_NUMBER

    AND MAX_SERVICE_END_DATE = up_crr(i).MAX_SERVICE_END_DATE;

  CLOSE u_crr;

  COMMIT;

END;



Please suggest me how to improve performance to update the procedure.


Thanks

Sudhir

Comments
Post Details
Added on Apr 1 2014
14 comments
199 views