TRCANLZR (TRCA): Using Trace Analyzer Output (Doc ID 2087091.1)

PURPOSE

This document outlines how to use the output of the Trace Analyzer tool (also known as TRCANLZR or TRCA). For more details about Trace Analyzer, refer to:  本文档概述了如何使用Trace分析器工具(也称为TRCANLZR或TRCA)的输出(output)。有关Trace分析器的更多详细信息，请参阅：

Document 224270.1 TRCANLZR (TRCA): sql_trace/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces

或参阅：https://blog.csdn.net/u010692693/article/details/103072575

SCOPE

Users wanting to format SQL_TRACE/10046 output.   想要格式化SQL_TRACE/10046输出的用户。

DETAILS

Using Trace Analyzer Output

Trace Analyzer Output starts with a header that shows the version details for TRCA, the name of the file analyzed, it’s duration and the timestamp of the first and last calls in the trace:  Trace分析器输出以一个标题开头，该标题显示TRCA的版本详细信息，所分析文件的名称，持续时间以及Trace中第一次和最后一次调用的时间戳：

The rest of the report can be accessed using the links to jump to report subsections:  可以使用链接跳至报告小节来访问报告的其余部分：

• Glossary of Terms Used – This is a list of performance related terms used in the document and their explanation (starts minimized)   使用的术语表 –这是文档中使用的与性能相关的术语及其说明的列表（最小化开始）
• Response Time Summary – This section shows a breakdown of the time spent in the trace broken into sections such as CPU, Idle/Non-idle and Unaccounted Elapsed/Response time. It is useful to getting an overview of where time is being spent.  响应时间摘要 –此部分显示了trace中花费的时间的细分，分为CPU, Idle/Non-idle and Unaccounted Elapsed/Response time 等部分。概述花时间的地方很有用。
• Overall Time and Totals – Shows the overall time spent in Parse/Execute/Fetch with various associated statistics  总时间 –显示带有各种相关统计信息的Parse/Execute/Fetch所花费的总时间
• Non-Recursive Time and Totals – identifies the time spent in Parse/Execute/Fetch for Non-Recursive SQL 非递归时间总计 –标识非递归SQL在Parse/Execute/Fetch中花费的时间
• Recursive Time and Totals – identifies the time spent in Parse/Execute/Fetch for Recursive SQL  递归时间总计 –确定在递归SQL的Parse/Execute/Fetch中花费的时间
• Top SQL – Identifies SQL that exceed the:  TOP SQL –标识超出以下内容的SQL：
  • "Response Time Accounted-for" threshold of 10.0% of the "Total Response Time Accounted-for"  "Response Time Accounted-for"的阈值是"Total Response Time Accounted-for"的10.0％
  • "Elapsed Time" threshold of 10.0% of the "Total Elapsed Time". This is useful for identifying the most resource intensive SQL   "Elapsed Time"阈值是"Total Elapsed Time"的10.0％。这对于确定最耗资源的SQL很有用
  • "CPU Time" threshold of 10.0% of the "Total CPU Time".  "CPU Time"阈值是"Total CPU Time"的10.0％
  This is useful for picking out where to focus tuning or analysis efforts.  这对于选择聚焦于调整或分析工作的位置很有用。
• Non-Recursive SQL – This is a list of individual executions of non-recursive SQL in chronological order by first db call timestamp. This is useful for seeing the sequence of events occurring in the trace.  非递归SQL –这是按顺序按第一个数据库调用时间戳按时间顺序分别执行非递归SQL的列表。这对于查看trace中发生的事件顺序很有用。
• SQL Genealogy - Aggregate view of non-recursive SQL statements (depth = 0) and their recursive SQL at all depths. This is useful for seeing where particular recursive SQL is coming from  SQL族谱 -非递归SQL语句 (depth = 0)及其在所有深度的递归SQL的聚合视图。这对于查看特定的递归SQL的来源很有用
• Individual SQL - List of individual (Recursive & Non-Recursive) SQL in order of first appearance in trace. This is followed by full text and Time, Totals, Waits, Binds and Row Source Plan for each SQL statement in order of appearance with a heading of “Hash Value / SQL ID / Plan Hash Value” (if data dictionary information about the tables involved exists in the TRCA database then this will also be included)   单个SQL-按首次出现在trace中的顺序列出单个（递归和非递归）SQL。随后是每个SQL语句的全文和时间，总计，等待，绑定和行源计划，其显示顺序为标题为“Hash Value / SQL ID / Plan Hash Value” （如果涉及的表的数据字典信息）存在于TRCA数据库中，那么这也将包括在内）
• Overall Segment I/O Wait Summary – This section provides an overview of the I/O related waits seen in the trace  总体段I/O等待摘要 –本节概述了trace中与I/O相关的等待
• Hot I/O Blocks – This section shows the list of blocks with largest wait time or times waited.  热I/O块 –此部分显示等待时间最长或等待时间最多的块列表。
• Gaps in Trace – Provides information about what might be responsible for any gaps that are perceived in the trace.  Trace中的空白 –提供有关可能导致Trace中任何空白的信息。
• ORA errors in Trace – Shows any Oracle Errors detected in the trace   Trace中的ORA错误 –显示在Trace中检测到的任何Oracle错误
• Transactions Summary - List of sessions recognized in trace(s), including transaction counts per type.  事务摘要 -在跟踪中识别的会话列表，包括每种类型的事务计数。
• Non-default Initialization Params - List of non-default parameters (If data dictionary information from the target system exists in the TRCA database generating the report then this will reflect that. If not then the information will be from the TRCA database generating the report)   非默认初始化参数 -非默认参数列表（如果目标系统的数据字典信息存在于生成报告的TRCA数据库中，则将反映出来。如果不存在，则该信息将来自生成报告的TRCA数据库）
• Trace Header – Header information from the raw trace file  跟踪标题 –原始trace文件中的标题信息
• Tool Data Dictionary – Information about the TRCA dictionary  工具数据字典 –有关TRCA字典的信息
• Tool Execution Environment – Information about the TRCA Environment  工具执行环境 –有关TRCA环境的信息
• Tool Configuration Parameters – Information about the TRCA Configuration Parameters  工具配置参数 –有关TRCA配置参数的信息

