Skip navigation

Database Support Blog

1 Post authored by: LajosV-Oracle

You've probably come across and/or was asked by Oracle Support to set
      EVENT 10046
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


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

 

Event10046.png

Welcome to the My Oracle Support Community! We highly encourage you to personalize your community display name to make your activity more memorable. Please see https://community.oracle.com/docs/DOC-1022508 for instructions.