IT Training

Newsletters

Developer's Corner

Developer's Corner

Oracle PL/SQL Debugging Tools

Part I: Using DBMS_TRACE

by Mike Marion
SQLSoft+ Senior Instructor/Developer

As the Oracle PL/SQL developer's first debugging tool, the DBMS_OUTPUT package is easy enough to use. The PUT_LINE procedure allows you to display debugging messages composed of any combination of string literals and variable values from any place in your code at anytime. It's enough to make an old coder (like me) wax poetically about the COBOL DISPLAY command! You can even turn the debugging messages on and off dynamically in your code using the ENABLE and DISABLE procedures. But eventually, you will run into the limitations and issues of DBMS_OUTPUT, including:

• Maximum debugging buffer size is 1,000,000 bytes.
• Tells you what the values of your variables are, but not necessarily how they got to be that way.
• Proliferation of DBMS_OUTPUT.PUT_LINE calls obfuscates the work of the actual code.

What Debugging Tools Are Available?

First of all, we are not going to discuss SQL debugging tools. We will save the discussion of TKPROF, AUTOTRACE, Explain Plan and the like for another set of articles. And, we will also not be discussing any of the various trace facilities available to the DBA to watch the execution of the internal components of the database server itself.

So our hunt for PL/SQL debugging tools (other than DBMS_OUTPUT) begins. Alright, maybe our DBA knows some good tools, or maybe we'll ask our peer group… SQL*Navigator, Toad, PL/SQL Developer, even Oracle Forms and Reports… All of these are excellent GUI development environments. All include wonderful debugging tools, but none are free, and your boss is out of budget. Well, how do these GUIs get all of this information? Believe or not, Oracle does provide some useful tools for finding out what our PL/SQL code is doing. The two most useful are:

• DBMS_PROFILER – This package compiles statistics about your test runs, the program units executed, and the lines of code executed. At the line level, you get to see how many times a particular line was executed, the total amount of time those executions took, and the minimum and maximum execution times for the given line. Because these stats are stored in permanent tables, you can compare different runs of the same code over time to see (any) improvement.
• DBMS_TRACE – This package allows you to see the actual execution sequence of the lines of code as they were executed within a program unit for a particular run. You have options as to the type(s) of lines you want to trace: calls to procedures and functions only, exceptions only, SQL statements only, or any combination (or all) of the above. Again, this information is stored in permanent tables so you can compare runs from different times.

Each of these tools demands a discussion of its own. Each one needs a lot of setup, much of it requiring the privileges of the DBA. I will discuss DBMS_TRACE (the shorter discussion) in this article. I will save DBMS_PROFILER for an upcoming Developer's Corner.

Warning: This article is an overview of the processes and tasks involved when using DBMS_TRACE. It is not an exhaustive discussion of all the options and settings available. Nor is this article a detailed, step-by-step description of all the processes and tasks involved. If you are interested is pursuing such information, please consider SQLSoft+'s course ora10g102, Oracle Database 10g: PL/SQL Application Tuning. That 4-day course provides the working knowledge you need to perform the tracing of your code.

Features of the DBMS_TRACE Package

The DBMS_TRACE Package has many features to control the tracing of your code. You can imagine that trying to debug parts of large systems can generate large volumes to trace information at the line level. These features give you granular control over what program units are traced during you test run.

• You control when the trace starts and ends with procedure calls.
• When the trace is started, you specify what types of statements to trace.
• You can pause and resume the tracing at any point(s) you desire.
• You can place comments on your runs to differentiate them later.
• You can place an approximate limit on the total number of trace records generated.
• Only program units compiled with the DEBUG option are traced.
• You can verify that the DBMS_TRACE version matches the database version. Incompatibility here can cause issues during debugging runs.

DBA Setup Tasks

Your DBA must prepare the Oracle Server to allow PL/SQL tracing. This includes the following tasks, which must be done while logged into the SYS user:

