Oracle PL/SQL Programming, Third Edition

Improving Application Performance

Now that we have reviewed some of the ways you can analyze your application’s execution, let’s consider some tips for improving the performance of that application.

Avoid Unnecessary Code Execution

This is “mom and apple pie” advice in the world of tuning, and may seem rather obvious. Surely, if the code is not necessary I would not include it in my program. Surely. And under the pressure of the moment, I would never take shortcuts or do bad things because I always have plenty of time to pay the necessary attention to write optimized code. Right.

The reality is that we usually rush headlong into our coding tasks, feeling just a bit panicky and overwhelmed, annoyed that management won’t buy us good tools and in a frenzy to get the job done and done well. And that is why, contrary to the laws of logic, it is all too easy to end up with code that really does not need to be executed in our programs. Removing such code will usually improve performance, sometimes in a dramatic fashion.

The search for unnecessary code

In an application with thousands of lines of source, how should you look for potential problems? Here are my recommendations:

Check your loops

Code within a loop (FOR, WHILE, and simple) usually executes more than once. Therefore, any inefficiency in a loop’s scope can have a multiplying effect.

Check your SQL statements

First of all, you should of course make sure that your SQL statements have been optimized. That topic is outside ...