Choose the event 10046 level

Event 10046 is Oracle's extended SQL trace: the level determines what goes into the file in addition to the database calls. WaitScope needs the waits. Use level 8; level 12 also works, but it writes bind values, which WaitScope ignores.

Before you start

• alter session set events turns on the event only in the session you are in and requires the ALTER SESSION privilege. To turn it on in another session, use DBMS_MONITOR.
• Check the parameters that change the file beforehand (timed_statistics and max_dump_file_size).
• To find the file later, give it a name with tracefile_identifier (mark the file name).

The levels

Levels 1, 4, 8 and 12 are listed in the Event Level Settings table on the SQL Trace page of the Oracle documentation, and level 8 is the one the 19c tracing guide uses to write waits. Without waits, the profile cannot separate waits from everything else: the time spent waiting for disk, a lock or the application becomes unaccounted-for time, within and between calls (gaps between calls). In the Summary, Waits equal to 0 means a trace without waits.

When the plan goes into the file

The STAT lines, the plan with the figures for each operation, go into the file at the frequency set by the plan_stat parameter. The DBMS_MONITOR reference documents three values:

In alter session set events, the documented levels, 1, 4, 8 and 12, do not choose this frequency, and the default is enough for the report to show the plan. If you need the plan on every execution, turn on tracing with DBMS_MONITOR and plan_stat => 'ALL_EXECUTIONS' (which values to use).

Turn it on and off with ALTER SESSION

1. In the session that will run the operation, turn on the event:

   alter session set events '10046 trace name context forever, level 8';

2. Run the whole operation in that same session.
3. Turn off the event:

   alter session set events '10046 trace name context off';

Result: the session's trace file has the PARSE, EXEC, FETCH and WAIT lines of the operation. To find its path, use the V$DIAG_INFO query in find the file of your own session.

Do not turn on 10046 for the whole system: each session writes its own file, and the operation you care about gets lost among the others.

Parameters that change the file

Check the values in effect before turning tracing on:

select name, value
from v$parameter
where name in ('timed_statistics', 'statistics_level', 'max_dump_file_size');

In your session, adjust them with alter session:

alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;

In someone else's session, traced through DBMS_MONITOR, the instance value applies, which only changes with alter system and applies to all sessions: agree on it with the DBA. The details are in the Oracle reference for MAX_DUMP_FILE_SIZE and TIMED_STATISTICS.

Trace truncated in the middle

The report measures only what is in the file. Signs that the trace does not cover the whole operation:

• the Period in the Summary starts after or ends before the operation;
• a warning at the top of the report that ends in (trace started or cut in the middle of a call);
• statements with Text not available: the cursor was opened before the trace started.;
• the Time gaps between calls finding in the Diagnosis.

To avoid it: turn on tracing before the operation starts and turn it off after it ends, without turning it off in the middle; leave max_dump_file_size unlimited; and, if the application switches files along the way, upload all the files together (analyze together).

Next steps

• To turn on tracing in another session: turn on tracing with DBMS_MONITOR.
• To find the file: find the trace file.
• To upload: upload from the browser.