如何针对性能问题收集10046 Trace (SQL_TRACE) 诊断（文档ID 376442.1）

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)

PURPOSE

This document outlines various procedures to effectively gather 10046 trace for use with Query Performance issues. For use by DBAs, Developers and Support personnel   本文档概述了可有效收集 10046 Trace 以用于查询性能问题的各种过程。供DBA，开发人员和支持人员使用

QUESTIONS AND ANSWERS

Gathering 10046 trace

Event 10046 is the standard method of gathering extended SQL_TRACE information for Oracle sessions.
Event 10046 是为Oracle sessions收集扩展的 SQL_TRACE 信息的标准方法。
For details of the event see:   有关事件的详细信息，请参见

Document 21154.1  EVENT: 10046 "enable SQL statement tracing (including binds/waits)"

For Query Performance issues the typical requirement is to record wait and bind variable information for queries. This is achieved using 10046 with level 12. The following examples outline how to set the event in various scenarios:   对于查询性能问题，典型的要求是记录等待并绑定查询的变量信息。这可以通过使用级别12的10046实现。以下示例概述了如何在各种情况下设置事件：

• Trace Location    Trace 位置
  
  11g R1 and above:   11g R1及更高版本
  
  With the introduction of the new diagnosability infrastructure introduced in Oracle Database 11g Release 1, traces and core files are placed in a location controlled by the DIAGNOSTIC_DEST initialization parameter.   随着Oracle Database 11g Release 1中引入的新的可诊断性基础结构，Traces and Core文件被放置在由DIAGNOSTIC_DEST初始化参数控制的位置。
  To show the location of the DIAGNOSTIC_DEST, the following command can be used:  以下命令查看

   SQL> show parameter diagnostic_dest

  Pre 11g R1:     11g R1之前的版本
  
  Event 10046 tracing will produce a trace file in the <Parameter user_dump_dest> for user processes and <Parameter background_dump_dest> for background processes.   Event 10046 跟踪将在<Parameter user_dump_dest>中为用户进程和在<Parameter background_dump_dest>中为后台进程生成Trace文件。
  To show the location of the user_dump_dest, the following command can be used:  以下命令查看

  SQL> show parameter user_dump_dest

  NOTE: Some examples include setting a 'tracefile_identifier' to assist with finding the resultant trace output.

  注意：一些示例包括设置'tracefile_identifier'以帮助查找Trace文件。
• Session Tracing    会话跟踪
  
  This tracing can be used where the session is accessible to the user prior to the start of the statement(s) to be traced.  此跟踪可用于在要跟踪的语句开始之前用户可访问会话的情况。
  To gather 10046 trace at the session level:   要在Session级别收集10046 Trace

  alter session set tracefile_identifier='10046';
  
  alter session set timed_statistics = true;
  alter session set statistics_level=all;
  alter session set max_dump_file_size = unlimited;
  
  alter session set events '10046 trace name context forever,level 12';
  
  -- Execute the queries or operations to be traced here --
  -- 执行此处要做的查询或操作 --
  
  select * from dual;
  exit;

  If the session is not exited then the trace can be disabled using:   如果未退出Session，则可以使用以下命令禁用跟踪：

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

  Note that if the session is not closed cleanly and tracing is disabled, then important trace information may be missing from the trace file. 
  请注意，如果未完全关闭Session并禁用了跟踪，则跟踪文件中可能缺少重要的跟踪信息。
  
   

  NOTE: "statistics_level=all" is set here so as to gather some level of statistics in cases where the parameter has been changed from the default and recommended level of "TYPICAL" (to BASIC). In order to diagnose performance issues, some level of statistics is required. A value of "ALL" may not be absolutely necessary but has been chosen over "TYPICAL" so as to have the most comprehensive information for diagnosis.

  注意：此处设置了"statistics_level=all"，以便在参数已从默认值和建议值"TYPICAL"(更改为BASIC)时收集某些统计信息。为了诊断性能问题，需要一定程度的统计信息。值"ALL"可能不是绝对必要的，但已选择了"TYPICAL"值，以获取最全面的诊断信息。
  
