Oracle 如何使用 SQLT 进行 SQL 调优
作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）
如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 如何使用 SQLT 进行 SQL 调优，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！
SQLT：SQLTXPLAIN，也被称为 SQLT，是由 Oracle 服务器技术中心- ST CoE 提供的工具。SQL main methods 输入一条 SQL 语句并输出一组诊断文件。这些文件通常用于诊断执行不良的 SQL 语句。SQL 连接到数据库并收集执行计划、基于成本的Optimizer CBO 统计信息、模式对象元数据、性能统计信息、配置参数和影响所分析SQL的性能的类似元素。对于 tuning SQL, SQLT 需要一定程度的专业知识才能最大限度地利用它。对于大多数问题，我们建议您首先使用 SQL 运行状况检查(SQLHC：SQL Health Check)检查查询，如果无法解决问题，则转到 SQLT。
SQLT main methods 连接到数据库并收集执行计划、基于成本的优化器 CBO 统计信息、架构对象元数据、性能统计信息、配置参数以及影响正在分析的一个 SQL 的性能的其他元素。
在安装此工具期间，将创建 2 个用户 SQLTXADMIN 和 SQLTXPLAIN 以及一个角色
SQLT_USER_ROLE。sql 存储库由用户 SQLTXPLAIN 拥有。每次使用任何 main 方法时，sql 用户都需要提供
SQLTXPLAIN 密码。SQLTXPLAIN 用户被授予以下系统权限:
• CREATE SESSION
• CREATE TABLE
PL/SQL包和视图的 SQL 集由用户SQLTXADMIN拥有。该 SQLTXADMIN 用户被锁定并由随机密码标识。SQLTXADMIN 被授予以下系统特权:
• ADMINISTER SQL MANAGEMENT OBJECT
• ADMINISTER SQL TUNING SET
• ADVISOR
• ALTER SESSION
• ANALYZE ANY
• SELECT ANY DICTIONARY
• SELECT_CATALOG_ROLE
所有的 SQL 用户在使用任何main方法之前都必须被授予 SQLT_USER_ROLE。该 SQLT_USER_ROLE 角色被授予以下系统权限:
• ADVISOR
• SELECT_CATALOG_ROLE
注意：不建议使用 SYS 或其他 DBA 账户来运行主要方法，因为收集可能会失败。授予 SQLT_USER_ROLE 并运行主要方法的最佳用户是应用程序的所有者。
如果必须使用 SYS 或其他 DBA 账户，那么在 12c 中，由于 PL/SQL 的安全模型的更改，需要手动执行额外的授权。要解决此更改，需要在 SYS 或 DBA 账户上授予 SQLTXADMIN 用户 INHERIT PRIVILEGES 继承特权。
GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;
下载 SQLT
MOS 下载地址：Download SQLTXPLAIN (SQLT) (Doc ID 215187.1)
sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip，也可添加我个人微信【JiekeXu_DBA】获取。
墨天轮下载地址：https://www.modb.pro/doc/86093
安装方法
unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
以 SYS 用户执行 ./sqlt/install/sqcreate.sql 脚本，需要输入以下信息。
这里说一下 Oracle pack license 这里，SQLT 本身不需要 licence，输入 T 的话收集的结果会包含 sql tuning 结果，输入 D 的话会包含 awr 报告信息，输入 N 这里就不包含这两项信息。如果不想使用 SQLT 的话可以使用脚本 ./sqlt/install/sqdrop.sql 直接卸载。
安装过程示例
这里以单机 12.2.0.1 多租户环境为例，首先进入到容器 JIEKEXUPDB1 下，可创建 SQLT 用户 SQLTXPLAIN，也可以不用建，跑脚本时自动创建 SQLTXPLAIN 用户。
然后执行脚本 @sqcreate.sql 输入连接串，创建 SQLT 用户的密码以及默认表空间等信息。
输入 T 后稍等一会当看到如下信息说明安装成功。
SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.
然后可以把 SQLT 角色 SQLT_USER_ROLE 给予其他业务用户，例如 SCOTT 等其他业务用户。
涉及到的主要 SQL
SYS@JiekeXu> alter session set container=JIEKEXUPDB1;SYS@JiekeXu> create user SQLTXPLAIN identified by SQLTXPLAIN; --当然也可以不用提前创建用户SYS@JiekeXu> grant CREATE SESSION,CREATE TABLE TO SQLTXPLAIN;SYS@JiekeXu> @sqcreate.sql adding: 221113152749_01_sqcreate.log (deflated 87%)Optional Connect Identifier (ie: @PROD): @JIEKEXUPDB1 <--- 输入 TNS 连接名
Password for user SQLTXPLAIN: <--- 输入专用用户密码Re-enter password: <--- 再次输入专用用户密码
Type YES or NO [Default NO]: YES <--- 输入专用用户表空间和临时表空间名，大写Default tablespace [USERS]: USERSTemporary tablespace [TEMP]: TEMPMain application user of SQLT: SQLTXPLAIN <--- 输入专用用户名，大写
"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 <--- 输入 license T SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.
SYS@JiekeXu> GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;SYS@JiekeXu> grant SQLT_USER_ROLE to sys;SYS@JiekeXu> grant SQLT_USER_ROLE to SCOTT;
主要的执行方法一般是 sqltxtract.sql 加 sqlid, 输入 SQLT 密码，然后便会生成 sqlt+日期+sqlid 结尾的 zip 包，解压后内容很多,我们一般情况下只需要关注 sqlt*lite.html 和 sqlt*main.html 结尾的文件就好，sqltlite.html 算是轻量级的 SQLT，sqltmain.html 是详细的信息。XTRACT Method、XECUTE Method 等其他方法可参考 SQLT 的安装介绍文档：sqlt_instructions.html。
SCOTT@jiekexupdb1> select count(*) from scott.t a,scott.test b where a.object_id=b.object_id /*JIekeXu*/;
COUNT(*)---------- 72783SCOTT@jiekexupdb1> select sql_id,sql_text from v$sql where sql_text like '%JIekeXu%' and sql_text not like '%like%';
SQL_ID-------------SQL_TEXT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------4mvsgjurg63fdselect count(*) from scott.t a,scott.test b where a.object_id=b.object_id /*JIekeXu*/
SCOTT@jiekexupdb1>@/home/oracle/tmp/SQLT/sqlt/run/sqltxtract.sql 4mvsgjurg63fd
--然后输入 SQLT 密码即可。
13:57:03 SYS@test> @/u01/soft/SQLT/sqlt/run/sqltxtract.sql 66jty4hfyt8mh SQLTXPLAIN --也可以直接跟 sqltxplain 密码再次说明不建议使用 SYS 或其他 DBA 账户来运行 main 方法，因为集合可能会失败。
授予和运行 main 方法的最佳用户是应用程序的所有者。
执行期间可查看这个视图监控执行过程：
SELECT * FROM SQLTXADMIN.sqlt$_log_v;
然后会生成一个以 sqlt+日期+sqlid_S.zip 的文件，sqlt_20221116_1428_60jj9axkt0v9u_S.zip 解压后有如下 20 个文件：
sqlt_s51483_10053_i1_c1_extract.trc sqlt_s51483_cell_state.zip sqlt_s51483_main.html sqlt_s51483_sqldx.zip sqlt_s51483_tcx.zipsqlt_s51483_addmrpt_0007.zip sqlt_s51483_driver.zip sqlt_s51483_opatch.zip sqlt_s51483_tcb.zip sqlt_s51483_tc.zipsqlt_s51483_ashrpt_0007.zip sqlt_s51483_lite.html sqlt_s51483_readme.html sqlt_s51483_tc_script.sql sqlt_s51483_trc.zipsqlt_s51483_10053_explain.trc sqlt_s51483_awrrpt_0007.zip sqlt_s51483_log.zip sqlt_s51483_sql_detail_active.html sqlt_s51483_tc_sql.sqlsqlt*lite.html 内容
这个文件算是轻量级的 sqlt 了，里面包含六大块内容，主要涉及到表、索引、索引列、执行计划这些信息。
sqlt*main.html 内容
215187.1 SQLT XTRACT 19.1.200226 Report: sqlt_s51484_main.html
Main 文件内容更加丰富，主要包含以下八类信息。带有下划线的内容点击均可进入到相应的模块，大家可自行点击查看，尤其 Golbal 相关的信息，更为重要，建议大家详细查看。
如下示例，列出了数据库中非默认的优化器参数及参数值。
SQLHC
这里顺便说一句比 SQLT 更简洁的 sqlhc 工具，这个工具收集的信息也很全面，值得大家尝试，研读。
上传 sqlhc 文件，公众号后台回复【sqlhc】获取，输入 T 和 sqlid 即可生成。sqlhc 是 SQL health check的简称，能够收集sql相关的表、索引、统计信息、优化器参数、SQL执行情况、等待事件等信息，可以帮你检查SQL存在的问题并优化 SQL。
执行方法：sqlplus / as sysdbaSQL> @/home/oracle/tmp/sqlhc.sql T 9a4tv1dduu9u4或者SQL> @/home/oracle/tmp/sqlhc.sqlParameter 1:Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)Enter value for 1: TPL/SQL procedure successfully completed.Parameter 2:SQL_ID of the SQL to be analyzed (required)Enter value for 2: 9a4tv1dduu9u4 <----输入 sql_id 等待 5 分钟左右
时间有可能更长或者更短(根据 AWR 保存周期、字典表大小不同相差较大，一般系统应该在 5 分钟以内能够完成)，对数据库没有影响。执行过程有 log，也有屏幕输出。执行过程会 insert 数据到 plan_table 表，执行结束时会 rollback。
结束后生成的文件名以 sqlhc 开头，依次是日期、时间、sql_id。类似这样：sqlhc_20211125_1810_9a4tv1dduu9u4.zip
其中 4 个 html 文件和 log.zip 是通常存在的。
10053 trace 文件的生成需要 11.2 版本以上，sql_id 仍在 library cache 内的情况下。
如果 sql_monitor.zip 也包含在 sqlhc 压缩包内，说明 SQL 执行时间超过了 5s，或者是并行的 SQL，而且收集 sqlhc 时仍保留在 sql monitor 的内存里。sql monitor 对分析 sql 执行计划有很大帮助，如果遇到问题收集 sqlhc 信息及时，就非常有可能收集到 sql monitor 文件。如果一个 sql 执行完后超过半小时没有收集 sqlhc，sql monitor 信息就就非常有可能被刷出内存。
主要分析的的 3 个 html 文件是：
*_health_check.html*_diagnostics.html*_execution_plan.html
内容也非常丰富，可以多收集一些看看，那么今天就到这里啦。
参考资料
All About the SQLT Diagnostic Tool (Doc ID 215187.1)SQLT Usage Instructions (Doc ID 1614107.1)SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)How to Create a SQL-testcase Using the DBMS_SQLDIAG Package(Doc ID 727863.1)Monitoring SQL statements with Real-Time SQL Monitoring (DocID 1380492.1)
全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————