概述
本文介绍在SQL问题诊断过程中利用的工具SQLT。
SQLT
SQLTXPLAIN (SQLT) 是Oracle用于诊断SQL相关问题的工具。
SQLT 会根据用户指定的模式，连接到数据库，收集执行计划、基于成本的 Optimizer CBO 统计信息、Schema 对象元数据、性能统计信息、配置参数和会影响正在分析的 SQL 性能的其他元素。
SQLT安装
可通过官方MOS Document 215187.1 可以下载最新的SQLT安装包，下载后可参考安装包中sqlt_instructions.html的介绍进行安装。
以下是安装SQLT步骤。
例：（12c NONCDB @Linux）
1.对SQLT安装包（sqlt_latest.zip）解压
代码语言：javascript复制 [oracle@db12102 20160827]$ ls
sqlt_latest.zip
[oracle@db12102 20160827]$ unzip sqlt_latest.zip
Archive: sqlt_latest.zip
creating: sqlt/
creating: sqlt/doc/
...
inflating: sqlt/utl/xplore/xplore.pks
[oracle@db12102 20160827]$ ls
sqlt sqlt_latest.zip2.进入sqlt的路径，准备安装
代码语言：javascript复制 [oracle@db12102 20160827]$ cd sqlt
[oracle@db12102 sqlt]$ ls
doc input install run sqlt_instructions.html sqlt_instructions.txt utl3.执行sqcreate.sql进行安装
代码语言：javascript复制SQL> conn /as sysdba
Connected.
SQL> @install/sqcreate.sql在安装过程中需要指定的参数和各参数的含义如下：
3-1.连接标识符（当安装在一个可插拔数据库上时是必须的）
代码语言：javascript复制Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.
Optional Connect Identifier (ie: @PROD):3-2.SQLTXPLAIN 密码
代码语言：javascript复制Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN:3-3.SQLTXPLAIN 默认表空间
代码语言：javascript复制
The next step is to choose the tablespaces to be used by SQLTXPLAIN
The Tablespace name is case sensitive.
Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?
Type YES or NO [Default NO]: NO
... please wait
TABLESPACE FREE_SPACE_MB
------------------------------ -------------
EXAMPLE
USERS
Specify PERMANENT tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Default tablespace [UNKNOWN]: USERS
PL/SQL procedure successfully completed.
... please wait
TABLESPACE
------------------------------
TEMP
Specify TEMPORARY tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Temporary tablespace [UNKNOWN]: TEMP3-4. 应用程序用户 （发出要分析 SQL 语句的用户）
代码语言：javascript复制The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.
Main application user of SQLT: teacherwhat※也可以在安装该工具后添加其他的 SQLT 用户，方法为：授予他们角色 SQLT_USER_ROLE， 或通过使用提供的脚本 sqlt/install/sqguser.sql
例：
代码语言：javascript复制SQL> conn /as sysdba
SQL> @install/sqguser.sql scott3-5. 授权的 Oracle Pack。（T，D 或 N）
代码语言：javascript复制SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:
"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses
Oracle Pack license [T]: T4.如果需要的话，通过下面的脚本可以卸载 SQLT。
代码语言：javascript复制SQL> conn /as sysdba
SQL> @install/sqdrop.sql※卸载 SQLT 会同时移除 SQLT Repository 以及所有SQLT相关的内容。
SQLT的使用
执行方法的种类
SQLT的执行方法主要包括以下:
代码语言：javascript复制XTRACT：通过V$SQL、AWR的过去执行过的SQL内容，收集相关信息。
XECUTE：通过执行SQL，收集相关信息。
XPLAIN：不执行SQL，基于EXPLAIN PLAN FOR命令收集相关信息。（不推荐）
XTRXEC：该方法合并了XTRACT和XECUTE 的功能。实际上，XTRXEC连续执行了这两种方法。
XTRSBY：分析在 Data Guard 或备用只读数据库上执行的 SQL。
XPREXT：使用XTRACT同时禁用一些SQLT的特性，使之执行更快。
XPREXC：使用XECUTE同时禁用一些SQLT的特性，使之执行更快。执行方法
XTRACT 方法
通过V$SQL、AWR的过去执行过的SQL内容，收集相关信息。
1.通过V$SQL或AWR找到要收集数据的SQL的SQL_ID 或者HASH_VALUE。
代码语言：javascript复制SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';2.进入到sqlt的run路径下，执行sqltxtract.sql脚本收集相关信息
代码语言：javascript复制$ ls
doc input install run SQLT_installation_logs_archive.zip sqlt_instructions.html sqlt_instructions.txt utl
$ cd run
$sqlplus Username/Password
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]3.执行例：
代码语言：javascript复制[oracle@db12102 sqlt]$ ls
doc input install run SQLT_installation_logs_archive.zip sqlt_instructions.html sqlt_instructions.txt utl
[oracle@db12102 sqlt]$ cd run
[oracle@db12102 run]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 27 20:18:29 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn teacherwhat/teacherwhat
Connected.
SQL> select/* GATHER_PLAN_STATISTICS MONITOR */ count(*) from SQLMON where a='A';
COUNT(*)
----------
1000
SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE 'select/* GATHER_PLAN_STATISTICS MONITOR */ count(*)%'; 2 3
SQL_ID HASH_VALUE
------------- ----------
TEXT
--------------------------------------------------------------------------------
acsdndc43qs74 138109156
select/* GATHER_PLAN_STATISTICS MONITOR
SQL> START sqltxtract.sql acsdndc43qs74 SQLTXPLAIN
...
adding: sqlt_s28359_sqldx.zip (stored 0%)
#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collected info can be purged from the database using the following file :
... getting sqlt_s28359_purge.sql out of sqlt repository ...
SQLTXTRACT completed.相关的信息生成在了sqlt_s28359_sqldx.zip 中。
XECUTE 方法
通过执行SQL，收集相关信息;与 XTRACT方法相比，该方法提供的信息更为详细.
1.使用XECUTE 方法之前，创建一个包含 SQL 文本的文本文件。
如果 SQL 包括绑定变量，则您的文件必须包含绑定变量声明和赋值。
例：
代码语言：javascript复制target.sql
-----------------------
select/* GATHER_PLAN_STATISTICS MONITOR */ count(*) from SQLMON where a='A';
-----------------------2.执行sqltxecute.sql脚本收集相关信息
代码语言：javascript复制$cd sqlt/run
$sqlplus Username/Password
SQL> START [path]sqltxecute.sql [path]scriptname [sqltxplain_password]3.执行例
代码语言：javascript复制[oracle@db12102 run]$ vi target.sql
[oracle@db12102 run]$ more target.sql
select/* GATHER_PLAN_STATISTICS MONITOR */ count(*) from SQLMON where a='A';
[oracle@db12102 run]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 27 21:06:09 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> conn teacherwhat/teacherwhat
Connected.
SQL> START sqltxecute.sql target.sql SQLTXPLAIN
...
adding: sqlt_s28360_sqldx.zip (stored 0%)
#####
The SQLT has collected information and place it in a repository in the database, exported it and zip it.
The collecte
d info can be purged from the database using the following file :
... getting sqlt_s28360_purge.sql out of sqlt repository ...
SQLTXECUTE completed.其他方法
其他方法可以参考SQLT安装包中的【sqlt_instructions.html】中的介绍。
例如高级方法和模块(Advanced Methods and Modules)的XPLORE，以后有机会再介绍。
