You need to be using DBMS_APPLICATION_INFO

I’m honestly very surprised how often I meet Oracle professionals who are not familiar with this package.  It’s a great thing to help you in debugging and monitoring your application.   It does require a little bit of “work” to use, and it’s little, but the benefits are potentially huge.

Let say I’m the owner of a particular piece of code that is pretty critical to the business, if it performs badly then lots of folks get pretty upset.  And being a proactive kind of guy I’d like to be able to monitor its performance even when it’s not causing problems.   The best way to see how something is running is to do a 10046 trace, or more formally, extended SQL tracing.

But tracing something is hard, especially since I want to trace a “thing” not a “user”.   This has been rather difficult until rather recently since all tracing was (are really still is) base on a session. But with DBMS_MONITOR we can now turn on tracing based on these things called “Module” and “Action”.

But to do this we need to set module and action otherwise this is useless.   This is where a few lines of code can make the huge impact.   The process is very simple; it’s a process that we’ve all used in one fashion or another in programming.  We want to set a variable to a value but we need to retain what it was set to, this way we can then set it back to its original setting later.

Here is a simple block of code to illustrate the process.  All I need to do to take advantage of these setting is use a few calls from the package:

DBMS_APPLICATION_INFO.read_module

DBMS_APPLICATION_INFO.set_module

And maybe

DBMS_APPLICATION_INFO.set_action

In my example I’m not using the last one.  I would use that in a more complicated code block where I’d want to show I’m doing something different within the same main code block. 

Here is a simple block of code to illustrate the process.

01)         PROCEDURE get_emp_instr IS

02)           fnlist_stack  fnlist_tab;

03)           lnlist_stack  lnlist_tab;

04)           preModuleName VARCHAR2(32) := NULL;

05)           preActionName VARCHAR2(32) := NULL;  

06)         BEGIN

07)           DBMS_APPLICATION_INFO.read_module(module_name => preModuleName, action_name => preActionName);

08)           DBMS_APPLICATION_INFO.set_module(module_name => 'Human Resources', action_name => 'Get Employees');

09)           SELECT first_name, last_name BULK COLLECT INTO fnlist_stack, lnlist_stack FROM employees;

010)          DBMS_APPLICATION_INFO.set_module(module_name => preModuleName, action_name => preActionName);

011)        EXCEPTION

012)          WHEN OTHERS THEN

013)          DBMS_APPLICATION_INFO.set_module(module_name => preModuleName, action_name => preActionName);

014)          raise_application_error (-20042,'ERROR in get_emp_instr');

015)        END get_emp_instr;

016)        /

Lines 4 and 5 set up holding locations for the previous module and action names.   In Line 7 we capture the current setting of module and action and put them into the defined variables.  Line 8 then sets the module and action name to what you want it set to for this code block.  Then we would do whatever it is we would do in the block.  Also this is not intended to set action every other line or something quite that detailed.  The idea is that a module is a “large” unit of work and that an action is a “significant” amount of work in a sub unit under the module.  I have found that trying to set it too quickly and the kernel seems to ignore some of the settings, or they go so fast they the change gets “lost”.   (I didn’t see the changes in the trace file.)

Lines 10 and 13 both set the module and action back to the previous settings.   Line 10 is certainly needed, since it not reset, when the code goes back to the calling environment the old setting would be lost.  However line 13 you may or may not want to have.  If it’s a locally handled exception and all it good to go then yes set it back to what it was, but it’s really an error state you likely will not want to reset them so these values are still set to what they were when the error occurred.   A little thought is required about resetting the module and action in an exception handler.

So there it is. With about 5-6 lines of code this block is instrumented and as it is executed I can monitor its executions (these module and action names get populated into several V$ views and such). And I could even trace this by turning on trace like this:

EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (service_name =>'HOTSOS', module_name =>'Human Resources', action_name => DBMS_MONITOR.ALL_ACTIONS, waits => TRUE, binds => TRUE);

Notice there is a system defined constant for all actions this way if I had a large code unit with the same module (for example a package) this would allow me to get any action used within the defined module.  Inside the trace file(s) I would see line like this:

*** MODULE NAME:(Human Resources) 2016-01-04 09:08:49.535

*** ACTION NAME:(get_emp) 2016-01-04 09:08:49.535

Of course you need to turn it off too so it would look like this (for every enable there is a disable):

EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE (service_name =>'HOTSOS', module_name =>'Human Resources', action_name => DBMS_MONITOR.ALL_ACTIONS);

A key point is that if you turn it on my module action you could end up with multiple trace files.  Because anyone running the named module/action combination will generate a trace, and trace files are still session generated.   So you can use trcsess to put them all together like this (this is an OS level utility, you run this at the OS command level):

trcsess output=hr_test.trc action="get_emp" module="Human Resources" *OP.trc

Pretty cool stuff eh? 

Oh and DBMS_APPLICAITON_INFO has been around since about 7.2 so don’t try this “it’s a new thing that’s why I don’ know about it” excuse.