TRCA Report Analysis   TRCA报告分析

As with most performance reports, analysis direction is largely dictated by the problem that is being investigated. The following suggestions are based upon a case where a trace has been gathered for a slow running session:   与大多数性能报告一样，分析方向在很大程度上取决于所研究的问题。以下建议基于针对运行缓慢的会话收集了trace的情况：

Top SQL   热门SQL

This section gives a good overview of where time is being spent in the trace from 3 different perspectives, i.e. SQL that exceed the:   本节从3个不同的角度（即超出以下范围的SQL）很好地概述了trace中花费的时间：

• "Response Time Accounted-for" threshold of 10.0% of the "Total Response Time Accounted-for"
• "Elapsed Time" threshold of 10.0% of the "Total Elapsed Time".
• "CPU Time" threshold of 10.0% of the "Total CPU Time".

These measures are useful for identifying the most resource intensive SQL statements:  这些措施对于识别最消耗资源的SQL语句很有用：

This is useful for picking out where to focus tuning or analysis efforts.  这对于选择聚焦于调整或分析工作的位置很有用。

By looking at these sections you can decide which SQL(s) are likely to be relevant and which are not. For example if we start with the "Response Time Accounted-for" section we can see 2 statements:  通过查看这些部分，您可以确定哪些SQL可能相关，哪些不相关。例如，如果我们从"Response Time Accounted-for"开始，则可以看到2条语句：

Being as one of these is idle for 99% of the time it is likely not this one and indeed, if we follow the Hash Value link, we can see that most of the time is spent waiting for ‘SQL*Net message from client’:   由于其中之一idle for 99%，因此很可能不是这样，实际上，如果我们遵循Hash Value链接，我们可以看到大部分时间都花在等待‘SQL*Net message from client’上：

Which is typically associated with waiting for input from the user keyboard.  通常与等待用户键盘的输入相关联。

Going back to the other SQL we can see that it is associated with 82.4% of the elapsed time and 99.5% of the CPU time:  回到另一个SQL，我们可以看到它与82.4％的elapsed time和99.5％的CPU time相关联：

Making it a much more likely candidate.   使它成为更有可能的候选人。

Following the Hash Value link for this, we get more details of the SQL involved. Firstly we get the full SQL text:  通过此Hash Value链接，我们可以获得所涉及的SQL的更多详细信息。首先，我们获得完整的SQL文本：

then this is followed by statistics and row source plan (from the trace) for the query:  然后是查询的统计信息和行源计划（来自trace）：

Finally you get bind variable information (if present), table and execution information.  最后，您将获得绑定变量信息（如果存在），表和执行信息。

This information can help focus on problem areas.  这些信息可以帮助您将重点放在问题领域。

Once that has been examined, you might be interested in seeing whether the SQL in question generates any recursive SQL and this can be determined in the "SQL Genealogy" section:   检查完该内容后，您可能会对查看所讨论的SQL是否生成任何递归SQL感兴趣，可以在"SQL Genealogy" 部分中确定这一点：

In this case there is no recursive SQL associated with Hash value 1608412308.  在这种情况下，没有与Hash value 1608412308关联的递归SQL。

Other report sections can be viewed depending on what the problem being encountered.  可以根据遇到的问题查看其他报告部分。

REFERENCES

NOTE:215187.1 - All About the SQLT Diagnostic Tool
NOTE:39817.1 - Interpreting Raw SQL_TRACE output