This part of the series is an extension to Part I and shows how
to set two more very important session environment variables to make tracing
more effective. Read on and learn how to set the module and action names.
In Part I of the Session Tracing Series we learned how to
set the CLIENT_IDENTIFIER session variable and how we could query it from the
V$SESSION view. We also covered how to look at the resources consumed in
real-time for that CLIENT_IDENTIFER so that we could determine if large amounts
of resources were attributed to a unique or group of client connections. If you
remember from Part I, we invoked a logon trigger whenever a session connected
and then queried the V$SESSION view. This gave us output similar to that of Listing
1. From this output, you can see a CLIENT_IDENTIFIER that is being set. I
changed the logon trigger to put in the host name instead of the IP address in
this example, and you can see that the module name did get populated for the
applications I used to connect from (SQL*Plus and Oracle Administrative
Assistant). Also, note that the action field is blank. Also notice that the
there are two connections using SQL*Plus from host FINE-ALE and one connection
from host PAULANER.
Listing 1
V$SESSION output
SQL> SELECT sid, client_identifier, action, module FROM V$SESSIONSID CLIENT_IDENTIFIER ACTION MODULE
—- —————————————– ——————– ——————
133 James?F.?Koopmann:PINEHORSEFINE-ALE SQL*Plus
135 James?F.?Koopmann:PINEHORSEFINE-ALE mmc.exe
149 James?F.?Koopmann:PINEHORSEFINE-ALE SQL*Plus
132 PAULANERAdministrator:PINEHORSEPAULANER SQL*Plus
The setting of the CLIENT_IDENTIFIER was of great importance
but if we want to get finer granularity on the actual amount of resources that
are being consumed within certain modules of code or within certain business
units of work, we need to look at the DBMS_APPLICATION_INFO package provided by
Oracle. Most applications will give some form of module name when you see
connections coming across but will leave the ACTION name unset. The setting of
a module name is fine for some circumstances. However, to get a real indication
of where in the code resources are being done, both fields should be considered
when writing applications. This naming will allow you as a DBA or developer the
ability to track down unique connections that you are concerned with in
real-time or for latter tracing runs. This granularity is highly desirable when
debugging a performance bottleneck and you will want to pin point which application
module is consuming the most resources through the MODULE name and what is
being done (ACTION) within the code. To do this, it is imperative, that the
applications set the name of the module and the action being performed each and
every time it enters a new module or the action of the transaction changes. The
module name could be the section of code or procedure being executed, while the
action could be a business unit of work or an individual statement. The purpose
is to be able to tie resources used to a unit of work about which you are
concerned.
Calling the DBMS_APPLICATION_INFO Package
Using the
DBMS_APPLICATION_INFO package is quite straightforward. A simple call to
SET_ACTION is all that is required to set the ACTION name. This call has the
form of:
DBMS_APPLICATION_INFO.SET_ACTION (action_name IN VARCHAR2);
Therefore, if you
wanted to set the ACTION to ‘LOGON TRIGGER’ you could construct the call as:
DBMS_APPLICATION_INFO.SET_ACTION('LOGON TRIGGER');
This action name should be very descriptive of the type of
action being done within the code and it is always best to set this before the
actual action is performed. That way when the action is being performed you
will see the ACTION in the V$SESSION view and know that it is currently being
done. When the transaction or specific action is complete within your code you
should set it back to NULL or to the next action to be performed. This setting to
NULL or a valid next transaction type is very important. If you do not adhere
to this pattern, future resources will be attributed to the last ACTION and you
will lose any true performance statistics.
The setting of the MODULE name is done through a call to the
SET_MODULE procedure and has the form of:
DBMS_APPLICATION_INFO.SET_MODULE( module_name IN VARCHAR2,
action_name IN VARCHAR2);
All of the suggestions around setting the ACTION apply to setting
the MODULE name as well. When setting this naming field, you want to set it
before the actual execution within the code is started and you want to make
sure to set the MODULE back to NULL or to the next MODULE to be executed so as
to not miss-represent any statistics encountered after the true execution of
the code is complete. As you might have recognized, this SET_MODULE procedure
allows for the setting of the ACTION name also. You should use this to set your
MODULE and ACTION name when first entering a new module of code but use the
SET_ACTION procedure after, in the module. This will limit some of the overhead
of setting these names, as I am sure there is some miniscule locking going on
behind the scenes. Therefore, for our logon trigger if I wanted to set the
actual MODULE and ACTION names it would look like the example in Figure 1. You
should note that this is quite an extreme example of setting these session
variables but I wanted to give you a flavor for how to do this. Notice that the
MODULE name was set to ‘LOGON_TRIGGER’ and the ACTION is to indicate a SELECT
from DUAL. Also, note that at the end of the trigger we set the MODULE and
ACTION back to NULL so that we are certain not to mess up statistical
collection. In addition, please note that I have put in a call to the DBMS_LOCK
package to sleep so that I could see this setting of the MODULE and ACTION
names.
Figure 1
Logon trigger to set client_identifier, module, and
action
CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
v_user_identifier varchar2(64);
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => ‘LOGON_TRIGGER’,
action_name => ‘SELECT FROM DUAL’);
SELECT SYS_CONTEXT(‘USERENV’, ‘OS_USER’)
||’:’||
SYS_CONTEXT(‘USERENV’, ‘HOST’)
INTO v_user_identifier
FROM dual;
DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
dbms_lock.sleep(5);
DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);
END;
/
Now when we query the V$SESSION view we can see the setting
of these variables through Listing 2. After the DBMS_LOCK.SLEEP call is
complete, the MODULE name returns to ‘SQL*Plus’ and the ACTION is NULL. I think
this is a function of just using SQL*Plus and you should not rely on Oracle
setting these back to the original call.
Listing 2
V$SESSION output after setting MODULE and ACTION names
SQL> SELECT sid, client_identifier, action, module FROM V$SESSIONSID CLIENT_IDENTIFIER ACTION MODULE
—- —————————————– ——————– ——————
133 James?F.?Koopmann:PINEHORSEFINE-ALE SELECT FROM DUAL LOGON_TRIGGER
135 James?F.?Koopmann:PINEHORSEFINE-ALE mmc.exe
149 James?F.?Koopmann:PINEHORSEFINE-ALE SQL*Plus
132 PAULANERAdministrator:PINEHORSEPAULANER SQL*Plus
Proper setting of the ACTION and MODULE name are imperative
for collecting statistics around specific code functions and transactions. Once
they are set you can begin getting those fine grain details about the area of
code that is performing poorly. Next time we will begin looking at how to pull that
fine grain information out of current sessions, to zero in on where we need to
concentrate our tuning efforts.
