Code Instrumentation Best Practices for Oracle Databases

 It is critical to know when to use the various types of code instrumentation features, where to place them, and, most importantly, what you are trying to achieve by instrumenting your code. Achievable goals usually target two groups of problems:

• Inefficient debugging
• Poor understanding of resource utilization

The majority of existing IT system professionals spend the greatest amount of effort trying to solve debugging problems because these problems are the most visible ones. The less time you spend trying to pinpoint a logged bug, the more time you can spend trying to fix it before clients start to complain about the downtime. As a result, very often, performance issues are piggybacked onto the existing error management systems. Normally, there is nothing wrong with that approach, but error management is usually handled by developers, while performance optimization is handled by DBAs. In addition, most code instrumentation mechanisms do not provide enough flexibility and/or depth to assist with tuning efforts.

In the realm of code instrumentation, the critical factor is placing logger calls in your code. The art and science involved is in doing it properly. First, all logger calls are not created equal. There are two completely different subgroups:

• Process markers (“Misha was here!”) Although the PL/SQL Hierarchical Profiler can determine which program units were called by others, it cannot provide information about IN/OUT parameters, the status of local/global variables, and so forth, or even provide current values of different statistics. It is important to have these kinds of markers in the code because, with all of these extra elements, you may be able to replicate detected issues. Process markers can also be considered “timing markers.” If each event is time-stamped with sufficiently detailed granularity, the complete historical log is the best and most convenient source of information for key performance criteria, namely how much time was spent in every part of the program unit.
• Error markers (“Tell me all about your problem!”) If something goes wrong, it is very important to have as complete a picture as possible about the whole environment surrounding the error. There is a large amount of information about sessions and processes that can help localize the problem. If you know your system well, inclusion of needed data elements in the exception handling mechanisms simplifies the process of putting the puzzle together.

You need to keep in mind that there are some very peculiar conditions to satisfy while developing a logging framework:

• Markers should be based on as few lines of code as possible and should be easy to insert into the existing programs without a lot of modifications. Otherwise, you may not find widespread use of them in the developer community.
• It is important to ensure that the right information is gathered to determine exactly where problems are occurring within a program unit. The meaning of “right” is different for different audiences. That is why all potential information recipients should be involved in setting requirements for timers.
• Markers should minimize the system impact on any level. No significant CPU resources or disk I/O should be involved.
•  If you do not want the process markers to exist at all during normal production running of the system, you can use the Oracle conditional compilation feature.
• Error markers should not be conditional. They may even be costly, because having more data about errors has higher priority than resource consumption.
• Code should be kept readable.

Satisfying all of the elements in this list is an attempt to have your cake and eat it too. The code base should be well instrumented to enable an efficient review process, but the footprint of the instrumentation should be minimized (whenever possible), in terms of both the human and technical resources spent. Although, instead of considering those resources to be wasted, you may consider them to be a part of your insurance plan against unforeseen events. As with all insurance, better protection means higher premiums.

       NOTE

In addition to home-grown code instrumentation, there are different existing libraries and sets of APIs that are accessible online and often free. Keep in mind that in many IT environments, bringing outside packages into the system raises too many security red flags. However, nothing prevents you from studying available best practices and applying them directly.