Optimize Distributed Workload - Expert Oracle RAC Performance Diagnostics and Tuning - page 520

Apart from the basic SQL-level trace information, additional information such as wait events encountered
(collected by default), bind variables and values used, and so forth, can also be collected. For example

EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (
SERVICE_NAME => 'SRV1',
MODULE_NAME => 'ORDERS ',
ACTION_NAME => DBMS_MONITOR.ALL_ACTIONS,
WAITS => TRUE,
BINDS => TRUE);

■ the SERV_MOD_ACT_TRACE_ENABLE utility generates trace files similar to the trace files generated using event
10046 at level 1. enabling wait events and binds will be similar to generating tracing using 10046 at level 12.

Note

Once these procedures are executed on the database server, the trace files are generated in the USER_DUMP_DEST
directory on the respective instances. Oracle generates one trace file for every session connecting to the database
using the service SRV1 . The trace files can then be consolidated based on different criteria.
Based on the example, the trace file will contain information (SQL statements, wait events encountered, bind
variables, and bind values). This trace information can be

tkprof (Transient Kernel Profiler) utility. • Analyzed directly using the

tkprof ssky1.ora.*.trc trcSRV1.prf explain=bmf/bmf table=bmf.temp sys=noFo

• Scanned through and extracted by action type using the

trcsess utility. Once these have been
extracted into a single file, it can be analyzed using the tkprof utility.

trcsess output=trcMixed.trc service=SRV1 module='ORDERS' action=Mixed ssky1_ora_*.trc

trcsess [output=<output file name >] [session=<session ID>] [clientid=<clientid>]
[service=<service name>] [action=<action name>] [module=<module name>] <trace file names>

Following are the descriptions for the various parameters used in the trcsess utility:
<output file name> Output destination default being standard output.
<sessionID> Session to be traced. Session ID is a combination of session index and session
serial number.

<clientid> To be traced.
<service name> Service to be traced.
<action name> Action to be traced.
<module name> Module to be traced.
<trace file names> Space separated list of trace files with wild card '*' supported.
The following trcsess command will extract trace information from the trace file that pertains to service SRV1
but contains all modules and actions:

trcsess output=trcSRV1.trc service=SRV1 ssky1_ora_*.trc