Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Procedure taking too much time to complete

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
Answers
-
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
-
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.
-
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;
-
Hi Karthic,
Thanks for your reply. Step 2 Procedure worked Perfect how do I do Step 3 Procedure Please suggest
Thanks
Sudhir
-
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
-
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 -
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
-
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
-
Hi Karthic,
Can we add Procedure 3 update in you insert statement. Please suggest
Thanks
Sudhir
-
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