All About the SQLT Diagnostic Tool (Doc ID 215187.1) 有关SQLT诊断工具的全部信息

MAIN CONTENT   主要内容

What is SQLTXPLAIN (SQLT)?    什么是SQLTXPLAIN(SQLT)?

SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise - ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.

SQLTXPLAIN，也称为SQLT，是Oracle服务器技术专家中心(ST CoE)提供的工具。SQLT输入一个SQL语句并输出一组诊断文件。这些文件通常用于诊断性能不佳的SQL语句。SQLT连接到数据库并收集执行计划，基于成本的Optimizer CBO统计信息，架构对象元数据，性能统计信息，配置参数以及影响所分析SQL性能的类似元素。

For tuning SQL, SQLT requires some level of expertise to get the most from it. For most issues we recommend that you start by checking the query using SQL Health Check and then progress to SQLT if you are unable to resolve the issue. For details of SQLHC see:

为了调优SQL，SQLT需要一定程度的专业知识才能充分利用它。对于大多数问题，我们建议您首先使用SQL运行状况检查检查查询，如果无法解决问题，请继续进行SQLT。有关SQLHC的详细信息，请参见：

Document 1366133.1 SQL Tuning Health-Check Script (SQLHC)

SQL调整运行状况检查脚本（SQLHC）

Licensing (SQLT requires no license and is FREE)  

许可（SQLT不需要许可，并且是免费的）

As in the title of this section, SQLT requires no license and is FREE.

SQLT can use data from the Oracle Diagnostic and/or the Oracle Tuning Packs if your site has a license for them. For more details refer to the licensing section in the SQLT FAQ:

Document 1454160.1 FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions

常见问题解答：SQLT（SQLTXPLAIN）常见问题

Download SQLTXPLAIN (SQLT)    下载SQLTXPLAIN (SQLT) 

10.2, 11.1, 11.2, 12.1, 12.2 18c_and 19c download
9.2 and 10.1 download

或者

https://pan.baidu.com/s/1YjwkblwekSLvhz_bsqhbww

Installation    安装

• For 10.2, 11.1, 11.2, 12.1. 12.2 18c and 19c download the SQLT tool from the sqlt.zip file attached and unzip the contents into a suitable folder.
   
• Read the SQLT Usage instructions:    使用说明

  Document 1614107.1 SQLT Usage Instructions

  
  These instructions (sqlt_instructions.html) are also included in the zip file.
• If the zip file appears corrupted while downloading, please retry with another browser. 
• If you encounter any installation issues, please refer to the following Community thread for assistance: SQLTXPLAIN: SQLT Installation Issues     安装问题的帮助
   
• For sample output, download output sample.   样本
   
• For 9.2 and 10.1 download the SQLT tool from the sqlt9i.zip. Read sqlt_instructions.txt included in zip file. Do not use this version of the tool on 10.2 and posterior versions .   不要在10.2和更高版本上使用此版本的工具。

SQLTXPLAIN REFERENCE MATERIAL     参考资料SQLTXPLAIN参考资料

How to get SQLT Installation Advice?    如何获得SQLT安装建议？

The best place to get SQLT installation advice is the My Oracle Support Community (MOSC). Refer to the following thread: SQLTXPLAIN: SQLT Installation Issues, where you can benefit from the shared experience of the community, ask questions and get help from others.

Troubleshooting SQLT Runtime or Installation Issues  

对SQLT运行时或安装问题进行故障排除

In the event that SQLT raises errors at runtime or during installation or has other installation related issues, refer to: 

Document 1670677.1 FAQ: Common SQLT (SQLTXPLAIN) Runtime/Installation Errors

常见问题解答：常见的SQLT（SQLTXPLAIN）运行时/安装错误

 As above, the My Oracle Support Community (MOSC) can also provide valuable assistance.

SQLTXPLAIN Usage Guide     使用指南

The SQLT usage guide can be found here:

Document 1614107.1 SQLT Usage Instructions      使用说明

Unless you plan to execute the SQL, you will need to know the SQL_ID or HASH_VALUE of the statement from an AWR or ASH report or you can select it from the database using the V$SQL view. See:

除非您计划执行SQL，否则您将需要从AWR或ASH报告中了解该语句的SQL_ID或HASH_VALUE，或者可以使用V$SQL视图从数据库中选择该语句

Document 1627387.1 How to Determine the SQL_ID for a SQL Statement    如何确定SQL语句的SQL_ID

Interpreting the SQLTXPLAIN Main report     解释SQLTXPLAIN主报告

You can find some suggestions about how you might be able to use the SQLT main report in the following document:

Document 1922234.1 SQLT Main Report: Usage Suggestions    使用建议

SQLTXPLAIN FAQ     常见问题

A FAQ for the SQLT tool can be found here:

Document 1454160.1 FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions

SQLTXPLAIN Video and Webinars

Additionally number of webcasts covering various SQLT related topics have been recorded, including topics such as:

• "Using SQLTXPLAIN to diagnose SQL statements performing poorly"
• "How to create in 5 minutes a SQL Tuning Test Case using SQLTXPLAIN".

The webinars can be found, along with many other recorded webcasts, here:

Document 740964.1 Advisor Webcast Archived Recordings

Related to the Testcase webinar, there are some articles outlining how to use SQLT Testcases here:

Document 1470811.1 How to Use SQLT (SQLTXPLAIN) to Create a Testcase Without Row Data
Document 1465741.1 How to Use SQLT (SQLTXPLAIN) to Create a Testcase Containing Application Data

BEST PRACTICES     最佳实践

Ask Questions, Get Help, And Share Your Experiences With SQLT

提出问题，获得帮助并分享您使用SQLT的经验

Would you like to explore SQLT further with other Oracle Customers, Oracle Employees, and Industry Experts?
We have various community threads available where you can ask questions, get help from others, and share your experiences with SQLTXPLAIN:

SQLTXPLAIN: SQLT Installation Issues      SQLT安装问题
SQLTXPLAIN: Dealing with Long Execution Times      处理执行时间长
SQLTXPLAIN: Dealing with Errors reported in SQLT MAIN report      处理SQLT MAIN报告中报告的错误
SQLTXPLAIN: Using SQLT on a Stand-by or Dataguard     在备用数据库或Dataguard上使用SQLT
SQLTXPLAIN: Interpreting and Understanding SQLT Output     解释和理解SQLT输出

SQLTXPLAIN (SQLT): General Discussion    一般讨论

Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Pro-Active Problem Avoidance and Diagnostic Collection

Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. SQLTXPLAIN is one of the tools that support recommend for collecting such diagnostics. For information on suggested uses, other proactive preparations and diagnostics, see:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

REFERENCES    参考

NOTE:1460440.1 - Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports
NOTE:465787.1 - How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g/12c
NOTE:1454160.1 - FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions
NOTE:1366133.1 - SQL Tuning Health-Check Script (SQLHC)
NOTE:749227.1 - * How to Gather Optimizer Statistics on 11g
NOTE:243755.1 - Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data
NOTE:1465741.1 - How to Use SQLT (SQLTXPLAIN) to Create a Testcase Containing Application Data
NOTE:1482811.1 - Best Practices: Proactively Avoiding Database and Query Performance Issues
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:781927.1 - Performance Tuning Guidelines For Siebel CRM Application On Oracle Database
NOTE:1477599.1 - Best Practices: Proactive Data Collection for Performance Issues
NOTE:1470811.1 - How to Use SQLT (SQLTXPLAIN) to Create a Testcase Without Row Data
NOTE:1322888.1 - pscbo_stats - Improving Statistics in Oracle RDBMS for PeopleSoft Enterprise