How to optimise your pl/sql code? Not using explain plan which is for tuning the quey

Nikhil Inamdar

 Profile Answers by Nikhil Inamdar Questions by Nikhil Inamdar

• Jul 8th, 2016

You can use DBMS_PROFILER to check performance bottlenecks and then change the code where the PLSQL code is taking more time and improve performence.

For more details go through below link.
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_profil.htm

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

The best technique used is to avoid repeatative coding by putting repeated logic in a subroutine/function. Save the DML operations after a specific interval or records insertion/updation/deletions , depending on your archive log size.

Answer Question  Select Best Answer

What is the amount of performance improvement you get with doing BULK COLLECT? I tried answering I will measure with the cost.. but the interviewer wants a specific answer. Is there any general formula for deriving the cost improvement based on the no. of records processed in bulk fetch?

Nikhil Inamdar

 Profile Answers by Nikhil Inamdar Questions by Nikhil Inamdar

• Jul 4th, 2016

Generally for millions of records, BULK COLLECT performs very much faster. If we compare BULK COLLECT and normal CURSOR FOR LOOP then BULK COLLECT can perform almost 4 times faster.
You can create your own program and use DBMS_UTILITY.GET_TIME for comparing the results.

dinesh

• Feb 3rd, 2013

We can limit the bulk binding values. It would improve the overall performance. It would not consume more memory.

Answer Question  Select Best Answer