SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

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

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

);

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

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

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

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

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;

3 people found this helpful

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

1 person found this helpful

Karthick2003

Sudhir_Meru wrote:

Hi Karthic,

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

Thanks

Sudhir

Your requirement can be done in single INSERT statement as already mentioned by me. Why do you want to split it and make it slow?

Sudhir_Meru

Hi Karthic,

Step 3 is to update CONTRACT_NUMBER which is given in Step 3 In the insert statement you are inserting CONTRACT_NUMBER directly only the SERIAL_NUMBER, MAX(SERVICE_END_DATE) CONTRACT_NUMBER must be inserted. Please suggest.

Thanks

Sudhir

Sudhir_Meru

Thanks Moazzam Tried your method after applying INDEXES as you suggested its fast takes less than a minute to complete the Update Process.

Thanks

Sudhir

unknown-7404

Four suggestions:

1. Write modular code - don't use one procedure (proc 1) to do TWO separate things. Write a CONTROL procedure to manage the work to be done.

That control procedure can then do any appropriate logging/timing to track what happens.

RENEWAL_ASSET_ALL()
    RENEWAL_ASSET_ONE();
    RENEWAL_ASSET_TWO();
    RENEWAL_ASSET_THREE();

2. Add exception handlers and logging to ALL procedures - create/use a custom logging package and insert calls to that package in the procedures.

3. Modify that first procedure to

a. remove the calls to the other procedures so that it does ONE thing.

b. use a simple SQL query to populate the work table - you don't need PL/SQL or collections for that at all

4. Create an actual Global Temporary Table instead of whatever you are using now.

1 - 14

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 30 2014

Added on Apr 1 2014

#cursor, #index, #insert, #procedure, #tuning, #update

14 comments

329 views

SQL & PL/SQL

Procedure taking too much time to complete

Comments

Post Details