Tuning your PL/SQL code starts with tuning the SQL statements you use in that code. If you are using a cursor,...
that cursor is based on a query. Have you tuned that SQL statement so that it effeciently pulls the data out of the database?
You also mentioned that you are updating rows in another table in your cursor's loop. Are you committing inside that loop? That can be a performance killer. Take the COMMIT out of the loop and see if that helps your performance.
Finally, are you sure that you cannot perform this as one simple SQL statement rather than updating in a cursor's loop? For instance, I might have the following psuedo code:
CURSOR c1 IS SELECT valX FROM tableX; OPEN c1; LOOP FETCH c1 INTO x; EXIT WHEN c1%NOTFOUND; UPDATE tableY SET colA = colA*1.10 WHERE colX = x; END LOOP; CLOSE c1;
UPDATE tableY SET colA = colA*1.1.0 WHERE colX IN (SELECT valX FROM tableX);
Dig Deeper on Oracle database performance problems and tuning
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.