• Tracing a process after it has started      跟踪其他Session
  
  If trace from an existing session is required then oradebug can be used to attach to the session and initiate 10046 tracing.
  如果需要从现有会话进行跟踪，则可以使用oradebug附加到该会话并启动10046跟踪。
  1. The first step is to identify the session to be traced by some means:
     第一步是通过某种方式来识别要跟踪的会话：
     For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:  例如，在SQL*Plus中，启动一个会话以查找目标会话的OS进程ID(spid) ：

     select p.PID,p.SPID,s.SID
     from v$process p,v$session s
     where s.paddr = p.addr
     and s.sid = &SESSION_ID
     /

     SPID is the operating system Process identifier (os pid)   操作系统的进程ID
     PID is the Oracle Process identifier (ora pid)     Oracle进程ID
     
     If you do not know the Session ID then you can use a select similar to the following to help you identify the target session:   如果您不知道Session ID，则可以使用类似于以下内容的Select来帮助您识别目标Session：

     column line format a79
     set heading off
     select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
       s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
     from v$session s , v$process p
     where p.addr = s.paddr
     and s.username <> ' ';

     Document For multi threaded processes in 12c, use newly added column stid from v$process to find specific thread, as Oracle combines many processes into a single ospid.  To find the specific thread, use following sytax:  文档对于12c中的多线程进程，请使用 v$process 中新添加的列 stid 查找特定线程，因为Oracle将许多进程合并为一个 ospid。要查找特定线程，请使用以下语法：

     oradebug setospid <spid> <stid>

      

  2. Once the OS process id for the process has been determined then the trace can be initialised as follows:
     一旦确定了该进程的OS进程ID，就可以按以下方式初始化跟踪：
     Lets assume that the process to be traced has an os pid of 9834.  假设要跟踪的进程的OS pid为9834。
     Login to SQL*Plus as a dba and execute the following:   以dba 身份登录SQL*Plus并执行以下操作：

     connect / as sysdba
     oradebug setospid 9834
     oradebug unlimit
     oradebug event 10046 trace name context forever,level 12

     Remember to replace the example '9834' value with the actual os pid.  请记住将示例'9834'的值替换为实际的os pid。

  NOTE: It is also possible to attach to a session via oradebug using the 'setorapid'.   注意：也可以使用'setorapid' 通过oradebug附加到Session。

  In this case the PID (Oracle Process identifier ) would be used (rather than the 'SPID') and the oradebug text would change to:   在这种情况下，将使用PID(Oracle进程标识符)(而不是'SPID')，并且oradebug文本将更改为：

  connect / as sysdba
  oradebug setorapid 9834
  oradebug unlimit
  oradebug event 10046 trace name context forever,level 12

  Remember to replace the example '9834' value with the actual ora pid.  请记住将示例'9834'的值替换为实际的ora pid。
  
  To disable oradebug tracing once tracing is finished:   要在跟踪完成后禁用oradebug：

  oradebug event 10046 trace name context off

  Document For multi threaded processes in 12c, use newly added column stid from v$process to find specific thread, as Oracle combines many processes into a single ospid.  To find the specific thread, use following sytax:   文档对于12c中的多线程进程，请使用v$process中新添加的列stid查找特定线程，因为Oracle将许多进程合并为一个ospid。要查找特定线程，请使用以下语法：

          oradebug setospid <spid> <stid>oradebug unlimit

         The tracefile name will be something like <instance><spid>_<stid>.trc.

• Instance wide tracing       实例范围的跟踪
   

  NOTE: Please be cautious when setting system wide, as this will impact performance due to every session being traced. 注意：设置系统范围时请小心，因为这会影响每个会话，因此会影响性能。

  This setting will trace every session that is created after the parameter is set. Existing sessions will not be traced.
  此设置将跟踪设置参数后创建的每个会话。现有会话将不会被跟踪。
  Setting system-wide 10046 tracing can be useful for scenarios where a problem session is known to occur but cannot be identified in advance.  设置系统范围的10046跟踪对于已知发生问题会话但无法提前识别的情况很有用。
  In this situation, tracing can be enabled for a short period of time, the problem can then be reproduced and tracing disabled and the resultant traces searched for evidence of the problem.  
  在这种情况下，可以在短时间内启用跟踪，然后可以重现问题并禁用跟踪，并在结果跟踪中搜索问题的证据。
  System-wide tracing can be enabled as follows:  可以按以下方式启用系统范围的跟踪：

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

  The setting can be disabled in all sessions by using the following command:  

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

• Initialisation parameter setting  初始化参数设置
  
  This setting will trace every session in the instance when it is restarted.  此设置将在重新启动实例时跟踪实例中的每个会话。

  event="10046 trace name context forever,level 12"

  The setting can be disabled by removing the parameter and restarting the instance or by using an alter system command as follows:   可以通过删除参数并重新启动实例或使用如下的alter system命令来禁用该设置：

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

• Via a Logon Trigger   
  
  There may be some situations where it is necessary to trace the activity of a specific user. In this case a logon trigger could be used.   在某些情况下，有必要跟踪特定用户的活动。在这种情况下，可以使用登录触发器。
  An example is provided below:   下面提供了一个示例：

  CREATE OR REPLACE TRIGGER SYS.set_trace
  AFTER LOGON ON DATABASE
  WHEN (USER like '&USERNAME')
  DECLARE
  lcommand varchar(200);
  BEGIN
  EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
  EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
  EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
  EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END set_trace;
  /

  NOTE: In order to trace a session, the user executing the trigger needs to have been explicitly granted 'alter session' privileges. i.e.:   注意：为了跟踪会话，需要明确授予执行触发器的用户'alter session'权限。即：
  

  grant alter session to <USERNAME> ;

