[Chapter 26] 26.2 Tracing for Production SupportChapter 26Tracing PL/SQL Execution 26.2 Tracing for Production SupportYou build a PL/SQL-based application. You debug it, probably by placing calls to some kind of trace procedure throughout your code.[1] Finally, after very careful QA and extensive testing with users, the application is deployed out into the field. You now have hundreds of people working with your code across the country (or maybe just in a single building). [1] Unless you are lucky enough to be using one of the few PL/SQL debuggers out there in the marketplace like Platinum's SQL-Station or Oracle's Procedure Builder.And, of course, problems rear their ugly heads. A user calls the support line and says that his program is behaving in a certain way. You cannot easily reproduce the problem. You are not entirely sure that the environment in which you are running your code is the same as that of your user. What you would really like to do is connect into his session and watch what he is doing as he is doing it. This section explores an implementation of a package architecture which allows you to "hook" into running programs and perform production support. You can also adapt it for use as a debugger/execution trace mechanism. 26.2.1 Features of a Real-Time Support MechanismFirst, let's come up with a concise definition of "real-time" support for PL/SQL-based applications: the ability to watch or analyze the activity of a currently connected Oracle user without disturbing that user's activity or otherwise affecting the behavior of the application. This is, obviously, very different from simply running the "same" code under the "same" circumstances -- that is a simulation of what your users are doing. To achieve this real-time support, you need to be able to get information out of your PL/SQL program and place it in some kind of repository where you can analyze what is going on and come up with fixes. You also need to be able to enable and disable the support mechanism while the user session is executing. Based on my discussions with customers and my own experience, here are the kinds of features developers need to effectively support their applications: The mechanism should be completely transparent to the user -- both when it is enabled and when it is disabled. A support person can enable the mechanism "remotely," that is, she can connect into a running session, turn on (or turn off) the real-time trace, and then analyze the application behavior. When placing the trace inside the PL/SQL application code, the developer can specify a filter of some kind (nested levels, numeric ranges, categories, or some other type of filter) so that the support trace can be turned on selectively within the application. Output from the support mechanism can be changed and specified for each support session. In other words, sometimes you might want to write the trace information to a file, sometimes to a database pipe, and sometimes to a database table. In this chapter, I can't provide a detailed implementation of all these features. Instead, I will discuss some of the more interesting challenges, and offer you ideas on building such a mechanism yourself. 26.2.2 Starting and Stopping a Support SessionHow do you tell an Oracle session which is already up and running that you want to take a look around at its internal processing? How do you tell it that you are done and that it should stop feeding you information? Well, let's assume that you have put a call to the trace startup procedure at the beginning of each program. It would look something like this: CREATE OR REPLACE PROCEDURE calctotals IS BEGIN trace.startup ('calctotals'); . . . END; /I could then have trace.startup check something somehow to see whether the trace mechanism should be turned on or off. I could do the same thing in any other trace procedure which is placed inside application code. Let's take the simplest and most direct approach: using a database table. I create a table as follows:CREATE TABLE tracetab_activate (username VARCHAR2(60), action VARCHAR2(20));Then I add these constants and function to the trace package:CREATE OR REPLACE PACKAGE trace IS /* Just showing the part of the package that is relevant */ /* for this functionality. */ c_start CONSTANT CHAR(1) := 'Y'; c_stop CONSTANT CHAR(1) := 'N'; FUNCTION activated (username_in IN VARCHAR2) RETURN BOOLEAN; END trace; / CREATE OR REPLACE PACKAGE BODY trace IS /* Just showing the part of the package that is relevant for this functionality. */ FUNCTION activated (username_in IN VARCHAR2) RETURN BOOLEAN IS CURSOR act_cur IS SELECT action FROM tracetab_activate WHERE username = UPPER (username_in); act_rec act_cur%ROWTYPE; BEGIN OPEN act_cur; FETCH act_cur INTO act_rec; RETURN (act_rec.action = c_start); END activated; END trace; /With this function in place, I can modify my trace.startup procedure as follows: PROCEDURE startup (context_in IN VARCHAR2) IS BEGIN IF activated (USER) THEN log; ELSE nolog; END IF; /* Then the rest of the procedure activity. */ END startup;In other words, if the user should be activated, I call the trace.log procedure to turn on logging for that session. All calls to execution trace programs will then write their information out to the log specified by the PLVlog package (it can be an operating system file, database table, database pipe, etc.). Normally, the activation table would be kept empty. If a user calls with a problem, the support person can get that user's Oracle account name and issue an insert (I hope through some sort of GUI interface) as follows: INSERT INTO tracetab_activate VALUES ('SAM_I_AM', 'Y');The next time Sam I Am's session executes trace.startup, the trace will be activated and the analysis can commence. To deactivate the real-time trace, the support person can simply delete that record from the table. Then the next time trace.startup is executed, logging will be turned off. This approach provides a straightforward mechanism to "get inside" an already-running session. One concern about using the trace.activated function in this way, however, is that a query against the tracetab_activate must be performed every time that trace.startup (and other trace "show" programs) is encountered. This could turn into an unacceptable amount of overhead if an application is well-modularized. That's the difference between a prototype and a production-quality utility. To really make this architecture successful, it would very likely need to be fine-tuned. One refinement is to maintain a counter in the trace package and be able to specify that you want to check for activation of the trace logging only every 50 or 100 or 1000 calls to trace programs. This would cut down on the overhead by skipping lots of queries, but it would also mean that it might take longer to activate the trace. 26.2.3 Filtering Trace InformationAnother critical element of a successful support mechanism is the ability to specify which trace information you want to see. If a system contains four main subsystems (with lots of interactions) and hundreds of programs, you're probably going to want to look at trace information from a particular area of functionality, based on the clues provided by the user. There are many different approaches one can take to filtering out trace messages so the support person or developer (let's call this person the analyzer) sees only what is relevant at that time. Here are the ideas I have thought of: Free formatAllow the analyzer to provide a wildcard string which any trace message must be LIKE in order to actively logged during this connection. Knowing this in advance, a developer could concatenate a "context" or level or category to the beginning of the message string. This is the most primitive form of filtering and requires all developers to know about the way in which trace performs its filter check. Structured interfaceProvide a separate argument in calls to programs like trace.startup and trace.show which would allow a developer to explicitly associate a level (integer) or category (string) with a given trace. With this approach, the trace message is kept distinct from the filter criteria. This is probably easier for developers to work with, but it requires more work within trace to provide an interface through which the analyzer can request a trace just for specific levels, ranges of levels, or one or more categories. If you are going to try to design your own package to handle this kind of functionality, you will find yourself at a crossroads of sorts. How much effort do you want to put into something like this versus how much effort are you going to require that developers/analyzers make in order to take advantage of your package? It can sometimes be hard to justify the resources required to "do it right." Obviously, I can't make that decision for you. In fact, I had trouble making that decision for myself as I designed the PLVxmn package of PL/Vision. I can, however, review what I think an appropriate interface would be to support these different approaches. 26.1 The PL/SQL Trace Facility26.3 Free Format Filtering Copyright (c) 2000 O'Reilly & Associates. All rights reserved.