Database Reference
In-Depth Information
Module Name
The module name is intended to convey the name of an application or larger module to the
DBMS. The default setting is
NULL
. SQL*Plus and Perl DBI automatically set a module name.
This example is from a SQL*Plus session:
*** MODULE NAME:(SQL*Plus) 2007-02-06 15:53:20.844
Action Name
An action name represents a smaller unit of code or a subroutine. A module might call several
subroutines, where each subroutine sets a different action name. The default setting
NULL
results in
a zero length action name.
*** ACTION NAME:() 2007-02-06 15:53:20.844
Client Identifier
Performance problems or hanging issues in three-tier environments, where the application
uses a database connection pool maintained by an intermediate application server layer, can
be extremely cumbersome to track down. Due to the connection pool in the middle tier,
performance analysts looking at V$ views or extended SQL trace files cannot form an associ-
ation between an application user reporting a slow database and the database session or
server process within the ORACLE instance serving a particular user. There is no way to find
out which SQL statements are run on behalf of the complaining end user—unless the applica-
tion is properly instrumented, which is something that has eluded me in my career as a DBA.
The client identifier is the answer to this dilemma. The package
DBMS_SESSION
provides a
means for an application to communicate an identifier that uniquely designates an application
user to the DBMS. This identifier becomes the value of the column
V$SESSION.CLIENT_
IDENTIFIER
. If SQL trace is enabled, this same identifier is also embedded in the SQL trace file.
The format is as follows:
*** CLIENT ID:(
client_identifier
)
YYYY-MM-DD HH24:MI:SS.FF3
Client_identifier
is the client identifier set by calling the procedure
DBMS_SESSION.SET_
IDENTIFIER
from the application code. To extract trace information for a certain client identifier
from one or more SQL trace files,
trcsess
clientid
=
client_identifier
can be used. The following
line shows an actual entry from a trace file. The client identifier used was ND. The entry was
written on February 6th, 2007.
*** CLIENT ID:(ND) 2007-02-06 15:53:20.844
The maximum length of a client identifier is 64 bytes. Strings exceeding this length are
silently truncated. When instrumenting applications with
DBMS_SESSION
, consider that the
procedure
DBMS_SESSION.CLEAR_IDENTIFIER
does not write a
CLIENT
ID
entry into the trace file,
leaving the client identifier in effect until it is changed with
DBMS_SESSION.SET_IDENTIFIER
.
When connection pooling is used, this may result in trace files where sections pertaining to
different client identifiers are not delineated. The solution consists of setting an empty client
identifier by passing
NULL
to the packaged procedure
DBMS_SESSION.SET_IDENTIFIER
instead of
calling the procedure
DBMS_SESSION.CLEAR_IDENTIFIER
.
