Optimizing PLSQL code performance

Question:
How can I fine-tune the performance of my PL/SQL procedures and functions?
Answer:
This is, of course, the sort of question that doesn't lend itself easily to a quick or short answer. Nevertheless, I offer some brief recommendations below for ways that you can improve the performance of your code. They are drawn from my book, Oracle PL/SQL Programming, which in turn relies heavily on the Oracle documentation for this topic (PL/SQL Users's Guide and Reference, Chapter 11: "Tuning PL/SQL Applications for Performance").
One general reminder to preface this list: Don't obsess about optimizing the performance of every single line of code; instead, follow best practices and concentrate on creating readable and maintainable code, taking advantage of the latest and greatest features of PL/SQL. Then test your application for bottlenecks, and concentrate your optimization efforts on resolving those bottlenecks.
Tune your SQL statements and be aware of context switching
It's rare to call PL/SQL subprogram without—sooner or later in the call tree—invoking SQL. And in the common case, when SQL is invoked, the total time for the initiating PL/SQL call is usually dominated by the time spent processing the SQL statements. So make sure that the SQL statements themselves are optimized. In addition, whenever you switch context between the PL/SQL and SQL statement execution engines, you incur non-trivial overhead. In particular, be aware of calls to PL/SQL functions from within your SQL statements, which can cause a large number of switches. You should also keep context switches to a minimum (see recommendations on BULK COLLECT and FORALL below).
Use the most aggressive compiler optimization level possible
Oracle Database 10g Release 1 introduced an optimizing compiler for PL/SQL programs. The default optimization level of 2 takes the most aggressive approach possible in terms of transforming your code to make it run faster. You should use this default level unless compilation time is unacceptably slow and you are not seeing benefits from optimization.
Enable all compiler warnings during development
Oracle Database 10g Release 1 also offers compile-time warnings about your code, which include a family of performance warnings that report on a variety of specific aspects of PL/SQL code execution. Make sure that you have enabled all compiler warnings during development so that you can take maximum advantage possible of this feedback.
Rely on supplied functionality whenever possible
Oracle, both through the native PL/SQL language and through its many supplied packages, offers a tremendous amount of highly-tuned functionality. You should take some time to become familiar with what is available before embarking on a new project, so that you don't reinvent the wheel. For example, it is undoubtedly quicker (both at runtime and when you write it) to use the Dbms_Random API than to write your own random number generator. Another example: in Oracle9i Database Release 2, you can define associative arrays that index by VARCHAR2. This offers many tantalizing possibilities for quickly and easily sorting data manipulated in your subprograms.
Use BULK COLLECT when querying multiple rows
The BULK COLLECT statement retrieves multiple rows of data through either an implicit or an explicit query with a single context switch. BULK COLLECT reduces the number of context switches between the PL/SQL and SQL engines and thereby reduces the overhead of retrieving data. Rather than using a cursor FOR loop or other row-by-row querying mechanism, switch to BULK COLLECT for a dramatic improvement in performance.
Note: The Oracle Database 10g optimizing compiler will automatically optimize cursor FOR loops, you may not need to hand-optimize those loops. Sometimes such a loop will contain statements, such as an INSERT or UPDATE, that could benefit from a rewrite of the loop to populate collections that can be used in a FORALL statement, described below.
Use FORALL when modifying multiple rows
As with BULK COLLECT, FORALL greatly reduces context switching between the PL/SQL and SQL engines, but this time for updates, inserts, and deletes. You can expect to see an order of magnitude (or greater) improvement in performance for multiple-row DML execution with FORALL.
Use the NOCOPY hint when passing large structures
The NOCOPY parameter hint requests that the PL/SQL runtime engine pass an IN OUT argument by reference rather than by value. This can speed up the performance of your programs, because by-reference arguments are not copied within the program unit. When you pass large, complex structures like collections, records, or objects, this copy step can be expensive.
Use PLS_INTEGER for intensive integer computations
When you declare an integer variable as PL.S_INTEGER, it will use less memory than INTEGER and rely on machine arithmetic to get the job done more efficiently. In a program that requires intensive integer computations, simply changing the way that you declare your variables could have a noticeable impact on performance.
In Oracle8i Database and Oracle9i Database, PLS_INTEGER will perform more efficiently than BINARY_INTEGER. In Oracle Database 10g, these two datatypes have become identical and thus perform equally well.
Use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic
Oracle Database 10g introduces two, new floating-point types: BINARY_FLOAT and BINARY_DOUBLE. These types conform to the IEEE-754 floating-point standard and use native machine arithmetic, making them more efficient than NUMBER or INTEGER variables.
Group related programs in a package
Whenever you reference any single element in a package for the first time in your session, the entire package is cached in the shared memory pool. Any other calls to programs in the package require no additional disk I/O, thereby improving the performance of calling those programs. Group related programs into a package to take advantage of this feature.
Pin into shared memory large and frequently executed programs
Pin frequently accessed programs in the shared memory pool with the DBMS_SHARED_POOL.PIN procedure. A pinned program will not be flushed out of the pool using the default least-recently-used algorithm. This approach guarantees that the code will already be present when it is need.