Procedure taking too much time to complete

Sudhir_Meru

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

Sujoy

Hi Sudhir,

For a start, you can generate the explain plan for the individual SQL statements - SELECT, UPDATE etc - and modify them for improvements.

Regards,

Sujoy

Pablolee

Well, without looking at it in too much depth, you are deleting all rows in RENEWAL_ASSETS_TEMPS so instead of doing a delete, do a truncate on that table.

Instead of using an explicit cursor, just do the insert in SQL:

insert into renewal_asset_temps 
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

Use similar techniques in the rest of your code i.e. use plain SQL instead of looping through cursors.

Karthick2003

All the 3 procedure can be converted into one INSERT statement like this.

insert 
  into renewal_assets_temps
       (
           serial_number
         , service_start_date
         , service_end_date
         , ib_creation_date
         , min_service_start_date
         , max_service_end_date
         , min_ib_creation_date
         , contract_number
         , product_id
       )
select serial_number
     , service_start_date
     , service_end_date
     , ib_creation_date
     , min(service_start_date) over (partition by serial_number)
     , max(service_end_date)   over (partition by serial_number)
     , min(ib_creation_date)   over (partition by serial_number)
     , contract_number
     , item_description
  from ib_support_contracts_mv
 where length(serial_number) > 10;

Sudhir_Meru

Hi Karthic,

  Thanks for your reply. Step 2 Procedure worked Perfect how do I do Step 3 Procedure Please suggest

Thanks

Sudhir

Cyrille MODIANO

    UPDATE RENEWAL_ASSETS_TEMPS

    SET CONTRACT_NUMBER        = (SELECT 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)

  WHERE SERIAL_NUMBER        = (SELECT IBV.SERIAL_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)

  AND MAX_SERVICE_END_DATE = (SELECT RA.MAX_SERVICE_END_DATE

                                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);

  COMMIT;

If I'm not wrong this should be the same thing, but i'm not sure this one is faster tham the original one, try it.

Cyrille

http://www.dba-scripts.com

Sudhir_Meru

Hi Cyrillie,

  Thanks for your reply your Update Statement is returning error with more than one value to SET. Please suggest me how to modify.,

Thanks
Sudhir

Cyrille MODIANO

Hi Sudhir,

sorry my first query is completly wrong, I,m not an expert is SQL or PL/SQL but I think this is the equivalent :

    UPDATE RENEWAL_ASSETS_TEMPS RA

    SET CONTRACT_NUMBER        = (SELECT IBV.CONTRACT_NUMBER

  FROM

  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);

Cyrille

Sudhir_Meru

Thank Cyrille for your reply. This query you pinged me is taking too much time to update. Need some experts to suggest how to tune the query

Thanks

Sudhir

Sudhir_Meru

Hi Karthic,

  Can we add Procedure 3 update in you insert statement. Please suggest

Thanks

Sudhir

Moazzam

Try the below one:

update RENEWAL_ASSETS_TEMPS RA
set RA.CONTRACT_NUMBER in ( select distinct CONTRACT_NUMBER
                        from 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.MAX_SERVICE_END_DATE)

Also ensure that an index exists in IB_SUPPORT_CONTRACTS_MV table on SERIAL_NUMBER column. Also there should be index on RENEWAL_ASSETS_TEMPS for (SERIAL_NUMBER, MAX_SERVICE_END_DATE) columns