Database Reference
In-Depth Information
entry format is the only one that is identical in Oracle9
i
and Oracle10
g
, TRCSESS has limited
backward compatibility to Oracle9
i
trace files. So far, everything has worked as you might expect.
The next section addresses pitfalls of using TRCSESS in a Shared Server environment.
TRCSESS and Shared Server
According to the documentation, TRCSESS caters for Shared Server environments (
Oracle
Database Performance Tuning Guide 10g Release 2
, page 20-6). However, there is a chance of
incorrect results when using TRCSESS in a Shared Server environment with any option apart
from session. When a shared server process begins servicing a session that has SQL trace enabled,
it only emits the session identifier (
V$SESSION.SID
) and session serial number (
V$SESSION.SERIAL#
).
Any other instrumentation entries are not written to the trace file repeatedly. The current
implementations of TRCSESS (releases 10.2.0.3 and 11.1.0.6) apparently do not keep track of
which instrumentation settings were made by a session. Instead, TRCSESS includes trace file
sections from other sessions in the output, merely because it does not encounter a new value
for service name, module, action, or client identifier. In order to work properly, TRCSESS
would need to see entries for service name, module, action, and client identifier each time a
shared server process services a new session. But since these entries are written only once, the
results obtained with TRCSESS may be incorrect. The test case below illustrates this. If Shared
Server is not enabled in your test environment, then you may enable it like this:
SQL> ALTER SYSTEM SET shared_servers=1;
System altered.
SQL> ALTER SYSTEM SET dispatchers='(PROTOCOL=TCP)(DISPATCHERS=1)';
System altered.
By using a single shared server process, I can make sure that several sessions will be present
in the same trace file. Next, logged in as a DBA, I enable tracing by service name, module, and
action using
DBMS_MONITOR
.
SQL> EXEC dbms_monitor.serv_mod_act_trace_enable('TEN.oradbpro.com', 'mod', 'act');
SQL> SELECT trace_type, primary_id, qualifier_id1, qualifier_id2, instance_name
FROM dba_enabled_traces;
TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 INSTANCE_NAME
--------------------- ---------------- ------------- ------------- -------------
SERVICE_MODULE_ACTION TEN.oradbpro.com mod act
For the remainder of the test I need two sessions, with SQL trace enabled in both. The first
session enables SQL trace manually.
$ sqlplus ndebes/
secret@ten.oradbpro.com
SQL> ALTER SESSION SET sql_trace=true;
Session altered.
The second session uses the instance service specified with the preceding call of
DBMS_
MONITOR
and also sets module and action to the values used in the call to
DBMS_MONITOR.SERV_
MOD_ACT_TRACE_ENABLE
. This enables
SQL_TRACE
. In the next code example, I have changed
the SQL*Plus setting for
SQLPROMPT
such that the reader can easily recognize which session
executes what.
