10g tracing quick start

in

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