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.
- Document 376442.1 How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
The document covers enabling the event for
- session tracing
- tracing a process after it started
- adding the event to init.ora
- collecting trace using SQLT
- and more ...
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
- Document 1102801.1 How to Get 10046 Trace for Parallel Query
To learn more about Event 10046 and how to interpret the output, refer to
- Document 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
- Document 1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
- Document 760786.1 TKProf Interpretation (9i and above)
- Document 39817.1 Interpreting Raw SQL_TRACE output
- Document 224270.1 TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces
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