it is very useful but as it is daily job please provide some more tips to improve the performance
Srinadh Tummala, April 07, 2017 - 10:19 am UTC
Many thanks for the quick answer.
also need more tips to tune this as it is a daily job and also tables get increased.
Thanks,
Srinadh
April 08, 2017 - 5:16 am UTC
Let's see some code and execution timings for
1) before you made the changes (ie original version)
2) our suggested changes
so we can see the scope of current improvements
Tested for one table and it has 144423 records
Srinadh Tummala, April 07, 2017 - 12:23 pm UTC
It's running for more than 1 and 1/2 half hour and it goes on.
could you please suggest any other approach for bulk update.
OPEN V_cursor FOR V_SQL_STRING;
V_SQL_STRING := ' UPDATE ' || REC_TAB.TABLE_NAME ||
' SET C4= :1
WHERE C1= :2
AND C2= :3
AND C3= :4 ';
LOOP
FETCH v_CURSOR BULK COLLECT
INTO DATA_COLL LIMIT 10000;
EXIT WHEN V_CURSOR%NOTFOUND;
FORALL I IN 1..DATA_COLL.COUNT
EXECUTE IMMEDIATE V_SQL_STRING
USING DATA_COLL(I).C1,
DATA_COLL(I).C2,
DATA_COLL(I).C3,
DATA_COLL(I).C4;
END LOOP;
COMMIT;
END LOOP;
Thanks in Advance
Srinadh
To Srinadh
J. Laurindo Chiappa, April 07, 2017 - 3:48 pm UTC
We all know that some overhead is unavoidable in dynamic SQL ** and ** in the use of LOOPs : please mensure the performance of direct, non-dynamic SQL for UPDATE of just one table againts the same routine updating dynamically the only the same table , more or less in this way (I am using NUMBER as the datatype, change it if not correct) :
SQL> VARIABLE V1 number;
SQL> VARIABLE V2 number;
SQL> VARIABLE V3 number;
SQL> VARIABLE V4 number;
SQL> execute :v1 := valuedesiredfor v1;
SQL> execute :v2 := anothervaluedesiredforv2;
SQL> execute :v3 := anothervaluedesiredforv3;
SQL> execute :v4 := anothervaluedesiredforv4;
and then :
SQL> UPDATE nameofthetable SET C4= :V1 WHERE C1= :V2 AND C2= :V3 AND C3= :V4;
SQL> COMMIT;
Compare performance of the code against the dynamic SQL code passing as argument the same table used in the code above, and see what you see : IF, as I suspect, the performance of the non-dynamic AND the dynamic code is more or less the same, you will look in other sources (such as index mainteinance, lock waiting, among many others) the root cause of the bad performance....
If this is not the case and the non-dynamic code is much quicker, you coud generate the 80 fixed UPDATEs in a script, maybe...
Best regards,
J. Laurindo Chiappa
Regards,
J, Laurindo Chiappa