Top 10 Way To Handle Performance Tunning of Procedure




1. Use NOCOPY Hint with procedure parameter like OUT,INOUT.



2. Declare only variable that you can use in programs.



3. Always try to declare new variable with %TYPE and %ROWTYPE.



4. Tune embedded sql statement.



5. Tunning Cursor.



why this, Because cursor is nothing but a select statement.
It may be simple select statement or complex select statement.



6. Cursor handling.



Cursor handling means open cursor,fetch data from cursor,close cursor.


Need to check how we are fetching data from cursor, using bulk collect or directly inserting data into table.



7. Exception handling.


If there is normal exception handle then no issue,But if any procedure or function or any sub-program called from exception then need to check that subprogram.



8. Replace Pl/Sql with Sql statement.



9. Use Pl/Sql datatype.



10. use Bulk collect, this is very useful in performance tuning.



11. use FORALL for DML operations.