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:
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!).
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 ...