This project is hosted at SourceForge.net. 
Click the following links for more information and access to download the files.

Introduction

Diagnosing and repairing performance problems in an Oracle environment can be a complicated and time-consuming job. However, you, the Application Developer, can make the job much simpler for everyone downstream (including yourself) by inserting a few extra lines of code—called instrumentation—into your applications. With the right instrumentation library, the job is easy. The Hotsos Instrumentation Library for Oracle (ILO) gives you the lines of code that you’re looking for.

The Payoff

Instrumentation makes your code faster, easier to maintain, and cheaper to write. Instrumentation makes your code faster because it shows you all your opportunities for making your code more efficient, right while you’re writing it. Instrumentation makes your code easier to maintain because it shows you exactly what your customer or support analyst is talking about when she says your code is too slow. And instrumentation makes your code cheaper to write because it focuses your tuning efforts only upon the parts of your code where performance really matters. Good instrumentation is your immunization against premature optimization, “the root of all evil” (Hoare’s Dictum).

All You Have to Do is Mark Your Tasks

A task is any unit of work whose duration you want to measure. HOTSOS_ILO_TASK makes it very simple to define tasks within your application. You simply bracket the code path that constitutes a performance-measurable work unit (a task) with calls to BEGIN_TASK and END_TASK, like this:

hotsos_ilo_task.begin_task(module, action, client, comment);

/* The code path for your task goes here. */

hotsos_ilo_task.end_task;

Using BEGIN_TASK and END_TASK does all the Oracle housekeeping your application needs. All that stuff you’ve ever heard about DBMS_APPLICATION_INFO and DBMS_SESSION… Now you can forget about it. All you have to remember is to mark your tasks.

How to Name Your Tasks

• Task names in HOTSOS_ILO_TASK contain two hierarchical components: the MODULE and the ACTION. Think of the ACTION as the name of the task itself and the MODULE as a means of identifying what part of your application the task represents. For smaller applications, the module name could and probably should be also the name of the package that contains the code. The action name is the operation being performed for this module.
  
  For example, the package INBOUND_SALES_ORDER_FEEDS contains many procedures and functions. One such procedure is called INSERT_BOM_SO; it inserts into several tables. A good task name for this procedure would be
  

  module_name=>'Sales Orders'
  action_name=>'Add BOM'
      

• Name a task with a name that is recognizable to the business. Notice that we’ve done this in the prior example. The hierarchical name 'Sales Orders'.'Add BOM' refers to a unit of work that is easily recognizable by name to the business.

• Tasks can nest. This is an especially useful feature for long-running tasks. Imagine that a task consumes an hour of run time in a loop. Your customer wants to measure and manage the duration of each loop iteration. You can define each loop iteration as a (sub)task, as in the following example:
  
  

  hotsos_ilo_task.begin_task('Billing', 'Invoice customers', '', '');
  for i in 1 .. r.count loop
          hotsos_ilo_task.begin_task('', 'Invoice one customer', '', i);
          /* Code to invoice a customer goes here. */
          hotsos_ilo_task.end_task;
  end loop;
  hotsos_ilo_task.end_task;
  

  Be careful how granular you get with your nesting. We’ve made every effort to make the Hotsos Instrumentation Library as fast and efficient as possible, but nested BEGIN_TASK calls can themselves become a performance problem if the tasks you’re instrumenting have very tiny durations.

• At minimum there are two lines in the package that need to be in all the code needing to be instrumented. The first line is the call to BEGIN_TASK. It should come right after the "BEGIN" line in procedures and functions. The END_TASK should be placed in the following locations:
  
  Procedures - The statement directly before the "EXCEPTION" line for that procedure and in every EXCEPTION block before the "END".
  
  Functions - The line directly before every "RETURN …" line, including "RETURN" statements in the EXCEPTION block.