The forest is thick with menace. The sunlight barely breaks through the leaves and is sludgy like curdled milk; the shadows of the branches look like gnarly fingers reaching to grasp at the pair of frightened small children.
As they wind down the path, Hansel leaves a trail of pebbles. “It will help us find our way home,” he says to Gretel, his voice quavering with fear.
There is a parallel somewhere in there with the world of Oracle, believe me. I know that you’re unlikely to be eaten by an ugly, wicked witch while writing PL/SQL (I can’t give you the same guarantees with Sql Server), but think of Hansel’s pebbles as debug messages, think of it as instrumentation.
Instrumentation is the practice of measuring the progress of your application’s performance as it runs, and is usually pre-baked into the software at the time the code is being written. It is, of course, not a uniquely Oracle concept, but Oracle is all I know and all I will talk about in this article. So, if you got here by googling “Hansel and Gretel”, you may wish to leave now.
Do You Need Instrumentation?
If you’ve ever asked:
• Why is my application suddenly performing so poorly?
• Which of my procedures needs tuning?
• Where exactly is my application crashing?
• What exactly is my code doing?
• Why is my application suddenly performing so poorly?
• Which of my procedures needs tuning?
• Where exactly is my application crashing?
• What exactly is my code doing?
Or if you’ve ever said:
• Aaaaargh!
Then you probably do need instrumentation.
What Is Instrumentation?
Instrumentation, very simply put, is the practice of including your debugging into your code as the code is being written, and not after the fact, after the crash/issue/problem has happened. This way, when you need to understand what your code is doing, you can simply run your application and examine your logs.
What To Instrument?
No one understands your code as well as you do (I hope!) and so no one can supply you a full list of what information you want to capture in your instrumentation. However, the following are some practices that are generally accepted as wise.
• Include instrumentation at the entry point of every procedure and function that you write. The entry point, obviously, would be the first line after the BEGIN.
• Include instrumentation at the exit point of every procedure and every function that you write. The exit point of your procedures is right before your exception handling . Secondly, I’m not your mother and so I don’t want to nag you, but I do hope that your functions generally have only one exit point – one RETURN – and are not littered with RETURNs within conditional statements and other digressions. However, you should include instrumentation right before every exit point.
• If your routine accepts any parameters, you should include instrumentation outputting the values passed to each of these parameters.
• You will want instrumentation within your exception handling. You will definitely want to know if your code has raised an exception, and you will want to know what it was. Some Oracle experts argue that you should never have WHEN OTHERS THEN exception handling in your code. However, it may be a good idea to include it, instrument the exception, and then re-raise it.
• Instrument any significant points within the body of your code. Got a conditional statement? Then instrument the various paths within it. Writing a cursor? Then instrument any parameters it takes and any output it spits out.
• You may also want to include a debug message logging the SQL%ROWCOUNT after significant DML.