You 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).
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.
First, 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:
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.
How 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.
Another 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:
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.