Tuning an Oracle application is a complex process: you need to tune the SQL in your code base, make sure the System Global Area is properly configured, optimize algorithms, and so on. Tuning individual PL/SQL programs is a bit less daunting, but still more than enough of a challenge. Before spending lots of time improving the performance of your PL/SQL code, you should first:

Tune access to code and data in the SGA

Before your code can be executed (and perhaps run too slowly), it must be loaded into the SGA of the Oracle instance. This process can benefit from a focused tuning effort, usually performed by a DBA. You will find more information about the SGA and other aspects of PL/SQL internals in Chapter 23.

Optimize your SQL

In virtually any application you write against the Oracle RDBMS, the vast majority of tuning will take place by optimizing the SQL statements executed against your data. The potential inefficiencies of a 16-way join dwarf the usual issues found in a procedural block of code. To put it another way, if you have a program that runs in 20 hours, and you need to reduce its elapsed time to 30 minutes, virtually your only hope will be to concentrate on the SQL within your code. There are many third-party tools available to both DBAs and developers that perform very sophisticated analyses of SQL within applications and recommend more efficient alternatives.

Once you are confident that the “context” in which your PL/SQL code is run is not obviously inefficient, you should turn your attention to the code base. I suggest the following steps:

Write your application with best practices and standards in mind

While you shouldn’t take clearly inefficient approaches to meeting requirements, you also shouldn’t obsess about the performance implications of every line in your code. Remember that most of the code you write will never be a bottleneck in your application’s performance, so you don’t have to optimize it. Instead, get the application done and then...

Analyze your application’s execution profile

Does it run quickly enough? If it does, great: you don’t need to do any tuning (at the moment). If it’s too slow, identify which specific elements of the application are causing the problem and then focus directly on those programs (or parts of programs). Once identified, you can then...

Tune your algorithms

As a procedural language, PL/SQL is often used to implement complex formulas and algorithms. You can use conditional statements, loops, perhaps even GOTOs and (I hope) reusable modules to get the job done. These algorithms can be written in many different ways, some of which perform very badly. How do you tune poorly written algorithms? This is a tough question with no easy answers. Tuning algorithms is much more complex than tuning SQL (which is “structured” and therefore lends itself more easily to automated analysis).

Take advantage of any PL/SQL-specific performance features

Over the years, Oracle has added statements and optimizations that can make a substantial difference to the execution of your code. Consider using constructs ranging from the RETURNING clause to FORALL. Make sure you aren’t living in the past and paying the price in application inefficiencies.

It’s outside the scope of this book to offer substantial advice on SQL tuning and database/SGA configuration. Even a comprehensive discourse on PL/SQL tuning alone would require multiple chapters. Further, developers often find that many tuning tips have limited or no impact on their particular environments. In the remainder of this chapter, I will present some ideas on how to analyze the performance of your code and then offer a limited amount of tuning advice that will apply to the broadest range of applications.

Please check back next week for the conclusion to this article.