Oracle工具(Oracle Tools) – SQLT(SQLTXPLAIN)-CSDN博客

Oracle又提供一款非常强大的调优SQL语句的工具，叫SQLTXPLAIN，我们一般简称SQLT，目前最新版本为11.4.4.8。

中国Oracle博客上也有对SQLT的简介
背景
SQLTXPLAIN (简称SQLT) 是Oracle提供的一种用来诊断SQL语句调优问题的工具。通常，当用户遇到一个SQL语句调优问题时，Oracle Support会要求提供很多的信息，比如SQL语句，10046/10053 trace，对象统计信息，optimizer信息等等。这些信息的收集是非常繁琐的事情，而且需要用户具有深入的产品知识。由于信息收集不全，或者客户不知道如何收集，导致一些问题最终无法解决。Oracle提供了SQLT这个工具来解决这个问题。

功能
? 收集单条SQL语句的信息，包括：
  ○ SQL文本
  ○ 执行计划(explain)
  ○ 真实执行计划(row source operation)
  ○ 运行时的信息(时间，记录数等等)
  ○ 10046/10053 trace
? 收集影响optimizer的统计信息(包括SYSTEM statistics和Object statistics)。
? 收集所有可能会影响optimizer计算的参数：
  ○ 平台
  ○ 版本
  ○ NLS信息
  ○ 初始化参数
  ○ fix_control
  ○ gather_statistics_job
? 如果Oracle Tuning Packs是可以使用的话，自动调用SQL Tuning Advisor来生成优化报告。
? 自动导出相关数据以方便客户/Oracle Support/Oracle Development创建test case来重现客户的问题，加速问题的处理。
? 自动生成SQL Profile的脚本，来固定SQL的执行计划。

特点
? SQLT是用SQL和PL/SQL编写的，可以运行在各种不同版本操作系统的数据库。
? 代码都是非加密的，可以清楚的看到操作的内容。
? 不会泄露用户敏感数据。比如Column的最大/最小值还有histogram可以选择隐藏起来。
? 安装在自己的schema下，不会影响用户数据。
? 免费下载和使用，只需有一个可用的My Oracle Support license。

下载及使用
? 下载：下载文档 ID:215187.1中的附件（需登陆My Oracle Support）。目前SQLT有两个不同的版本，一个是基于9.2/10.1的，另一个是基于10.2以后的。
? 使用说明：解压缩文件夹中的sqlt_instructions.html或者SQLTXPLAIN.pdf。在解压缩文件夹下的doc目录下也可以找到相同的信息(PPT)。
在绝大多数的情况下，SQLT包含了诊断SQL语句调优问题的所有必要信息。因此推荐客户尽可能安装并用它来收集信息，以便于问题更有效率的解决。

官方原文介绍
SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT main methods input one SQL statement and output a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly.

Once installed, you can use SQLT to analyze a SQL statement by passing its text within a script (including bind variables), or by providing its SQL_ID.

SQLT main methods connect to the database and collect execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and other elements that influence the performance of the one SQL being analyzed.

SQLT may use the Oracle Diagnostic and/or the Oracle Tuning Packs if your site has a license for them. These two provide enhanced functionality to the SQLT tool. During SQLT installation you can specify if one of these two packages is licensed by your site. If none, SQLT still provides some basic information that can be used for initial SQL performance diagnostics.

参考文档

SQLT (SQLTXPLAIN) – Tool that helps to diagnose a SQL statement performing poorly [ID 215187.1]
FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions [ID 1454160.1]
How to Use SQLT (SQLTXPLAIN) to Create a Testcase Containing Application Data [ID 1465741.1]
How to Use SQLT (SQLTXPLAIN) to Create a Testcase Without Row Data [ID 1470811.1]

下载
点击这里下载适用于9i的版本、点击这里下载适用于10g以上的版本

安装
Installing SQLT
SQLT installs under its own schema SQLTXPLAIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.

Installation steps:
1. Uninstall a prior version (optional).
     This optional step removes all obsolete SQLTXPLAIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqdrop.sql

2. Execute installation script sqlt/install/sqcreate.sql connected as SYS.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqcreate.sql

During the installation you will be asked to enter values for these parameters:
1. Optional Connect Identifier.
     In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key.
2. SQLTXPLAIN password.
     Case sensitive in most systems.
3. SQLTXPLAIN Default Tablespace.
     Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.
4. SQLTXPLAIN Temporary Tablespace.
     Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.
5. Optional Application User.
     This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won't be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE or by using provided script sqlt/install/sqguser.sql
6. Licensed Oracle Pack. (T, D or N)
     You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

If a silent installation is desired, there are three options to pass all 6 installation parameters:
1. In a file.
     Executing first a script with pre-defined values, similar to sample script sqlt/install/sqdefparams.sql. Then use sqlt/install/sqcsilent.sql instead of sqlt/install/sqcreate.sql.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqdefparams.sql
     SQL> START sqcsilent.sql

2. In-line.
     Executing sqlt/install/sqcsilent2.sql instead of sqlt/install/sqcreate.sql. The former inputs the same 6 installation parameters but in-line.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqcsilent2.sql '' sqltxplain USERS TEMP '' T

3. Internal installation at Oracle.
     Executing sqlt/install/sqcinternal.sql instead of sqlt/install/sqcreate.sql. The former executes sqlt/install/sqdefparams.sql followed by sqlt/install/sqcsilent.sql.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqcinternal.sql

Uninstalling SQLT
Uninstalling SQLT removes the SQLT repository and all SQLTXPLAIN schema objects. The SQLTXPLAIN user also gets dropped. To uninstall SQLT simply execute sqlt/install/sqdrop.sql connected as SYS.
     # cd sqlt/install
     # sqlplus / as sysdba
     SQL> START sqdrop.sql

