Oracle SQLT（SQLTXPLAIN）工具安装及用法

开SR有时遇到难以分析的sql会遇到让上传sqlt报告，报告内容非常非常详细，如果是生产环境的语句不要直接发在公众场合，容易出安全事故。一般给的顺序是：实际执行计划 -> sql monitor -> sqlhc -> sqlt。

 

一、 SQLT是什么

SQLT全名叫SQLTXPLAIN，是Oracle的提供的一个免费的sql分析和诊断工具（926 KB）。

输入项为sql_id，输出项为一个诊断文件集合（.zip压缩包，可能有50M）。SQLT会收集sql执行计划、CBO数据、sql中对象的元数据、性能数据、参数设置等。由于sqlt报告过于复杂，在使用sqlt之前建议先用sqlhc（SQL Health Check）分析。

详情及下载地址参考 All About the SQLT Diagnostic Tool (文档 ID 215187.1)   

 

二、 SQLT安装

1. 创建sqlt用户

也可以给sqlt创建单独的永久表空间和temp表空间

create user sqlter identified by sqlter;

2. 解压sqlt安装包

unzip sqlt_latest.zip 
cd sqlt
ls
#目录结构如下
doc  input  install  run  sqlt_instructions.html  sqlt_instructions.txt  utl

3. 安装sqlt

cd sqlt/install
sqlplus / as sysdba
SQL> START sqcreate.sql

执行sqcreate.sql脚本后会要求输入一些信息：

...

 Specify optional Connect Identifier (as per Oracle Net)
 Include "@" symbol, ie. @PROD
 If not applicable, enter nothing and hit the "Enter" key.
 This connect identifier is only used while exporting SQLT
 repository everytime you execute one of the main methods.

 Optional Connect Identifier (ie: @PROD): @hxy   <-- 连接标识符

 PL/SQL procedure successfully completed.
 Define SQLTXPLAIN password (hidden and case sensitive). 
 Password for user SQLTXPLAIN:  <-- sqlt用户的密码
 Re-enter password:  <-- 再输一次
PL/SQL procedure successfully completed.

 ... please wait

 no rows selected

 Specify PERMANENT tablespace to be used by SQLTXPLAIN.
 Tablespace name is case sensitive.
 Default tablespace [UNKNOWN]: USERS  <-- sqlt用户使用的永久表空间，必须大写

PL/SQL procedure successfully completed.

 ... please wait

 TABLESPACE
 ------------------------------
 TEMP
 TEMP001 

Specify TEMPORARY tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

 Temporary tablespace [UNKNOWN]: TEMP001 <-- sqlt用户使用的temp表空间，必须大写
 PL/SQL procedure successfully completed. 

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: sqlter <--之前创建的专用用户

PL/SQL procedure successfully completed.

...
 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]: T <-- 选择拥有哪些ORACLE PACK的使用许可。T代表Oracle Tuning（默认）、D代表 Oracle Diagnostic、N代表没有

...

SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully. 

最后看到上面两句话代表已安装成功

 

4. 使用sqlt分析sql

根据安装输出的提示，使用前必须授予SQLT_USER_ROLE 角色的权限。

grant SQLT_USER_ROLE to sqlter; 

切换到sqlt解压路径的run目录下，执行sqltxtract.sql脚本

cd ../run
sqlplus / as sysdba
SQL> START sqltxtract.sql sql_id

#执行后需要按提示输入sql_id和sqlt用户的密码
Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)

Paremeter 2:
SQLTXPLAIN password (required)
Enter value for 2: sqlter

执行时间可能由几分钟到半小时左右，试sql复杂度而定，等着就好 

完成后会生成一个sql报告的压缩包 

一般主要是看main那个，里面有非常多信息

 

三、 SQLT卸载

cd sqlt/install
sqlplus / as sysdba
SQL> START sqdrop.sql

如果之前创建了单独的表空间，手动删除即可。

 

参考

All About the SQLT Diagnostic Tool (文档 ID 215187.1)    
SQL Tuning Health-Check Script (SQLHC) (文档 ID 1366133.1)
FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (文档 ID 1454160.1)
SQLT Main Report: Usage Suggestions (文档 ID 1922234.1)

https://www.cnblogs.com/feiyun8616/p/6707246.html