SQL Session Tracing

1. Start a sqlplus session to the database, and enable sql tracing with this command:

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

The resulting trace file will be put in the USER_DUMP_DEST directory.
To find the location of this directory, as system, do:
SQL>show parameters user_dump_dest

2. Execute your code to reproduce the error/issue.

3. Turn off the trace using this command:

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

4. Please upload the trace file and the alert.log file


A 10046 trace creates an output dump file. Therefore, before you enable the 10046 trace event, you will need to set some database parameters that control the output of the dump file. These parameters include:

TIMED_STATISTICS – Must be set to TRUE to get timing information in your trace files.

MAX_DUMP_FILE_SIZE – Controls the maximum size of the trace file. For 10046 trace files, the default setting is generally too small.

USER_DUMP_DEST – This is the location the 10046 trace file(s) are written to.

STATISTICS_LEVEL – This should be set, at a minimum, to TYPICAL. When set to ALL, more information will be collected in the resulting trace files. All examples in this white paper are with STATISTICS_LEVEL set to TYPICAL.

Listener Tracing

We will need to trace a connection on both CLIENT ,SERVER and Listener endpoints to see what is happening.
Please collect traces for the good and the slow connections:

Please follow these steps:

1. Add the following parameters to the listener.ora:

TRACE_LEVEL_<listener_name>=16
TRACE_DIRECTORY_<listener_name>=<some_known_directory> (but not the root directory)
TRACE_FILE_<listener_name>=listener
TRACE_TIMESTAMP_<listener_name>=ON

Replace <listener_name> with the name of your listener, the default name is LISTENER.


2. Reload the listener (execute "lsnrctl reload <listener_name>").

3. Add the following parameters in the sqlnet.ora file on the CLIENT workstation:

TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=<some_known_directory> (but not the root directory)
TRACE_FILE_CLIENT=client
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON


4. Add the following parameters in the sqlnet.ora file on the SERVER:

TRACE_LEVEL_SERVER=16
TRACE_DIRECTORY_SERVER=<some_known_directory> (but not the root directory)
TRACE_FILE_SERVER=server
TRACE_TIMESTAMP_SERVER=ON


5. Attempt to reproduce the issue (connect from a client workstation) and verify if trace files were created.

>>> Please note that we need to group of traces, one when the connection is fine and one when there a delay.

6. Compress (in .zip or .tar.gz format) and send the trace files. Also please clarify in the name if it the good trace or the bad one.

7. Turn off tracing by setting all TRACE_LEVEL_<listener_name> = OFF and reload the listener.

8. Disable tracing by removing the TRACE lines from sqlnet.ora on the client and server