Tuning PL/SQL Code - Optimizing PL/SQL Performance
(Page 2 of 5 )
This section contains brief recommendations for ways to improve the performance of your code and points you to other sections in the book that cover each topic more thoroughly.
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. See the “The Optimizing Compiler” section in this chapter for detailed information.
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 round trip to and from the database. 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. See the “BULK COLLECT” section in Chapter 15 for more about this feature.
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. See the “Bulk DML with the FORALL Statement” section in Chapter 14 for detailed information.
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. See the “The NOCOPY Parameter Mode Hint” section in Chapter 17.
Use PLS_INTEGER for intensive integer computations.
When you declare an integer variable as PLS_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. See the section “The PLS_INTEGER Type” in Chapter 9 for a more detailed discussion.
In Oracle8i Database and Oracle9i Database, PLS_INTEGER will perform more efficiently than BINARY_INTEGER. In Oracle Database 10g, they are equally performant.
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. See “The BINARY_FLOAT and BINARY_DOUBLE Types” section in Chapter 9.
Group together 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. See the “Packaging to improve memory use and performance” section in Chapter 23 for details.
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 guarantees that the code will already be present when it is need. See the “What to Do if You Run Out of Memory” section in Chapter 23.
Next: Protecting Stored Code >>
More Oracle Articles
More By O'Reilly Media
|
 This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 11 
