Oracle Database 11gR2 Performance Tuning Cookbook

Chapter 6. Optimizing PL/SQL Code

In this chapter, we will cover:

• Using bind variables and parsing
• Array processing and bulk-collect
• Passing values with NOCOPY (or not)
• Using short-circuit IF statements
• Avoiding recursion
• Using native compilation
• Taking advantage of the function result cache
• Inlining PL/SQL code
• Using triggers and virtual columns

Introduction

In this chapter, we will focus on PL/SQL code, such as stored procedures, functions, and triggers.

The topics explained in this chapter are easier to apply than those in Chapter 4, Optimizing SQL Code because you can make changes to PL/SQL code without having to rebuild the entire application—and they can be just as easily reversed if something doesn't work as expected.

It is relatively easy to obtain ...