PL/SQL was first released in 1992 as an optional extension to Oracle 6. In its initial years it lacked the optimizations needed to efficiently process queries and other DML operations which resulted in slaggy performance in comptute - intensive task. Over the years oracle has emerged as the most robust relational database and the performance of PL/SQL has skyrocketed.
The data focused performance problems have disappeared over the years with the advent of array processing optimization techniques such as BULK_COLLECT and FORALL. The compute-intensive performance gap has narrowed significantly with the introduction of native compilation, compute-friendly data types, such as SIMPLE_NUMBER, and the function cache. Intensive algorithm from PL/SQL to Java can still improve performance.
Determining the performance bottlenecks within PL/SQL code has also augmented and improved significantly over the years when the original line based profiler, DBMS_PROFILER has been superseded by a hierarchical profiler, DBMS_HPROF, making it possible to determine the calling context and to separate out the overheads associated with subroutine calls.
Data driven optimization.
Optimization on large number of rows
Processing a large number of rows one at a time can lead to performance overheads, as the same block may be requested multiple times, leading to redundant logical reads. Array processing, by using the BULK_COLLECT statement with the LIMIT clause, allows rows to be fetched more efficiently in groups.
In 11g with at least the default level of optimization (PLSQL_OPTIMIZE_LEVEL set to 2 or higher) cursor FOR loops are automatically rewritten to make use of BULK_COLLECT with a LIMIT of 100. As the optimization is not too sensitive to the LIMIT value, the default optimization should be adequate in most cases (using very large values for LIMIT or omitting it altogether can cause memory contention).
DML Optimization
In the case of DML operations that manipulate a large number rows the FORALL statement can be used to enforce array processing (note, array optimization is not enforced by default in the case of DML operations).
Trigger Optimization
Trigger performance can be improved by ensuring that the PL/SQL trigger block is only entered when the trigger code will cause a change to the database. In the case of UPDATE statements, the FOR UPDATE clause can be used to restrict the trigger to the appropriate subset of table columns, and the WHEN clause can be used to exclude block entry on the basis of an arbitrary predicate.
Where a choice is available, an AFTER ROW trigger is usually better than a BEFORE ROW trigger, as a BEFORE ROW trigger will pre-emptively lock the context row.
Dynamic SQL
Using bind variables reduces the parsing overhead and contention for latches, by allowing a single parsed representation of a SQL statement to be executed many times. In the case of SQL statements that reference values by means of PL/SQL variables, there is no need to explicitly introduce bind variables, as PL/SQL variable references are automatically treated as bind variable references in most cases. The exception is dynamic SQL. In this case a bind variable reference must be added explicitly to the SQL statement and referenced in a USING clause.
SQL Different to PL/SQL
Tuning the SQL statements called from within PL/SQL is likely to have a far bigger impact on performance than tuning of the PL/SQL code itself, so the tuning of embedded SQL should be attempted first and seperately.
Compute-Intensive Optimizations
Data Type Selection
Very considerable performance gains can be achieved by avoiding automatic data type conversions, and by selecting the most restrictive data type that will adequately represent the set of values required.
In the case of floating point numbers, either BINARY_FLOAT or BINARY_DOUBLE is more efficient than NUMBER. In the case of integers, PLS_INTEGER is more efficient than NUMBER, and SIMPLE_INTEGER is more efficient than PLS_INTEGER (SIMPLE_INTEGER can produce a performance gain over NUMBER of about 5 for interpreted code and 30 for machine code).
Call by Reference
Like many other programming languages PL/SQL allows variables to be passed by reference or by value. The default method is by value. However, when passing large values, such as PL/SQL collections, into or back from a function there can be a considerable performance overhead. Using the IN OUT NOCOPY or OUT NOCOPY constructs can be used to specify that the corresponding parameter should be passed by reference rather than by value. However, passing values by references is generally best avoided when the values are small as it prevents effective encapsulation and leads to more brittle code.
Recursion
High levels of recursion should be avoided as each call requires the allocation of additional PGA memory, decreasing performance and reducing the memory available for use by other processes.
Loop Termination and Optimization
As in all programming languages, it makes sense to remove invariant statements from within loops and to exit loops as soon as possible with an EXIT statement once further iterations are no longer required.
Compiler-Based Optimizations
Compiler Optimization
Since 10g it has been possible to specify the level of optimization that occurs during compilation (whether compilation to PL/SQL bytecode or to native, machine code). The optimization level is set using parameter PLSQL_OPTIMIZE_LEVEL. To find the current value for the session:
SHOW PARAMETER PLSQL_OPTIMIZE_LEVEL
To set the current value for the session:
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = <n>;
To recompile a previously compiled procedure or function after changing the session value:
ALTER ( PROCEDURE | FUNCTION ) COMPILE;
Different levels of Optimization
0 – Oracle 9i compatibility level, with very little optimization and no side effects
1 – Removes unnecessary computations and exceptions, but does not reorder code
2 – Removes invariants from loops, transforms FOR loops to BULK_COLLECT with a LIMIT of 100
3 – Inlines subroutines
Native Compilation
By default when a PL/SQL procedure or function is compiled, it is compiled to PL/SQL bytecode, and it is executed by the PL/SQL interpreter engine. For compute-intensive routines a performance gain of perhaps (5-15)% can be obtained by compiling the routine into native, machine code instead. In 11g, native compilation has been made very easy. The compilation mode is determined by the value of parameter PLSQL_CODE_TYPE, which has values of INTERPRETED (default) or NATIVE.
To find the current value for the session:
SHOW PARAMETER PLSQL_CODE_TYPE
To set the current value for the session:
ALTER SESSION SET PLSQL_CODE_TYPE = ( INTERPRETED | NATIVE );
The performance of PL/SQL code for compute-intensive tasks has increased dramatically – by a factor of up to 100 – since PL/SQL was first launched. However, a Java program called from a PL/SQL wrapper will still outperform a native PL/SQL compilation of code that makes the best used of performant data types, such as SIMPLE_INTEGER, by a factor of perhaps 5.
PL/SQL Inlining
Inlining is a common compiler optimization technique in which a call to a subroutine is replaced by the code contained within the subroutine. Some inlining is done automatically when the value of PLSQL_OPTIMIZE_LEVEL is set to 3. Inlining can be forced by included the manual directive:
PRAGMA_INLINE ( <function name>, ‘YES’ )
before a call to a PL/SQL procedure or function. Performance gains tend to be modest, at around the 50% mark.
Function Caching
One of the new features in 11g is a function cache for routines or statements that include calls to PL/SQL functions. The
RESULT_CACHE
This clause is added to the function definition. A mapping between the function’s input parameters and the function’s return value is held in memory. This mapping is looked up on each function call, and the mapped value is used if available; otherwise, the function is called in the normal manner, and the input parameter values and return value are added to the mapping. Function caching is only of value if the same function is likely to be called repeatedly with the same parameter values. Clearly, the function must be deterministic and not depend on side effects, such as accessing a changing global package variable or a random number generator. A cached function can also access database tables, and while the table data should be relatively stable given the frequency of function calls it can change, as the function cache will be automatically re-initialized.