Performance Tuning Guide - 10g Release 1 (10.1)
PL/SQL allows us to perform many activities not available to standard SQL. If you use PL/SQL in your application, consider some of the following measures to improve the performance of your PL/SQL code:
- Use normal code optimization techniques. In particular, avoid unnecessary loops and place the more frequently satisfied conditions first in IF clauses.
- Take advantage of stored procedures and functions. Storing PL/SQL in the database avoids parsing overhead.
- Use explicit cursors in PL/SQL. An implicit cursor will often perform additional fetches.
- Use the WHERE CURRENT OF CURSOR clause to quickly access a row currently open in a cursor. Alternately, fetch and use the ROWID column if you want to avoid the overhead of the FOR UPDATE statement.
- Cache frequently accessed data values in PL/SQL tables.
LOOP and IF Statements
Minimize the number of iterations
– Use EXIT to cease looping where necessary
Remove any statements within a loop that could be processed outside the loop
– Especially when you have nested loops
Specify the most probable condition first in a compound IF statement
– Try to reduce the number of evaluations
Array processing
BULK COLLECT statement allows data from a query to be loaded directly into a PL/SQL table
FORALL allows data to be inserted into a table directly from a PL/SQL block
For large operations, array processing is typically 10 times faster than non-array
WHERE CURRENT OF cursor
Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly
– This is faster than looking up the row by the primary key updated.
The cursors SQL must be defined with the FOR UPDATE clause
– This can cause some overhead
Saving the rowid as using that for the lookup can be a useful alternative
– But you need to check that the row has not been altered since you opened the cursor
NOCOPY clause
If a subroutine takes a PL/SQL table as an argument, a copy of the table is created for the subroutine
– This may be time consuming, especially if the subroutine is called repeatedly
NOCOPY allows the subroutine to work on the table directly
– This is sometimes called “passing by reference” vs. “passing by value”
Avoiding Recompilation
Use packages rather than procedures.
– Providing the package header remains constant, changes to subroutines will not cause recompilation of dependent procedures
Pin large or performance critical packages in the shared pool
– Dbms_shared_pool.keep
Optimizing Triggers
Ensure that triggers fire only when required
– Use WHEN clause to restrict trigger execution to specific logical conditions
– Use the UPDATE OF clause to restrict trigger execution to changes to specific rows
Explicit Cursors
Although you can embed SQL directly in a routine, explicitly creating a cursor is more efficient
– An implicit cursor does a second fetch to ensure that only one row is returned
– This is especially important if the SQL performs a table scan
Using the PL/SQL Profiler
The DBMS_PROFILER package allows you to identify hot spots in your code
Surround your PL/SQL call with
DBMS_PROFILER.START_PROFILER and
DBMS_PROFILER.END_PROFILER
The output is stored in
PLSQL_PROFILER_RUNS,
PLSQL_PROFILER_UNITS and
PLSQL_PROFILER_DATA
