Performance - PL/SQL

Achieving good performance is very important in server business logic. This section provides some useful guidelines and hints.

This section describes commonly known performance issues that can be avoided by applying these relatively simply performance standards. The issues are not sorted by importance. The nature of performance related issues is such that there is no absolute fix that works for all cases. In some cases one of the solutions described below could be the best solution and in another case it could be better to go with some other solution. But if you suspect that the code you write or change, is critical for performance then you should discuss your proposed solution with a technical expert.

Contents

Here are some of the potential performance bottlenecks and the recommended solutions to avoid those

• Micro Cache PL/SQL
  Runtime cache where the data is saved temporarily.
• Unnecessary Function Calls
  Replace several X_API.Get_Xxx calls with one X_API.Get.
• Cursor For-Loop
  Use bulk collect to fetch data from a cursor.
• NOCOPY Parameter Passing
  Use NOCOPY for parameters with large Data Structures.
• Bind variables instead of concatenating values
  Use Bind variables to reuse the execution plan when executing the same statement multiple times.
• Move unnecessary statements out of Loops
  Move unnecessary statements outside the Loops
• Nested Loops
  Replace inefficient nested loops executing SQL with carefully written SQL
• Functions in Select and Where
  Use PL/SQL functions and packages carefully in your SQL statements, especially in WHERE clauses.
• Packing/Unpacking Attribute Strings
  Use record types instead of packing and unpacking attribute strings in the calls within the server.
• Select *
  Only select the required columns when writing cursors for views or tables.