One of my biggest problems when I debug my code is that I am overconfident about my development and debugging skills, so I try to address too many problems at once. I make five or ten changes, rerun my test, and get very unreliable and minimally useful results. I find that my changes cause other problems (a common phenomenon until a program stabilizes, and a sure sign that lots more debugging and testing is needed), that some, but not all, of the original errors are gone, and that I have no idea which changes fixed which errors and which changes caused new errors.
In short, my debugging effort is a mess, and I have to back out of changes until I have a clearer picture of what is happening in my program.
Unless you are making very simple changes, you should fix one problem at a time and then test that fix. The amount of time it takes to compile, generate, and test may increase, but in the long run you will be much more productive.
Another aspect of incremental testing and debugging is performing unit tests on individual modules before you test a program that calls these various modules. If you test the programs separately and determine that they work, when you debug your application as a whole (in a system test), you do not have to worry about whether those modules return correct values or perform the correct actions. Instead, you can concentrate on the code that calls the modules. (See the earlier section Section 19.4 for more on unit testing.)
19.6 Tuning PL/SQL Programs
Tuning an Oracle application is a complex process—you need to tune the SQL in your code base, make sure the System Global Area (SGA) 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 the PL/SQL runtime architecture in Chapter 20.
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 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 should not take clearly inefficient approaches to meeting requirements, you also should not 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 make use of 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. From the RETURNING clause to FORALL, make sure you aren't "living in the past" and paying the price in application inefficiencies.
It is 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. Furthermore, 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 performance of your code, and then offer a limited amount of tuning advice that will apply to the broadest range of applications.
19.6.1 Analyzing Performance of PL/SQL Code
Before you can tune your application, you need to figure out what is running slowly and where you should focus your efforts. Oracle and third-party vendors offer a variety of products to help you do this; generally they focus on analyzing the SQL statements in your code, offering alternative implementations, and so on. These tools are very powerful, yet they can also be very frustrating to PL/SQL developers. They tend to offer an overwhelming amount of performance data without telling you what you really want to know: how fast did a particular program run, and how much did the performance improve after making this change?
To answer these questions, Oracle offers a number of built-inutilities. The most useful are:
DBMS_PROFILER
This built-in package allows you to turn on execution profiling in a session. Then, when you run your code, Oracle uses tables to keep track of detailed information about how long each line in your code took to execute. You can then run queries on these tables or—much preferred—use screens in products like TOAD or SQL Navigator to present the data in a clear, graphical fashion.
DBMS_UTILITY.GET_TIME
Use this built-in function to calculate the elapsed time of your code down to the hundredth of a second. The scripts tmr81.ot and plvtmr.pkg (available on the O'Reilly site) offer an interface to this function that allows you to use "timers" (based on DBMS_UTILITY.GET_TIME) in your code. These make it possible to time exactly how long a certain operation took to run, and even to compare various implementations of the same requirement.
In case you do not have access to a tool that offers an interface to DBMS_PROFILER, here are some instructions and examples.
First of all, Oracle does not yet install DBMS_PROFILER for you automatically. To see if DBMS_PROFILER is installed and available, connect to your schema in SQL*Plus and issue this command:
SQL> DESC DBMS_PROFILERIf you then see the message:
ERROR:ORA-04043: object dbms_profiler does not existyou will have to install the program.
For Oracle 7.x and 8.0, you need to ask your DBA to run the following scripts under a SYSDBA account (the first creates the package specification, the second the package body):
$ORACLE_HOME/rdbms/admin/dbmspbp.sql
$ORACLE_HOME/rdbms/admin/prvtpbp.plb
For Oracle8i and Oracle9i, you need to run the $ORACLE_HOME/rdbms/admin/profload.sql file instead, also under a SYSDBA account.
You then need to run the $ORACLE_HOME/rdbms/admin/proftab.sql file in your own schema to create three tables populated by DBMS_PROFILER:
PLSQL_PROFILER_RUNS
Parent table of runs
PLSQL_PROFILER_UNITS
Program units executed in run
PLSQL_PROFILER_DATA
Profiling data for each line in a program unit
Finally, you will probably find it helpful to take advantage of some sample queries and reporting packages offered by Oracle in the following files:
$ORACLE_HOME/plsql/demo/profrep.sql $ORACLE_HOME/plsql/demo/profsum.sqlOnce all these objects are defined, you gather profiling information for your application by writing code like this:
BEGIN DBMS_OUTPUT.PUT_LINE (DBMS_PROFILER.START_PROFILER ('showemps ' || TO_CHAR (SYSDATE, 'YYYYMMDD HH24:MI:SS'))); showemps; DBMS_OUTPUT.PUT_LINE (DBMS_PROFILER.STOP_PROFILER);END;Once you have finished running your application code, you can run queries against the data in the PLSQL_PROFILER_ tables. Here is an example of such a query that displays those lines of code that consumed at least 1% of the total time of the run:
/* File on web: slowest.sql */SELECT TO_CHAR (p1.total_time / 10000000, '99999999') || '-' || TO_CHAR (p1.total_occur) AS time_count, p2.unit_owner || '.' || p2.unit_name unit, TO_CHAR (p1.line#) || '-' || p3.text text FROM plsql_profiler_data p1, plsql_profiler_units p2, all_source p3, plsql_profiler_grand_total p4 WHERE p2.unit_owner NOT IN ('SYS', 'SYSTEM') AND p1.runid = &&firstparm AND (p1.total_time >= p4.grand_total / 100) AND p1.runid = p2.runid AND p2.unit_number = p1.unit_number AND p3.TYPE = 'PACKAGE BODY' AND p3.owner = p2.unit_owner AND p3.line = p1.line# AND p3.NAME = p2.unit_nameORDER BY p1.total_time DESC;As you can see, these queries are fairly complex (I modified one of the canned queries from Oracle to produce the above four-way join). That's why it is far better to rely on a graphical interface in a PL/SQL development tool.
After you have analyzed your code and identified bottlenecks, the following sections can help you determine what kinds of changes to make to your code to improve performance.
