开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)