Monthly Archives: March 2012

Instrumentation (when referring to code) is code that is put in place to allow you to monitor the execution of the code. Instrumentation in PL/SQL code is something that is often missing. Worse, a lot of people don’t allow instrumentation because they worry that adding the extra code to do the instrumentation will slow down the code too much. It is true that there could be a small bit of performance decrease due to the extra code. However, it is so minimal, this should never be a consideration, especially with the extreme benefits it provides. Proper instrumentation will let you see exactly where your code is performing poorly, so you can focus your efforts where it will have the most benefit.

The best part is that Oracle provides built in packages that make it extremely easy to instrument your code. What I use the most is DBMS_APPLICATION_INFO to set the module and action, so I know what exactly is running:

dbms_application_info.set_module(module_name => 'Employee Maintenance',action_name => 'employee_update');

The module should refer to the business task, and the action should identify the specific process that is running for that task.

I also use DBMS_APPLICATION_INFO if I need my code to see what is currently running:

dbms_application_info.read_module(module_name => v_start_module, action_name => v_start_action);

I typically use this to keep track of what was previously running before I call set_module, so I can set the module and action back to what it was when my code is done.

Once module and action are being set, you will notice their values showing up in V$SESSION (columns module and action) and Enterprise Manager screens will even use these values in its performance reports.

For more information, check out the excellent presentation by Karen Morton of Method R titled “Performance Instrumentation for PL/SQL: When, Why, How”. It can be found on the Method-R presentations page