value as shown below: new_rate := mortgage_rate (down_payment, prime_rate, mortgage_type); The mortgage_rate function was, unfortunately, setting new_rate to NULL. After examining the function, it was clear to me that the only way it could return a NULL was if one of the inputs was NULL. I then used this information as follows: 1. I had just examined the global variable holding the mortgage type. That global value was transferred to the mortgage_type variable in an earlier program and passed to my current module, so I knew that it was OK. 2. I performed a walk-through of the code and could not identify how any of the other two variables could be NULL. 3. So I inserted a trace statement before and after the call to mortgage_rate. My code now looked like this: DBMS_OUTPUT.PUT_LINE ('Inputs: ' || TO_CHAR (down_payment) || '-' || TO_CHAR (prime_rate)); new_rate := mortgage_rate ( down_payment, prime_rate, bank, mortgage_type); DBMS_OUTPUT.PUT_LINE ('Rate: ' || NVL (TO_CHAR (new_rate), 'Missing')); 4. I ran the program and no matter what data I entered on the screen, my trace statements remained the same: Inputs: 55000-9.5 Rate: Missing I wracked my obviously overwrought brain: what could cause a stored function to return a NULL value? I looked at the source code for the function again. There wasn't much to it. Just division and multiplication. How could it return a NULL without a NULL input? After two hours of this nonsense, I finally said to myself, "Well, you know that you really haven't verified the value of the mortgage_type variable." I knew that it was OK, but, hey, it wouldn't hurt to check -- and if I didn't solve this one soon I would have to actually ask for help. So I modified my trace statement and, sure enough, the mortgage type was NULL. Turns out that while the global variable held the proper value, the previous program did not pass it to the local variable properly. My assumption did me in. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. One of the first assumptions you will make in your testing is that you have valid inputs to your program. This can also be one of the most dangerous assumptions to make. Data errors are perhaps the most frustrating of all the kinds of bugs you will encounter. You follow all the right steps for debugging and analyzing your code, only to discover that there is nothing actually wrong with your program. Instead, the data that drives your program is wrong. I encounter this problem most often in the following situations: ● I am testing my code in multiple database instances. Never assume that the data structures and actual rows of data are identical in all instances. Never assume that all indexes have been defined the same way. ● The reference data is still unstable. What is the valid code for a "closed" call? What are the ten valid company types? If this data is still changing, your program is likely to break. If you do not understand why your program is doing what it is doing, make a list of all your assumptions and then test those -- including the data you rely on to run your program. There is a good chance that your error was introduced very early into the process. 24.2.8 Leverage Existing Utilities -- Or Build Your Own As you build more and more complex programs, you will find it increasingly difficult and incredibly frustrating to manage and debug these programs without a utility of some kind. Take the time to investigate what is available and what it will do for you. Historically, Oracle Corporation has been very slow to offer debugging and other programmer- oriented utilities. Third-party vendors seem to have taken a clue from Oracle and also have not hurried to provide a strong set of tools for PL/SQL developers. As of mid-year 1997, that situation is finally changing. You can now purchase debuggers from the following vendors: ● Oracle Corporation: Procedure Builder ● Platinum Technology: SQL Station Debugger ● Technosolutions: SQL Navigator ● Compuware: XPediter/SQL All of these products greatly improve the ability to debug client-side PL/SQL; you will need to carefully examine the specific benefits and features before deciding which of these (and, I hope, by the time this book is published, others as well) fit your needs most closely. If, on the other hand, you cannot find anything that will help (or you can't get the approval to buy the utility of your dreams), you might consider building your own. I have found in the past that it is relatively straightforward to implement utilities that have a significant impact on my debugging capabilities. I built XRay Vision, a debugger for SQL*Forms, implemented entirely in SQL*Forms itself, which gave me the ability to view and modify all variables in my programs. You'll find this Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. debugger, xrayvizn.zip, on the RevealNet Web site. While you are unlikely to be using SQL*Forms at this point, you may find the source code of interest (stored in the good, old INP files). When Oracle Forms 4.x (even the version in Oracle Developer/2000 that has its own source code debugger) came along, I realized that it was impossible to view and change data structures created at runtime (record groups, parameter lists, etc.). So I built a utility named Arjy (pronounced "RG" for Record Group), which gave me that access. The shareware version of Arjy, arjy.zip is also available at the RevealNet Web site. The basic PL/SQL product from Oracle Corporation will never have everything you need. If you can't find what you need to get the job done, then get creative and take a crack at meeting your own needs. Dive in and build your own utility. Not only will you improve your productivity (and that of others), but you will gain a feeling of intense satisfaction from solving your own problems all by yourself. 24.2.9 Build Debugging Messages into Your Packages If you do not use a GUI-based, source code debugger, you probably spend a fair amount of time throwing debug or trace messages into your code and then removing them when things are fixed. A much better approach is to leave these messages intact, but give yourself lots of flexibility in deciding when you want to see them. The simplest model for this technique is actually built right into the DBMS_OUTPUT package. The DBMS_OUTPUT.PUT_LINE procedure displays output from your PL/SQL program when that program completes execution. But it will not show anything unless the package itself is enabled with a call to DBMS_OUTPUT.ENABLE and/or unless from within SQL*Plus you issue the SET SERVEROUTPUT ON command. Furthermore, this is an all-or-nothing proposition: you either see no output, or you see output from every call to this procedure from every corner of your application. That can be overwhelming if you have inserted lots of trace messages. You can easily come up with a more discerning technique when working with packages. Suppose I have developed a package to perform calculations for profit-and-loss statements. My p_and_l package maintains a last statement date as a global variable for use within the package. I build a "get and set" layer of code around the variable so that applications can retrieve and manipulate the variable -- but only through my code layer. Here is the package: CREATE OR REPLACE PACKAGE p_and_l IS PROCEDURE set_lastdate (date_in IN DATE); FUNCTION lastdate RETURN DATE; /* Lots of other stuff, too! */ . . . END p_and_l; / Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CREATE OR REPLACE PACKAGE BODY p_and_l IS g_lastdate DATE; PROCEDURE set_lastdate (date_in IN DATE) IS BEGIN /* Date cannot be in future. */ g_lastdate := LEAST (SYSDATE, date_in); END; FUNCTION lastdate RETURN DATE IS BEGIN RETURN g_lastdate; END; END p_and_l; / As I test this package as part of a large, complex application, suppose that I find that the last date variable is being set improperly, but I can't figure out what is doing it and why. I can go into the p_and_l.set_lastdate procedure and insert a call to DBMS_OUTPUT.PUT_LINE as follows: PROCEDURE set_lastdate (date_in IN DATE) IS BEGIN DBMS_OUTPUT.PUT_LINE (`setting date to ` || TO_CHAR (date_in)); /* Date cannot be in future. */ g_lastdate := LEAST (SYSDATE, date_in); END; but then I have to see all the output in my application and try to find this one statement among all the others. The approach I take instead is to provide a debug "toggle" in my package which allows me to focus output to just the statements I need to see. With the toggle technique, I add three programs to my package specification: CREATE OR REPLACE PACKAGE p_and_l IS PROCEDURE set_lastdate (date_in IN DATE); FUNCTION lastdate RETURN DATE; PROCEDURE dbg; PROCEURE nodbg; FUNCTION debugging RETURN BOOLEAN; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. /* Lots of other stuff, too! */ . . . END p_and_l; / I also modify the package body to both implement this toggle and use it inside the set_lastdate procedure: CREATE OR REPLACE PACKAGE BODY p_and_l IS g_lastdate DATE; g_dbg BOOLEAN := FALSE; PROCEDURE dbg IS BEGIN g_dbg := TRUE; END; PROCEDURE nodbg IS BEGIN g_dbg := FALSE; END; FUNCTION debugging RETURN BOOLEAN RETURN g_dbg; END; PROCEDURE set_lastdate (date_in IN DATE) IS BEGIN IF debugging THEN DBMS_OUTPUT.PUT_LINE (`before set ` || TO_CHAR (g_lastdate)); END IF; /* Date cannot be in future. */ g_lastdate := LEAST (SYSDATE, date_in); IF debugging THEN DBMS_OUTPUT.PUT_LINE (`after set ` || TO_CHAR (g_lastdate)); END IF; END; FUNCTION lastdate RETURN DATE IS BEGIN RETURN g_lastdate; END; END p_and_l; / Then if I want to see what is happening to the g_lastdate variable, I can issue the debug command in SQL*Plus for this package and see just the output I need: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. SQL> exec p_and_l.dbg SQL> exec testing_program before set 12-JAN-97 after set 15-JAN-97 Of course, you'd probably want to see more information, such as the execution call stack to see what program called the p_and_l.set_lastdate procedure. You can add anything you want -- all within the confines of the IF debugging clause -- and that information will be available only on a need-to-know basis. You might even decide to free yourself from the confines of DBMS_OUTPUT by writing information out to a database pipe. Furthermore, if you set as a standard in your group that every package is to have a debug toggle, then it will be much easier for users of those packages to debug their own use (or misuse) of that reusable code. They know that there will be a program named PKG.dbg which can be used to extract additional information about package processing. This technique is explored in more detail and with a slightly different focus (production support) in Chapter 26, Tracing PL/SQL Execution. Previous: 24.1 The Wrong Way to Debug Oracle PL/SQL Programming, 2nd Edition Next: 25. Tuning PL/SQL Applications 24.1 The Wrong Way to Debug Book Index 25. Tuning PL/SQL Applications The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Previous: 24.2 Debugging Tips and Strategies Chapter 25 Next: 25.2 Tuning Access to Compiled Code 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. [...]... 10, PL/SQL Tables, shows how you can use a PL/SQL table stored in a package to "remember" values which have already been queried in that session If the value is in the PL/SQL table, the function returns that value If not, it retrieves the value from the database table and, before returning the value, stores it in the PL/SQL table 25.3.2 Call PL/SQL Functions in SQL to Reduce I/O Chapter 17, Calling PL/SQL. .. the closest you can get to true array processing for SQL inside a PL/SQL program 25.3.5 Avoid Procedural Code When Possible Compared to Oracle SQL, PL/SQL is a slow language when it comes to processing SQL statements So whenever possible, you should take advantage of set-oriented SQL statements instead of row-at-atime cursor processing in PL/SQL Consider the following block of code: DECLARE CURSOR table_X_cur... Debugging Tips and Strategies Oracle PL/SQL Programming, 2nd Edition Book Index Next: 25.2 Tuning Access to Compiled Code 25.2 Tuning Access to Compiled Code The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates All rights reserved Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark Previous: 25.1 Analyzing Program Performance Chapter 25 Tuning PL/SQL Applications Next:... cursor with a PL/SQL table When you fetch from the cursor, you will be able to dump a specified number of values from multiple rows into that PL/SQL table DMBS_SQL BIND_ARRAY This procedure allows you to bind multiple values from a PL/SQL table to a DML statement, so that you can, say, insert 100 rows in a single call to DBMS_SQL.EXECUTE, instead of having to make 100 calls to this function See Oracle Built-in... Analyzing Program Performance Oracle PL/SQL Programming, 2nd Edition Book Index Next: 25.3 Tuning Access to Your Data 25.3 Tuning Access to Your Data The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates All rights reserved Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark Previous: 25.2 Tuning Access to Compiled Code Chapter 25 Tuning PL/SQL Applications Next:... you would never be tempted to write a whole bunch of PL/SQL code when a simple UPDATE statement would suffice! It is worth the reminder, however, because as we spend more and more of our time cranking out PL/SQL code, we can easily lose perspective We might find ourselves trying to do everything in PL/SQL and forget about our other options 25.3.6 Use PL/SQL to Improve Performance of IO-Intensive SQL Now... object is first referenced in a PL/SQL program, the PL/SQL engine checks the ACCESS$ table (owned by SYS) to see if the executor of the program has authority on that database object The structure of this table is shown here: SQL> desc access$ Name D_OBJ# ORDER# COLUMNS TYPES Null? -NOT NULL NOT NULL Type -NUMBER NUMBER RAW(32) NOT NULL NUMBER The PL/SQL engine searches through this... by Oracle Developer/2000 developers When you create a form in Oracle Forms, the base table block takes care of an awful lot of the required SQL, including some very complex locking mechanisms Am I saying that you should abandon all of that Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark "automatic" stuff and write a large volume of code? Definitely not (though with Oracle. .. requires just one network call to get the job done 25.3.4 Take Advantage of DBMS_SQL Batch Processing The PL/SQL8 version of DBMS_SQL allows you to perform bulk updates, inserts, deletes, and queries The new version of this handy package (which allows for the execution of dynamic SQL and dynamic PL/SQL inside PL/SQL programs) offers the following programs to support "array processing": Please purchase PDF... Package Variables Prior to PL/SQL8 , any data declared in a package simply stayed around until the end of the session, whether or not it was needed any more by the application This is an important feature of PL/SQL packages (persistent, global data), but it limits scalability since such memory grows linearly with the number of users To help applications better manage memory usage, PL/SQL8 provides the pragma . watermark. An Oracle product introduced with Oracle7 .3 that provides some excellent tuning tools, including Oracle Performance Manager, Oracle Trace, and Oracle. Chapter 26, Tracing PL/SQL Execution. Previous: 24.1 The Wrong Way to Debug Oracle PL/SQL Programming, 2nd Edition Next: 25. Tuning PL/SQL Applications