25. Tuning PL/SQL Applications

Contents:
Analyzing Program Performance
Tuning Access to Compiled Code
Tuning Access to Your Data
Tuning Your Algorithms
Overview of PL/SQL8 Enhancements

Tuning an application is a very complicated process. Really, it deserves a book of its own. Fortunately, there is such a book: Oracle Performance Tuning by Mark Gurry and Peter Corrigan.[ 1 ] Many of the ideas in this section are drawn from -- and explored more thoroughly in -- Gurry and Corrigan's book.

[1] O'Reilly & Associates, Second Edition, 1996. There are other books on Oracle tuning as well.

Before diving into the particulars, I want to be sure that you recognize the different aspects of tuning PL/SQL that you might want to perform:

Analyzing program performance. Before you can tune your application, you need to figure out what is running slowly and where you should focus your efforts.
Tuning access to compiled code. Before your code can be executed (and perhaps run too slowly), it must be loaded into the System Global Area (SGA) of the Oracle instance. This process can benefit from a focused tuning effort.
Tuning access to your data. Much of the tuning you do will attempt to optimize the way PL/SQL programs manipulate data in the Oracle database, both queries and DML (updates, inserts, deletes). Lots of the issues here involve tuning SQL statements (out of the scope of this book), but there are certainly steps you can take in your PL/SQL code and environment to improve the performance of even an optimally constructed chunk of SQL.
Tuning your algorithms. As a procedural language, PL/SQL is often used to implement complex formulas and algorithms. You make use of conditional statements, loops, perhaps even GOTOs and reusable modules (I hope) 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? A tough question with no simple answers; I offer some case studies at the end of this chapter which will perhaps give you some fresh approaches to bring to bear on your own code.

The following sections address each of these areas of PL/SQL tuning.

25.1 Analyzing Program Performance

Before you can tune your application, you need to know what is causing it to slow down. To do this, you usually need to be able to analyze the performance of your application. Oracle offers a number of database monitoring and diagnostic tools, as do third-party vendors like Platinum Technology and Quest. Check Oracle documentation and Chapter 10 of Oracle Performance Tuning for more details, and be aware of the following major tools:

MONITOR: A SQL*DBA facility that lets you look at various system activity and performance tables.
SQL_TRACE: A utility that writes a trace file containing performance statistics.
TKPROF: A utility that translates the SQL_TRACE file into readable output and can also show the execution plan for a SQL statement.
EXPLAIN PLAN: A statement that analyzes and displays the execution plan for a SQL statement.
ORADBX: An undocumented tool that allows you to track a running process and create a trace file in the same format as the SQL_TRACE trace file. You can then run TKPROF against the trace file to obtain the execution plan details, as well as disk I/O, parsing, and CPU usage.
ANALYZE: A statement that compiles statistics for use by the cost-based optimizer to construct its execution plan. The statement also produces other useful information that can be used to detect chained rows and help with capacity planning.
UTLBSTAT (begin) and UTLESTAT (end): Scripts that produce a snapshot of how the database is performing from the time you start UTLBSTAT until you run UTLESTAT.
Enterprise Manager/Performance Pack: An Oracle product introduced with Oracle7.3 that provides some excellent tuning tools, including Oracle Performance Manager, Oracle Trace, and Oracle Expert.

25.1.1 Use the DBMS_UTILITY.GET_TIME Function

The tools listed in the previous section provide varying levels of detail and granularity; however, they all do require some effort -- often on the part of a person other than the PL/SQL developer in need -- to get them enabled. And then they require even more effort to interpret results. Don't get me wrong; I am not really complaining. It's just that, quite frankly, PL/SQL developers often want to examine the performance of a particular program and do not want to have to deal with all that other stuff.

No problem! PL/SQL provides a mechanism to obtain timings of code execution that are accurate to 100th of a second: the DBMS_UTILTY.GET_TIME function. Yes, that's right. I said 100th of a second. For those of you who have programmed in Oracle over the past few years, this should be a welcome surprise. Before the advent of the DBMS_UTILITY package, the only way to measure elapsed time was to use SYSDATE and examine the difference in the time component. Sadly, this component only records times down to the nearest second. This doesn't help much when you need to measure subsecond response time.

DBMS_UTILTY.GET_TIME returns the number of hundredths of seconds which have elapsed since some arbitrary point in time. I don't remember what that point is and, well, that's the whole point. A single value returned by a call to dbms_utility.get_time is, by itself, meaningless. If, on the other hand, you call this built-in function twice and then take the difference between the two returned values, you will have determined the number of hundredths of seconds which elapsed between the two calls. So if you sandwich the execution of your own program between calls to DBMS_UTILTY.GET_TIME, you will have discovered how long it takes to run that program.

The anonymous block below shows how to use GET_TIME to determine the time it takes to perform the calc_totals procedure:

DECLARE    time_before BINARY_INTEGER;    time_after BINARY_INTEGER; BEGIN    time_before := DBMS_UTILITY.GET_TIME;    calc_totals;    time_after := DBMS_UTILITY.GET_TIME;    DBMS_OUTPUT.PUT_LINE (time_after - time_before); END;

I found myself relying on GET_TIME frequently as I developed the code in this book, because I wanted to analyze the performance impact of a particular approach or technique. Is it faster to raise an exception or execute an IF statement? Is it faster to load 100 rows in a table or concatenate 100 substrings into a long string?

There are two basic approaches you can take to using this handy function:

Write again and again the kind of script you see above, changing the program or lines of code executed.
Encapsulate the way dbms_utility.get_time operates inside a package, which will hide the details and make it easier to use.

You will find on the companion disk an explanation and code for such a package, sp_timer, in the files sptimer.sps and sptimer.spb. In addition, you will find in Advanced Oracle PL/SQL Programming with Packages a more complete performance timing utility based on DBMS_UTILITY.GET_TIME in the PLVtmr package.

Once you have encapsulated your usage of DBMS_UTILITY.GET_TIME, it is very easy to put together scripts which not only analyze performance, but also compare different implementations. The following script, for example, executes two different versions of the is_number function (see " Section 25.4, "Tuning Your Algorithms" " for more information on this function) and displays the resulting elapsed times (using the PLVtmr and p packages from the PL/Vision library; again, see Advanced Oracle PL/SQL Programming with Packages :

SET VERIFY OFF DECLARE    b BOOLEAN; BEGIN    PLVtmr.set_factor (&1)    PLVtmr.capture;    FOR repind IN 1 .. &1 -- Number of iterations    LOOP       b := isnum ('&2'); -- The string to test       IF repind = 1       THEN          p.l (b);       END IF;    END LOOP;    PLVtmr.show_elapsed (`TO_NUMBER Version');     PLVtmr.set_factor (&1)    PLVtmr.capture;    FOR repind IN 1 .. &1    LOOP       b := isnum_translate ('&2');       PLVtmr.last_timing := 15;       IF repind = 1       THEN          p.l (b);       END IF;    END LOOP;

 

     PLVtmr.show_elapsed (`TRANSLATE Version');

 

   END;

 

 

 

  /


24.2 Debugging Tips and Strategies		25.2 Tuning Access to Compiled Code