• Collecting Trace with SQLT    使用SQLT收集Trace
  
  Using SQLTXPLAIN with the "Xecute" option produces a 10046 trace as part of the SQLT output. As the name XECUTE implies, SQLT executes the SQL being analyzed, then it produces a set of diagnostics files (including a 10046 trace). See:
  将SQLTXPLAIN与"Xecute"选项一起使用会产生10046 Trace，作为SQLT输出的一部分。顾名思义，XECUTE执行要分析的SQL，然后生成一组诊断文件(包括10046 Trace)。

  Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly (Doc ID 215187.1)      也可参考文章：https://blog.csdn.net/u010692693/article/details/102976993

  Trace produced in this way will be included in the SQLT output package in the format:  以这种方式产生的跟踪将以以下格式包含在SQLT输出包中：

  sqlt_s12345_10046_execute.trc

  Where 12345 is the SQLT report id    其中12345是SQLT的report id 

• Tracing with DBMS_MONITOR     使用DBMS_MONITOR进行跟踪
  
  Also note that the DBMS_MONITOR package provides a number of methods of enabling trace. For more details see:
  还请注意，DBMS_MONITOR包提供了许多启用跟踪的方法。有关更多详细信息，请参见：也可以参考文章：https://blog.csdn.net/u010692693/article/details/103083214

  Document 293661.1 Tracing Enhancements In 10g Using DBMS_MONITOR
  Oracle Database PL/SQL Packages and Types Reference
  10g Release 2 (10.2)
  Part Number B14258-02
  Chapter 60 DBMS_MONITOR

• Alternative Trace Possibilities for Specific Scenarios    针对特定情况的替代Trace可能性
   

  Document 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
  Document 1274511.1 General SQL_TRACE / 10046 trace Gathering Examples   可参考文章：https://blog.csdn.net/u010692693/article/details/103083535
  Document 160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
  Document 371678.1 Capture 10046 Traces Upon User Login (without using a trigger)
  Document 1102801.1 How to Get 10046 Trace for Parallel Query    可参考文章：https://blog.csdn.net/u010692693/article/details/103083713
  Document 258418.1 Getting 10046 Trace for Export and Import    可参考文章：https://blog.csdn.net/u010692693/article/details/103084140

  
  If you are running PL/SQL procedures or packages then use of the PL/SQL profiler can be useful to determine where time goes while using PL/SQL routines.  如果您正在运行PL/SQL过程或程序包，那么使用PL/SQL profiler对确定使用PL/SQL例程的时间可能会很有用。
  Instructions on the use of the profiler can be found in the following article:
  可以在以下文章中找到有关使用profiler的说明：

  Document 243755.1 Implementing and Using the PL/SQL Profiler

• Trace Interpretation
   

  Document 199081.1 SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
  Document 39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output
  Document 224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046
  Document 41634.1 - TKProf Basic Overview
  Document 32951.1 - TKProf Interpretation (9i and below)
  Document 760786.1 - TKProf Interpretation (9i and above)
  Document 214106.1 - Using TKProf to compare actual and predicted row counts
  Document 199083.1 * Master Document SQL Query Performance Overview
  Document 398838.1 * FAQ: SQL Query Performance - Frequently Asked Questions

REFERENCES

NOTE:160124.1 - How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
NOTE:243755.1 - Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data
NOTE:75713.1 - Important Customer information about using Numeric Events
NOTE:39817.1 - Interpreting Raw SQL_TRACE output
NOTE:21154.1 - EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
NOTE:760786.1 - TKProf Interpretation (9i and above)
NOTE:293661.1 - Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above)
NOTE:214106.1 - Using TKProf to Compare Actual and Predicted Row Counts
NOTE:215187.1 - All About the SQLT Diagnostic Tool
NOTE:224270.1 - TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (NO LONGER SUPPORTED - Use SQLTXPLAIN sqltrcanlzr.sql)
NOTE:1274511.1 - General SQL_TRACE / 10046 trace Gathering Examples
NOTE:1102801.1 - How to Get 10046 Trace for Parallel Query
NOTE:371678.1 - Capture 10046 Traces Upon User Login (without using a trigger)
NOTE:41634.1 - TKPROF Basic Overview
NOTE:199081.1 - SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
NOTE:258418.1 - Getting 10046 Trace for Export and Import