• Execute the script (found in the administrator directory of the server) that installs the DBMS_TRACE package. The script will also create the appropriate public synonym and grant the execute privileges necessary.
• Execute the script (found in the administrator directory of the server) that installs the PLSQL_TRACE tables used by everyone. The script drop the tables first (those drop commands will fail the first time the script is run).
• Manually create the synonyms for the PLSQL_TRACE tables, and grant the necessary Select, Insert, Update and Delete privileges to the appropriate developers.

At this point, developers (that have been granted the privileges mentioned above) can start making debugging runs.

Enable Modules for Trace

Each program unit (procedure, function, or package) you wish to debug must be enabled for tracing. To accomplish that, the program unit must be compiled with the DEBUG option. (You can also set the session debug flag on, so that all compiles done in the session automatically have the DEBUG option.) Package specifications and bodies can be enabled separately, if desired. The easiest way to do this is probably an ALTER command such as the following:

Set the Trace Options

Setting the "focus" of the trace run is done when you start tracing. This is accomplished by calling the DBMS_TRACE.SET_PLSQL_TRACE procedure. You have several options for setting focus:

The options are actually constants in the DBMS_TRACE package, and they are additive. They are like bit switches. You can add these constants together to get any combination of settings. Note: if you specify both a trace_all_ option and a trace_enabled_ option, the "all" option wins.

Limit the Trace Lines

If you are concerned about how many trace lines will be generated, you can put an approximate limit on the number of lines allowed during this trace run.

Run the Test

Now, run the program unit you want to debug. This will place the trace lines in the trace tables for later querying. Then stop the tracing when done.

Pause/Resume Trace

When desired, you can pause and resume the trace around a procedure or function call to eliminate unwanted trace lines from program units you don't need to debug…

Run Number and Comment

When your test/trace is done, you will need to know what trace runnumber was assigned to the run by DBMS_TRACE. To find this out, run the GET_PLSQL_TRACE_RUNNUMBER function. In the example below, the current runnumber is displayed to the SQL*Plus session for future use.

You can also place a comment on the run. Use the COMMENT_PLSQL_TRACE procedure. This comment is available later, so you can remember what you did.

Extract Trace Information

Once the CLEAR_PLSQL_TRACE procedure stops the run, you can then execute queries to see what happened. The first stop is the PLSQL_TRACE_RUNS table. To see what runs are available, who ran them and when, try a query like this:

To see the execution sequence of the lines of code, you need to look at the PLSQL_TRACE_EVENTS table. For example, use a scattergun approach to see the lines of code executed during the run…

Now you can see that you got some debugging data you didn't need – for example, you don't need to debug the DBMS_TRACE package. If you are interested in only some of the lines, add some more WHERE predicates. This is especially useful to pick out the code from the program unit(s) you want, or the types of statements you want -- SQL statements (EVENT_KIND=54) or call statements (EVENT_KIND=45) for example. Of course, you could also rerun the test with DBMS_TRACE.trace_all_sql and/or DBMS_TRACE.trace_all_calls only. You can also join this data to the ALL_SOURCE Data Dictionary view and match the line numbers to get the actual source code that matches each of these trace lines.

So Many Options, So Little Time

As you can see, there is a lot of information available in the trace tables, such as the list of Event Kinds (EKIND in the listing). For more information about the contents of the tables, or the processes necessary to build and interpolate them, check out ora10g102, Oracle Database 10g: PL/SQL Application Tuning. Or, jump directly into the Oracle documentation. (The link will get you to the Oracle PL/SQL Packages and Types Reference section about DBMS_TRACE. A user account is required.)

If you have any questions about SQL, PL/SQL, Oracle Developer, or even Java, feel free to e-mail me (Mike Marion). I actually love this stuff. Sometimes, your questions are my only contact with the real IT world! (Wait, is that an oxymoron?) I'll try to get your question answered in 24-48 hours. Just remember that sometimes I am out of town teaching and may not be able to get my company e-mail.

Thanks for hanging in there... and Good PL/SQL Debugging to you all!