Oracle PL/SQL Best Practices, 2nd Edition

Chapter 9. My Code Runs Faster Than Your Code

Certainly one of the most frustrating experiences an application user can have is slow performance. Software is supposed to help people get things done faster and more easily. Ah, the gap between “supposed to” and reality!

This chapter offers many recommendations for helping you improve the performance of your PL/SQL programs. Of course, tuning PL/SQL is just a small part (and often not the most crucial part) of application tuning. Development teams (and their DBAs) must also optimize SQL statements (the most common source of performance issues) and overall database instance activity. Before diving into the details, I offer the following high-level advice regarding code optimization:

Performance optimization is never a theoretical exercise

For example, I’ll talk in this chapter about such specifics as the FORALL statement and how you could see an enormous increase in performance when you use this statement. The operative word here is “could.” You need to verify the impact of this advice in your application, your installation of the Oracle database, your database server, and with your data. Don’t assume that you will see the same results that I have experienced (hey, yours might be even better!).

Remember the 80-20 rule

Most of the code you write will not be part of a performance bottleneck. So generally you should not obsess about optimizing every single line of code in your application. You will end up wasting a lot of time. Instead, you ...