Turn on tracing with DBMS_MONITOR

DBMS_MONITOR turns on 10046 tracing in a session that is already connected, without changing the application. For WaitScope, turn it on with waits (waits => true) and without bind values (binds => false), right before the operation starts, and turn it off right after it ends.

Before you start

• Privilege. According to the Oracle tracing documentation, DBMS_MONITOR can only be called by a user with the DBA role. To turn on tracing in your own session, use DBMS_SESSION (in your own session).
• Access to the views. To find the session, you query V$SESSION, or GV$SESSION in RAC.
• Disk space. The trace grows in the trace folder on the database server while it is on: keep it on only during the operation.
• Reference. The parameters on this page are those of the DBMS_MONITOR reference for 19c.

Turn it on in one session (SID and serial#)

1. Find the session in V$SESSION by user, machine, program or module:

   select sid, serial#, username, machine, program, module, status
   from v$session
   where username = 'EXEMPLO';

2. Turn on tracing with the SID and serial# of the session:

   begin
     dbms_monitor.session_trace_enable(
       session_id => 123,
       serial_num => 4567,
       waits      => true,
       binds      => false);
   end;
   /

3. Check that it is on:

   select sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
   from v$session
   where sid = 123;

   Result: SQL_TRACE is ENABLED, SQL_TRACE_WAITS is TRUE and SQL_TRACE_BINDS is FALSE.

4. Run the slow operation in that session.
5. Turn off tracing:

   begin
     dbms_monitor.session_trace_disable(
       session_id => 123,
       serial_num => 4567);
   end;
   /

The serial# ensures that the call applies to that session, and not to another one that reuses the same SID later. To find the file that was written, follow find the trace file.

In your own session

When you are the one running the operation, turn on tracing in your session with DBMS_SESSION, which has the same waits, binds and plan_stat parameters:

begin
  dbms_session.session_trace_enable(waits => true, binds => false);
end;
/

-- run the slow operation

begin
  dbms_session.session_trace_disable;
end;
/

Turn it on by client_id

When the application uses a connection pool, the operation goes through different sessions. If the application sets an identifier with DBMS_SESSION.SET_IDENTIFIER, turn on tracing by that identifier. The value appears in the CLIENT_IDENTIFIER column of V$SESSION: use it exactly as shown there, with the same uppercase and lowercase letters.

begin
  dbms_monitor.client_id_trace_enable(
    client_id => 'EXEMPLO',
    waits     => true,
    binds     => false);
end;
/

Turn it off at the end of the operation:

begin
  dbms_monitor.client_id_trace_disable(client_id => 'EXEMPLO');
end;
/

Oracle writes one file per process that served the client_id. To see the operation in a single analysis, upload the files together (analyze together): the report shows each session and adds up their time. With a connection pool, the interval in which the session was serving another client_id may appear as unaccounted-for time between calls.

Turn it on by service, module and action

If the application connects through its own service and sets module and action with DBMS_APPLICATION_INFO, turn on tracing by that combination. The service is required; module and action narrow it further, and whatever you omit matches all values.

begin
  dbms_monitor.serv_mod_act_trace_enable(
    service_name => 'EXEMPLO_SRV',
    module_name  => 'EXEMPLO',
    waits        => true,
    binds        => false);
end;
/

Turn it off with the same service and the same module:

begin
  dbms_monitor.serv_mod_act_trace_disable(
    service_name => 'EXEMPLO_SRV',
    module_name  => 'EXEMPLO');
end;
/

This trace applies to the whole database, and instance_name restricts it to one instance. As with client_id, each process writes its own file.

Which values to use

In RAC

• SESSION_TRACE_ENABLE turns on tracing only in the instance you are connected to. Find the session's instance in the INST_ID column of GV$SESSION and connect to it before turning tracing on.
• CLIENT_ID_TRACE_ENABLE applies to all instances.
• SERV_MOD_ACT_TRACE_ENABLE applies to the whole database, or only to the instance given in instance_name.
• Each instance writes its files to the trace folder on its own server: look on every node that served the operation.

select inst_id, sid, serial#, username, machine, module
from gv$session
where username = 'EXEMPLO';

Check what is still on

Traces by client_id, by service and for the whole database appear in DBA_ENABLED_TRACES. At the end, the query should return no rows, or only what you want to keep:

select trace_type, primary_id, qualifier_id1, qualifier_id2,
       waits, binds, plan_stats, instance_name
from dba_enabled_traces;

Tracing turned on in a session does not appear in this view: check it in the SQL_TRACE column of V$SESSION, as in step 3 for a session.

Common problems

• The call to the package fails for lack of privileges. Ask the DBA to turn on tracing, or use DBMS_SESSION in your own session.
• The query shows SQL_TRACE as DISABLED. Check the SID and serial# and, in RAC, whether you are connected to the session's instance.
• Waits equal to 0 in the report Summary. Tracing was turned on without waits. Turn it on again with waits => true (10046 levels).
• Text not available: the cursor was opened before the trace started. The statement page shows this warning when tracing was turned on after the statement was parsed. Turn it on before the operation starts.
• The same operation left several files. Upload them all together (analyze together).

Next steps

• Find the trace file on the database server.
• Before uploading, see what a 10046 trace contains.
• Upload the file from the browser.