Application Instrumentation and End-to-End Tracing - Secrets of the Oracle Database - page 262

$ sqlplus ndebes/ [e-mail]
SQL> SET SQLPROMPT "MOD_ACT> "
MOD_ACT> exec dbms_application_info.set_module('mod','act');
MOD_ACT> SELECT server, service_name, module, action
FROM v$session WHERE sid=userenv('SID');
SERVER SERVICE_NAME MODULE ACTION
--------- ---------------- ---------- -------
SHARED TEN.oradbpro.com mod act
MOD_ACT> SELECT 'mod_act' AS string FROM dual;
STRING
-------
mod_act

Back to the first session. I select a string literal from DUAL . This serves the purpose of including
a unique string in the trace file, which may be searched for later.

SQL> SELECT 'not instrumented' AS string FROM dual;
STRING
----------------
not instrumented
SQL> ALTER SESSION SET sql_trace=false;
Session altered.

SQL trace in the first session is switched off now. Back to session 2. In order to delimit the
trace file section for extraction with TRCSESS and to switch off tracing, this session sets module
and action to empty strings.

MOD_ACT> exec dbms_application_info.set_module('','');

You may now go to the directory specified with the parameter BACKGROUND_DUMP_DEST 5 and
run TRCSESS.

$ trcsess output=mod_act.trc module=mod action=act ten1_s000_8558.trc
$ grep "not instrumented" mod_act.trc
SELECT 'not instrumented' AS string FROM dual

The output file created by TRCSESS includes the SELECT of the string literal by session 1,
although session 1 did not set module and action to the option values passed to TRCSESS. This
defect applies to TRCSESS releases 10.2 and 11.1. As stated earlier, the problem is that instru-
mentation entries are not written again when a shared server process services another session.
Following is an excerpt of a shared server process' trace file that confirms this (all instrumen-
tation entries are included):

1 *** ACTION NAME:(act) 2007-09-06 20:23:52.514
2 *** MODULE NAME:(mod) 2007-09-06 20:23:52.514
3 *** SERVICE NAME:(TEN.oradbpro.com) 2007-09-06 20:23:52.514

The session level parameter TRACE_FILE_IDENTIFIER , which makes it easier to identify trace files,
merely applies to dedicated server processes. These include the parameter's value in the trace file
name. Shared server processes ignore this parameter.

5.