Database Reference
In-Depth Information
clientid=<clientid> clientid to be traced.
service=<service name> service to be traced.
action=<action name> action to be traced.
module=<module name> module to be traced.
<trace_file_names> Space separated list of trace files with wild card '*' supported.
In the dedicated server model, all trace file entries for a particular session are in a single
file. Shared Server and connection pooling environments are a different matter. In the Shared
Server model, trace file entries for a single session may be spread across as many files as shared
server processes configured. This is due to the fact that a user session is serviced by different
shared server processes from time to time. This makes it difficult to create a complete resource
profile for a session. Connection pooling environments are the worst of all, since a DBA cannot
form an association between an end user, who may have contacted him for support, and a
database session, unless the application is instrumented and sets a client identifier, which
allows the DBA to find out which database session is currently used on behalf of the end user.
To continue the example from the previous section, I will show how to extract the SQL
statements issued while the session was executing module “mod” and action “act” with TRCSESS.
Glancing back at the code excerpt in the section “JDBC End-to-End Metrics Sample Code” near
the beginning of this chapter, you would expect that merely the
SELECT
statement that retrieves
the session identifier is contained in the resulting trace file.
$ trcsess module=mod action=act *.trc
*** 2007-10-30 22:53:03.984
*** 2007-10-30 22:53:03.984
=====================
PARSING IN CURSOR #2 len=75 dep=0 uid=61 oct=3 lid=61 tim=392103604193 hv=536335290
ad='66afd944'
SELECT userenv('sid'), to_char(sysdate, 'Month dd. yyyy hh24:mi') FROM dual
END OF STMT
PARSE #2:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=392103604185
EXEC #2:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=392103608774
WAIT #2: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0
obj#=-1 tim=392103608842
WAIT #2: nam='SQL*Net message from client' ela= 52777 driver id=1413697536 #bytes=1
p3=0 obj#=-1 tim=392103661760
WAIT #2: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0
obj#=-1 tim=392103661904
FETCH #2:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=392103661941
WAIT #2: nam='SQL*Net message from client' ela= 233116 driver id=1413697536 #bytes=1
p3=0 obj#=-1 tim=392103895174
Note how all the application instrumentation entries including the client identifier, which
came after the action and module names, have been removed from the output of TRCSESS.
Unless a meaningful file name is used, it is not possible to tell how the file was processed. This
also implies that it is impossible to drill down from module and action to a specific client iden-
tifier. Calling TRCSESS with the option
session
leaves any other instrumentation entries intact.
Specifying any other option removes all the instrumentation entries and merely leaves the
timestamps that accompanied them in the resulting trace file. Since the session identification
