You've probably come across and/or was asked by Oracle Support to set
to enable SQL statement tracing while investigating a SQL related issue with your Oracle Database.
The 10046 trace is the equivalent of setting SQL_TRACE=TRUE. The advantage of using the event is that additional details may be written to the trace file depending on the level specified with the event. This event is often requested to be set by Oracle Support when gathering information for specific SQL issues and particular when investigating and/or diagnosing performance issues.
For Query Performance issues the typical requirement is to record wait and bind variable information for queries. This is achieved using 10046 with trace level 12.
The following document outlines various methods to gather 10046 trace in order to investigate Query Performance issues for a serial query.
For diagnosing performance issues with a parallel query, most of these instructions from the above document are also valid, however there are some differences. Those differences are outlined in
To learn more about Event 10046 and how to interpret the output, refer to
10046 trace is collected by certain methods of SQLT that execute the problem query. SQLT is an Oracle database support tool to diagnose SQL statements performing poorly. You can download SQLT individually or as part of the Support Tools Bundle Trace File Analyzer Collector (TFA):
Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly
Document 1513912.2 TFA Collector - Tool for Enhanced Diagnostic Gathering