Instrumentation

During the ODTUG conference in New Orleans, there were multiple sessions for the Development/DBA. As I consider myself a developer with a great interest in the database, I love going to sessions like these.
One of the recurring themes was Instrumentation. During the Expert Panel a question was asked what the biggest thing is that a DBA can teach a developer. The short answer “Instrumentation”.
Also during the session given by Cary Millsap, Instrumentation was also indicated as one of the best practices a developer can do.
Tom Kyte did an hour presentation on Instrumentation, and I was lucky enough to be his Ambassador.

What and Why?

What do we actually mean with Instrumentation? It is a way of showing what is actually going on in your code. Without it, how can you be sure what is happening in your code? Suppose you have a PL/SQL block which looks like:

...
if var <= 10
then
   call_procedure ('x', var);
else
   call_another_procedure ('x', var);
end if;
...

How can you tell which procedure in this block of code is called?
You could go through the code at runtime, for example with a debugger. Or try to go through your code and interpreting the decision made by walking through it without actually executing it…
Would it be a lot nicer when you can look at something with data in it which shows you what actually happened? Something like:

- Start of the Procedure
--- Variable var has value NULL
--- Call_Another_Procedure is called
- Start CALL_ANOTHER_PROCEDURE
- incoming parameter: first_parameter: x
- incoming parameter: second_parameter: NULL
...

Looking at something like above helps to understand which decisions made by your application. Tracking down unexpected turns in your code all of a sudden is a lot easier to do. Include timestamps and other relevant information and you can also track execution times.
Do you need this extra code? Yes, I think you do. Even though it may be a little more code to write, it pays of the first time you use the results of the Instrumentation. When something in your application happens which you couldn’t explain, looking at the results will pinpoint the reason why it happend.
Does it slow down your code? If you have concerns wether it affects the performance of your application, test it! Don’t assume it will slow down your application. Test it, proof it! When it does in fact slow down your application to an unacceptable level, you can always use Conditional Compilation to remove the Instrumentation from your code when you go into Production . This could mean, however, that when you need your Instrumentation you will need to recompile your code on a Production System… Think about that.

Where and How?

Where do you need to place your Instrumentation? Everywhere. A simple start would be to replace the comments in your code by calls to your Instrumentation package. The package you use for Instrumentation could write its information to a database table or to a file. The information you include in the instrumentation should at least contain the “who, what, when, where, why”. Any relevant information you think you might need should be included. Better to include too much information than too little.
Using the built in package DBMS_APPLICATION_INFO lets you set information in V$SESSION. Client_info, Module and Action can be set with this package. This means that you can use the V$SESSION view to see at runtime what your application is doing. You can also use this package to add information to V$SESSION_LONGOPS given an indication of how long it will take for your procedure to complete.

References and Ready to use Instrumentation Packages

Of course you can build your own Instrumentation package, but why not use someone else’s?
Tom Kyte demonstrated a DEBUG package, which can be downloaded here.
There is Log4PLSQL, which is an Open Source Instrumentation similar to Log4J.
Hotsos also has an open source Instrumentation Library, which also writes to the raw trace file. You can read more on the last one in this blog post.