Oracle PL/SQL Tuning tips Oracle Tips by Burleson Consulting

There are several proven techniques for improving the speed of PL/SQL execution and they are presented below in order of importance:

Use bulk collect:  When reading-in lots of related rows, bulk collect can run 10x faster than a conventional loop.  This tuning is possible because Oracle reduces context switches into a single operation.

Use forall:  When loading a table from an array, the forall operator is 10x faster than a conventional SQL insert statement.  Again, this is because of the reduction of context switching.

Use SQL analytics:  Many advanced data operations can be done without using PL/SQL and are readily available as a SQL built-in function.

Use implicit cursors:  Implicit cursors are faster in PL/SQL than explicitly defining cursors

The following PL/SQL tuning Tips are from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

• Introducing PL/SQL tuning optimization

• Variables and Constants in PL/SQL

• Branching and Conditional Control in PL/SQL

• PL/SQL Procedures, Functions and Packages

• Object Type tuning in PL/SQL

• Tuning with Varrays in PL/SQL

• Error Handling in PL/SQL

• Writing optimal PL/SQL

• Dynamic Bind tuning in PL/SQL

• Show Errors

• Implicit vs. Explicit PL/SQL Cursors

• Bulk Bind tuning in PL/SQL

• Declarations, Blocks, Functions and Procedures in Loops

• Minimize Datatype Conversions

• Efficient tuning of PL/SQL Function Calls

• Using the NOCOPY Hint

• Using BINARY_FLOAT and BINARY_DOUBLE

• Decoupling (Cheating) for faster PL/SQL Performance

• Introduction to PL/SQL Bulking for high performance

• Bulk Collection tuning with the LIMIT Clause

• Bulk Collection of DML Results

• Bulk UPDATE tuning in PL/SQL

• Sparse Collections

• Handling Exceptions in Bulk Operations

• Using Arrays for tuning PL/SQL

• Using Package Variables to Store Global Data

• Using Contexts to Store Global Data

• PL/SQL RAM Memory tuning

• Pinning Packages in the Shared Pool

• Introduction to Cursor Variables

• Cursor Variables as Parameters

• Cursor Attributes and Cursor Variable Usage

• Dynamic SQL and Variant Resultsets

• Cursor Expressions

• Table Functions and Pipelining

• Parallel Enabled Table Functions

• Creating Transformation Pipelines

• Deterministic Functions in PL/SQL

• Introduction to PL/SQL Monitoring

• The DSP PL/SQL Package

• dbms_application_info Package

• dbms_profiler Package

• dbms_trace Package

• Generating SQL Trace Files

• Trace Example

• SQL Execution Plan tuning

• dbms_xplan Package

• Dynamic Performance Views (V$)

• session_stats.sql and system_stats.sql

• Tuning PL/SQL with STATSPACK

• Automatic Database Diagnostic Monitor for tuning

• Dynamic SQL and Bulk Operations

Other PL/SQL references:

• Oracle PL/SQL Links

• Oracle PL/SQL Self Study Questions

• Oracle PL/SQL: The evolution and features of PL/SQL

For a complete overview of Oracle PL/SQL, see the book "Easy Oracle PL/SQL Programming".  It's only $19.95 and has a downloadable code depot: