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.
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
X_API.Get_Xxxcalls with one
- 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.