Upgrading SQLT
If you have a prior version of SQLT already installed in your system, you can upgrade SQLT to its latest version while partially preserving some objects of your existing SQLT repository. The new migrated SQLT repository can then be used to restore CBO statistics or to perform a COMPARE between old and new executions of SQLT.
To upgrade SQLT, simply do an installation without performing the optional uninstall step.
If the upgrade fails, then it is possible the prior SQLT version was too old to be upgraded. In such case please proceed to uninstall SQLT first, followed by a clean installation.

使用
介绍下使用方法，SQLT提供5个主要的方法：XTRACT、 XECUTE、 XTRXEC、 XTRSBY和XPLAIN，另外还有COMPARE、 TRCANLZR、 TRCAXTR、 TRCASET、 TRCASPLIT、 XTRSET等其他方法。

使用，都要使用安装的时候创建的用户：

XTRACT
        SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] SQL> START sqltxtract.sql 0w6uydn50g8cx SQL> START sqltxtract.sql 2524255098 XECUTE
        SQL> START [path]sqltxecute.sql [path]scriptname
        SQL> START run/sqltxecute.sql input/sample/script1.sql

XTRXEC
        SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] SQL> START sqltxtrxec.sql 0w6uydn50g8cx SQL> START sqltxtrxec.sql 2524255098 XTRSBY
        SQL> START sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [DB_LINK] SQL> START sqltxtrsby.sql 0w6uydn50g8cx V1123
        SQL> START sqltxtrsby.sql 2524255098 v1123

XPLAIN
        SQL> START [path]sqltxplain.sql [path]filename
        SQL> START run/sqltxplain.sql input/sample/sql1.sql

COMPARE
SQL> START [path]sqltcompare.sql [STATEMENT_ID 1] [STATEMENT_ID 2] SQL> START run/sqltcompare.sql 92263 72597 SQL> START run/sqltcompare.sql

TRCANLZR # sqlplus [application_user] SQL> START [path]sqltrcanlzr.sql [SQL Trace filename|control.txt] SQL> START run/sqltrcanlzr.sql V1122_ora_24292.trc
SQL> START run/sqltrcanlzr.sql control.txt

TRCAXTR # sqlplus [application_user] SQL> START sqltrcaxtr.sql [SQL Trace filename|control.txt] SQL> START sqltrcaxtr.sql V1122_ora_24292.trc
SQL> START sqltrcaxtr.sql control.txt

TRCASET # sqlplus [application_user] SQL> START sqltrcaset.sql

TRCASPLIT # sqlplus [sqlt_user] SQL> START [path]sqltrcasplit.sql [SQL Trace filename] SQL> START run/sqltrcasplit.sql V1122_ora_24292.trc

XTRSET # sqlplus [application_user] SQL> START sqltxtrset.sql List of SQL_IDs or HASH_VALUEs: 2yas208zgt5cv, 6rczmqdtg99mu, 8w8tjgac6tv12

这些方法的具体介绍和使用方法请阅读解压下载之后的包根目录下sqlt_instructions.html。

下面是我在测试环境(Win7 + Oracle RDBMS 11.2.0.1)进行的操作：

安装时六个地方交互，包括创建密码、指定表空间等，推荐创建独立表空间提供于SQLT使用，最小50M。 C:\>sqlplus / as sysdba

sys@LUOCS> start G:\app\Luocs\scripts\sqlt\install\sqcreate.sql 输出内容非常多，这里略。 安装完后，会自动切换到sqltxplain用户。 我以XTRACT方法为例实验下： 首先导出某个时间段的AWR报告，从中找到TOP 1 SQL，记录SQL_ID - 8szmwam7fysa3 然后我们运行SQLT，XTRACT方法用到的是sqltxtract.sql脚本 C:\>sqlplus sqltxplain/oracle

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 4 16:40:35 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

sqltxplain@LUOCS> start G:\app\Luocs\scripts\sqlt\run\sqltxtract.sql 8szmwam7fysa3 -- 省略N多行 test of sqlt_s37895_xtract_8szmwam7fysa3.zip OK Archive: sqlt_s37895_xtract_8szmwam7fysa3.zip Length Date Time Name --------- ---------- ----- ---- 135747 2012/11/04 16:43 sqlt_s37895_10053_i1_c0_extract.trc 3395 2012/11/04 16:43 sqlt_s37895_driver.zip 1159 2012/11/04 16:43 sqlt_s37895_export_driver.sql 10211 2012/11/04 16:43 sqlt_s37895_lite.html 13155 2012/11/04 16:43 sqlt_s37895_log.zip 747020 2012/11/04 16:43 sqlt_s37895_main.html 11925 2012/11/04 16:43 sqlt_s37895_readme.html 222 2012/11/04 16:42 sqlt_s37895_remote_driver.sql 192126 2012/11/04 16:43 sqlt_s37895_tc.zip 1255 2012/11/04 16:43 sqlt_s37895_tcb.zip 418 2012/11/04 16:43 sqlt_s37895_tcb_driver.sql 1089 2012/11/04 16:43 sqlt_s37895_tc_script.sql 197 2012/11/04 16:43 sqlt_s37895_tc_sql.sql 1750594 2012/11/04 16:43 sqlt_s37895_trc.zip --------- ------- 2868513 14 files File sqlt_s37895_xtract_8szmwam7fysa3.zip for 8szmwam7fysa3 has been created. SQLTXTRACT completed. OK，已经将分析结果压缩到sqlt_s37895_xtract_8szmwam7fysa3.zip文件，里面包括14个文件。这个打包文件有必要的话就提交给Oracle Support即可。 其他方法不做演示。