Tuning PL/SQL code

Once the DBA configures the database for optimal performance, the code development plays an essential role in PL/SQL performance. Now, we will discuss certain areas of improvements. These improvements can be made during the development stage so as to avoid the nightmares later.

The tunable areas identified and covered in this section are as follows:

• Avoiding implicit typecasting: Identification of appropriate data type

• Modularizing the programs: Modular programming and effective code sampling shares the work load

• Usage of bulk bind collections and the FORALL function : Usage of the FORALL function and bulk bind collections optimize the bulk operations

• Optimized conditional statements: Conditional statements can be optimized by logically placing the conditions

Besides the above areas, there are several other areas where tuning can bring comprehensive changes in performance. There is an immense scope for tuning in the code which uses dynamic SQL, SQL queries in PL/SQL blocks...