Monitoring activity in a busy database is easy from a session perspective; we can see the SQL statements that are being executed and a raft of other details. However, understanding the particular business process that’s being executed can be difficult, especially when various processes execute the same statements.
Oracle provides mechanisms to help us in this regard by providing two session level variables; module and action. The supplied package DBMS_APPLICATION_INFO provides routines for setting these variables; SET_MODULE and SET_ACTION. The documentation for DBMS_APPLICATION_INFO notes the purpose of the module and action to be:
“Your applications should set the name of the module and name of the action automatically each time a user enters that module. The module name could be the name of a form in an Oracle Forms application, or the name of the code segment in an Oracle Precompilers application. The action name should usually be the name or description of the current transaction within a module.”
The reality is that the settings are session-based string variables that can be set as required. The module can be up to 48 bytes and the action can be up to 32 bytes. I typically adopt one of two strategies for setting these variables:
Make the module the name of the executing package and the action the name of the routine within the paackge
or
Set the module to name of the business process and the action to be a combination of the executing package and package routine.
The particular strategy adopted is based on which one I believe to be most effective for the situation that presents itself. If the particular process is predominantly self-contained in a single package then I’ll use the former. If the process is involved and spread out across multiple packages then I’ll adopt the latter. The action may not be set for every routine, especially if the routine is a “helper” style of routine as opposed to a core contributor of the overall process.
So, how can we use these settings? Well, they’re exposed in the v$session view, which is typically the first place people look to figure our what’s running in the database. GUI tools such as SQL Developer and TOAD display the module and action settings in their session monitor windows. From a performance tuning perspective, as of Oracle 10g it’s possible to invoke SQL Trace based on the module and action values. This is a particularly nice feature if you have a complex multi-tiered application with connection pooling that makes it hard to identify specific end-user activity at the database level.
Let’s look at a simple script that illustrates how the module and action settings might be used. The script outlines an ETL process that obtains the source data, cleans it, transforms it and loads it into a destination. Rather than actually performing these tasks we’ll just use placeholders.
SET ECHO ON
SET SERVEROUPTUT ON
-- clear any existing settings for module and action
-- before we run the script
EXEC dbms_application_info.set_module (NULL,NULL)
DECLARE
-- a routine that obtains the module and action and outputs them
-- using dbms_output, along with a message
PROCEDURE display (p_msg IN VARCHAR2)
AS
l_module_name VARCHAR2(48);
l_action_name VARCHAR2(32);
BEGIN
dbms_application_info.read_module (module_name => l_module_name
,action_name => l_action_name);
dbms_output.put_line (NVL(l_module_name,'<>') || ':' ||
NVL(l_action_name,'<>') || ' - ' ||
p_msg);
END display;
-- simple stubs that do nothing except set the action
PROCEDURE read_data
AS
BEGIN
dbms_application_info.set_action ('read_data');
display ('Inside read_data');
END read_data;
PROCEDURE clean_data
AS
BEGIN
dbms_application_info.set_action ('clean_data');
display ('Inside clean_data');
END clean_data;
PROCEDURE transform_data
AS
BEGIN
dbms_application_info.set_action ('transform_data');
display ('Inside transform_data');
END transform_data;
PROCEDURE load_data
AS
BEGIN
dbms_application_info.set_action ('load_data');
display ('Inside load_data');
END load_data;
BEGIN
display ('Start');
dbms_application_info.set_module (module_name => 'ETL Process'
,action_name => NULL);
read_data;
clean_data;
transform_data;
load_data;
display ('End');
END;
/
The script produces the following output:
<>:<> - Start ETL Process:read_data - Inside read_data ETL Process:clean_data - Inside clean_data ETL Process:transform_data - Inside transform_data ETL Process:load_data - Inside load_data ETL Process:load_data - End PL/SQL procedure successfully completed.
We can see easily that the module and action settings are being set as expected.
Despite the simplicity behind setting the variables there are some points to consider. The module and action settings stay set until until changed to some other value or the session terminates. So, if a process fails to set the variables then they remain with whatever their current values might be. This means that if your application uses connection pooling then potentially the settings may be retained across different application sessions.
The retention of the settings means that if routines modify the setting then they remain in effect once the routine exits. So, if proc_a calls proc_b and proc_b modifies the action setting, then any processing performed by proc_a after the call to proc_b will be done under the action that proc_b set. Let’s modify the previous example to illustrate this. Instead of calling the various procedures in sequence, our new process requires that the transform_data routine invokes the clean_data routine. I’ve modified the code to display the module and action setting before and after the call to clean_data:
SET ECHO ON
SET SERVEROUPTUT ON
-- clear any existing settings for module and action
EXEC dbms_application_info.set_module (NULL,NULL)
DECLARE
-- a routine that obtains the module and action and outputs them
-- using dbms_output, along with a message
PROCEDURE display (p_msg IN VARCHAR2)
AS
l_module_name VARCHAR2(48);
l_action_name VARCHAR2(32);
BEGIN
dbms_application_info.read_module (module_name => l_module_name
,action_name => l_action_name);
dbms_output.put_line (NVL(l_module_name,'<>') || ':' ||
NVL(l_action_name,'<>') || ' - ' ||
p_msg);
END display;
-- simple stubs that do nothing except set the action
PROCEDURE read_data
AS
BEGIN
dbms_application_info.set_action ('read_data');
display ('Inside read_data');
END read_data;
PROCEDURE clean_data
AS
BEGIN
dbms_application_info.set_action ('clean_data');
display ('Inside clean_data');
END clean_data;
PROCEDURE transform_data
AS
BEGIN
dbms_application_info.set_action ('transform_data');
display ('Inside transform_data, before clean_data');
clean_data;
display ('Inside transform_data, after clean_data');
END transform_data;
PROCEDURE load_data
AS
BEGIN
dbms_application_info.set_action ('load_data');
display ('Inside load_data');
END load_data;
BEGIN
display ('Start');
dbms_application_info.set_module (module_name => 'ETL Process'
,action_name => NULL);
read_data;
transform_data;
load_data;
display ('End');
END;
/
This produces the following output:
<>:<> - Start ETL Process:read_data - Inside read_data ETL Process:clean_data - Inside clean_data ETL Process:clean_data - Inside transform_data ETL Process:load_data - Inside load_data ETL Process:load_data - End PL/SQL procedure successfully completed.
We can see from the above output that the “Inside transform data” entry has the action “clean_data”, which was what the “clean_data” routine set it to. This is probably not what is intended. So, how do we manage this?
One strategy is to simply to be strict as to which routines do and do not alter the module and action settings. Routines that call others should “know” whether the module or action settings will be modified by those routines and, if necessary, revert the values once the call has completed. So, in the above example, the transform_data routine would need to reset the action after the call to clean_data. Obviously this may not be as easy to achieve, especially in a large environment with routines that are used by many different processes.
Another strategy, and to date it’s my preferred one, is for any routine that changes the module and / or action setting to reset them back to their previous values prior to exit. However, this too isn’t as easy as it may appear. To start with it requires programming discipline to ensure the current values are saved and then restored on exit. Failure to do so will not result in any obvious error except for the fact that the settings will no longer reflect the expected values. Only specific testing or close monitoring will pick this up. Also, routines have two exit mechanisms; normal completion of the main body and exception exit. Exit of a routine via an exception will either need to restore the values, via an exception handler, or accept the corruption of the settings in the event of an exception.
Can we make the handling of the module and action settings easier for us? The next post looks at a small package that helps me…
Download scripts shown in this post.
Mark Hoxey 
Pingback: SQL Monitor | Mark Hoxey