Oracle’s released a few new facilities to help with tracing in 10g, here’s a real quick wrap up of the most significant:
Using the new client identifier
You can tag database sessions with a session identifier that can later be used to identify sessions to trace. You can set the identifier like this:
begin
dbms_session.set_identifier('GUY1');
end;
You can set this from a login trigger if you don’t have access to the source code. To set trace on for a matching client id, you use DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE:
BEGIN
DBMS_MONITOR.client_id_trace_enable (client_id => 'GUY1',
waits => TRUE,
binds => FALSE
);
END;
You can add waits and or bind variables to the trace file using the flags shown.
Tracing by Module and/or action
Many Oracle-aware applications set Module and action properties and you can use these to enable tracing as well. The serv_mod_act_trace_enable method allows you to set the tracing on for sessions matching particular service, module, actions and (for clusters) instance identifiers. You can see current values for these usng the following query:
SELECT DISTINCT instance_name, service_name, module, action
FROM gv$session JOIN gv$instance USING (inst_id);
INSTANCE_NAME SERVICE_NA MODULE ACTION
---------------- ---------- ------------------------------ ------------
ghrac11 SYS$USERS
ghrac11 ghrac1 SQLNav5.exe
ghrac11 ghrac1 Spotlight On Oracle, classic 4.0
ghrac13 SYS$USERS [email protected]
v.mel.au.qsft (TNS
ghrac13 ghrac1 Spotlight On Oracle, classic 4.0
ghrac12 ghrac1 SQL*Plus
ghrac12 SYS$USERS [email protected]
v.mel.au.qsft (TNS
So to generate traces for all SQL*plus sessions that connect to the cluster from any instance, I could issue the following command:
BEGIN
DBMS_MONITOR.serv_mod_act_trace_enable
(service_name => 'ghrac1',
module_name => 'SQL*Plus',
action_name => DBMS_MONITOR.all_actions,
waits => TRUE,
binds => FALSE,
instance_name => NULL
);
END;
.
/
Tracing using sid and serial
DBMS_MONITOR can enable traces for specific sid and serial as you would expect:
SELECT instance_name, SID, serial#, module, action
FROM gv$session JOIN gv$instance USING (inst_id)
WHERE username = 'SYSTEM';
INSTANCE_NAME SID SERIAL# MODULE ACTION
---------------- ---------- ---------- ------------ ------------
ghrac11 184 13179 SQL*Plus
ghrac11 181 3353 SQLNav5.exe
ghrac13 181 27184 SQL*Plus
ghrac13 180 492 SQL*Plus
ghrac12 184 18601 SQL*Plus
BEGIN
dbms_monitor.session_trace_enable (session_id => 180,
serial_num => 492,
waits => TRUE,
binds => TRUE
);
END;
/
The sid and serial need to be current now – unlike the other methods, this does not setup a permanent trace request (simply because the sid and serial# will never be repeated). Also, you need to issue this from the same instance if you are in a RAC cluster.
Providing NULLs for sid and serial# traces the current session.
Finding and analyzing the trace
This hasn’t changed much in 10g; the traces are in the USER_DUMP_DEST directory, and you can analyze them using tkprof.
The trcsess utility is a new additional that allows you to generate a trace based on multiple input files and several other conditions.
trcsess [output=<output file name >]
[session=<session ID>]
[clientid=<clientid>]
[service=<service name>]
[action=<action name>]
[module=<module name>]
<trace file names>
output=<output file name>
To generate a single trace file combining all the entries from the SQL*Plus sessions I traced earlier, then to feed them into tkprof for analysis, I would issue the following commands:
[oracle@mel601416 udump]$ trcsess module='SQL*Plus' *.trc output=sqlplus.trc
[oracle@mel601416 udump]$ tkprof sqlplus.trc sqlplus.prf
TKPROF: Release 10.2.0.1.0 - Production on Wed Sep 27 14:47:51 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Comments