解决棘手 SQL 性能问题，我的 SQLT 使用心得

本文由 dbaplus 社群授权转载。

一、SQLT 背景介绍

SQLTXPLAIN（简称 SQLT）是 ORACLE COE 提供的一款 SQL 性能诊断工具，SQLT 主要方法是通过输入的一个 SQL 语句，从而生成一组诊断文件，这些文件用于诊断性能较差的或产生错误结果 (WRONG RESULTS) 的 SQL。

SQLT 产生的诊断文件内容包括执行计划、统计信息、CBO 的参数、10053 文件、性能变化的历史等需要诊断 SQL 性能的一系列文件，而且 SQLT 还提供一系列工具，比如快速绑定 SQL 执行计划的工具。

SQLT 主要使用场合是在需要快速绑定 SQL 执行计划，或者一些和参数、BUG 等相关的疑难 SQL 分析中。

二、SQLT 家族简介

SQLT 主要包含下列方法：

SQLT 为一个 SQL 语句提供了下面 7 种主要方法来生成诊断详细信息 XTRACT，XECUTE，XTRXEC，XTRSBY，XPLAIN，XPREXT 和 XPREXC。XTRACT，XECUTE，XTRXEC，XTRSBY，XPREXT 和 XPREXC 处理绑定变量和会做 bind peeking（绑定变量窥视），但是 XPLAIN 不会。这是因为 XPLAIN 是基于 EXPLAIN PLAN FOR 命令执行的，该命令不做 bind peeking。

因此，如果可能请避免使用 XPLAIN，除了 XPLAIN 的 bind peeking 限制外，所有这 7 种主要方法都可以提供足够的诊断详细信息，对性能较差或产生错误结果集的 SQL 进行初步评估。如果该 SQL 仍位于内存中或者 Automatic Workload Repository (AWR) 中，请使用 XTRACT 或 XTRXEC，其他情况请使用 XECUTE。对于 Data Guard 或备用只读数据库，请使用 XTRSBY。仅当其他方法都不可行时，再考虑使用 XPLAIN。XPREXT 和 XPREXC 是类似于 XTRACT 和 XECUTE，但为了提高 SQLT 的性能它们禁了一些 SQLT 的特性。

几种主要方法的关系如下：

其中 XTRXEC 包括了 XTRACT 和 XECUTE 方法，实际上它会同时执行这两个方法生成对应的文件。使用这些方法后，会生成文件，自动打包。

SQLT 的详细内容请参考 MOS 文档：SQLT 使用指南 (Doc ID 1677588.1)，本文重点说下 SQLT 里比较有用的方法（本文内容的环境是 11.2.0.3）。

三、SQLT 宝剑出鞘

1、SQLT 生成诊断文件

生成诊断文件使用的是 sqlt/run 目录下的文件，此目录下还有 SQLHC 健康检查的脚本。这里看一个例子：

 复制代码

SQLtext:
select*
fromtest1
wheretest1.statusin(selecttest2.statusfromtest2
whereobject_namelike'PRC_TEST%');

这是条简单的子查询 SQL，其中 test1 的 status 有索引，而且 status 有倾斜分布如下：

 复制代码

dingjun123@ORADB>selectstatus,count(*)
2 from test1
3 group by status;
{1}
STATUS COUNT(*)
------- ----------
INVALID6
VALID76679
-- 子查询结果是 INVALID
dingjun123@ORADB>selecttest2.statusfromtest2
2whereobject_name like'PRC_TEST%'
3;

STATUS
-------
INVALID
INVALID

子查询中的语句返回的正好是 INVALID，那么可以预测，此语句应该是用子查询结果驱动表 test1，走 test.status 列的索引，正常的应该是走 nested loops。OK，那么我们看看执行计划：

执行计划令人费解，要知道，对于表的统计信息是最新的且采样比例 100%，而且也收集了 STATUS 列的直方图，为什么还走 HASH JOIN，而且 TEST1 还走全表呢？先用 SQLT 诊断下，到 sqlt/run 目录下找到对应的脚本，然后输入 SQLID, 之后会将生成的文件打包。

 复制代码

dingjun123@ORADB> @sqltxtrxec
PL/SQLproceduresuccessfullycompleted.
Elapsed:00:00:00.00

Parameter1:
SQL_IDorHASH_VALUEofthe SQLtobe extracted (required)

Enter valuefor1: aak402j1r6zy3

Paremeter2:
SQLTXPLAIN password (required)

Enter valuefor2: XXXXXX
PL/SQLproceduresuccessfullycompleted.
Elapsed:00:00:00.00
Value passedtosqltxtrxec:
SQL_ID_OR_HASH_VALUE:"aak402j1r6zy3"

解压文件，即可看到如下内容：

这里我们主要看 main 文件，这是主要内容以及 10053 等。

首先打开 main 文件，可以看到主要诊断内容：

可以看到，包括 CBO 的环境，执行计划以及历史执行信息，表，索引等对象统计信息都在这个 main 文件中，大部分时候可以通过此文件，了解 SQL 效率不佳的原因，比如执行计划变坏的时间段内正好收集了统计信息，那么可以快速定位可能是统计信息收集不正确导致的。

一般情况下，都是先看执行计划，通过 Plans 目录找到 Execution Plans，可以点那些 +，会显示对应的统计信息等内容：

在统计信息正确的情况下，CBO 估算的返回结果行是 76685 行，而实际结果是 6 行，估算是实际的 12781 倍，这显然是有问题的。可以点开对应的 +，看看统计信息：

TEST1 的 STATUS 列收集了直方图，而且是 100% 采样，没有任何问题。到此，这个简单的 SQL 很可能的情况就是：

• CBO 的缺陷，无法准确估算对应的结果集的 cardinality；
• CBO 的 BUG 或参数设置原因。

针对以上两种情况，后面会介绍解决方法，这里先说下，为什么这里走了 HASH JOIN，TEST1 走了 FULL TABLE SCAN，结果集的 cardinality 估算的结果正好是 TEST1 的行数呢，原因在于：

• TEST1 的 STATUS 有直方图；
• 子查询结果查询出 STATUS，但是查询结果的 STATUS 值在没有执行之前是未知的，也就是可能是 INVALID 也可能是 VALID。

综合以上因素，CBO 无法在运行期之前预知结果的具体值，从而导致优化器缺陷，走了不佳的执行计划（12C 的 apative plan 可以解决这个问题）。

既然知道是这个原因，那么，就采用 SQL PROFILE 绑定就可以了，详细内容见下节。

2、SQLT 快速绑定执行计划

SQL PROFILE 可以使用 SQLT 工具快速绑定，SQL PROFILE 就是对 SQL 增加了一系列 HINTS，好处是不需要改写 SQL，可以在数据库里直接管理。

对于 COE 工具 SQL PROFILE 绑定有两类：

• 直接绑定：针对执行计划经常突变的，历史中有好的执行计划，当前走的执行计划差，直接绑定即可。
• 替换绑定：针对执行计划一直较差，没有好的执行计划作为参考，可通过添加 hints 让其走好的执行计划，然后通过 coe 工具手动修改文件或 coe_load_sql_profile 或者编写存储过程绑定到好的执行计划上。

注意：如果 SQL 没有绑定变量，则通过 coe_xfr_sq\l_profile 生成的文件需要修改 force_match=>true, 手动编写存储过程或者 coe_load_sql_profile 做替换绑定的也需要修改 force_match=>true, 以让所有 SQL 结构相同（字面量条件不同）的 SQL 都绑定上好的执行计划。

（对应的绑定计划的脚本在 sqlt/utl 目录下）

下面分别说说这两种绑定方式：

1）使用 coe_xfr_sql_profile 脚本直接绑定

针对 SQL 执行计划经常突变，当计划变差时候，快速绑定到效率高的执行计划中。如下例：运行 code_xfr_sql_profile 然后输入 sql_id：

 复制代码

SQL> @coe_xfr_sql_profile.sql

Parameter1:
SQL_ID (required)
Enter valuefor1:0hzkb6xf08jhw


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3071332600.006-- 效率高的计划
40103161653

Parameter2: --------------- 次数输入需要绑定的 PLAN_HASH_VALUE, 显然我们输入3071332600
PLAN_HASH_VALUE (required)

Enter valuefor2:

最后生成文件，执行。

注意：如果 SQL 没有使用绑定变量，需要将生成文件的 force_match => FALSE 中的 FALSE 改成 TRUE。

2）使用 coe_load_sql_profile 做替换绑定

3.1 中的例子是由于 CBO 的缺陷导致无法判定子查询结果，从而导致走错了执行计划，这里在 12c 之前需要绑定执行计划，因为没有现成的执行计划，所以需要自己写 hints 构造一条正确执行计划的 SQL，然后通过 SQLT 的替换绑定，将正确执行计划绑定到原 SQL 中去。

先将原始 SQL 通过增加 hints, 让其执行计划正确，改造后的 SQL 如下：

 复制代码

select/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$5DA710D3""TEST1"@"SEL$1")
LEADING(@"SEL$5DA710D3""TEST2"@"SEL$2""TEST1"@"SEL$1")
INDEX_RS_ASC(@"SEL$5DA710D3""TEST2"@"SEL$2"("TEST2"."OBJECT_NAME"))
INDEX_RS_ASC(@"SEL$5DA710D3""TEST1"@"SEL$1"("TEST1"."STATUS"))
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/ *
from test1
wheretest1.statusin(select test2.status from test2
whereobject_name like'PRC_TEST%');

然后使用 coe_load_sql_profile 脚本做替换绑定，输入原始的 sql_id 和替换的 sql_id：

 复制代码

dingjun123@ORADB> @coe_load_sql_profile
Parameter1:
ORIGINAL_SQL_ID(required)

Entervalue for 1: aak402j1r6zy3

Parameter2:
MODIFIED_SQL_ID(required)

Entervalue for 2: 6rbnw92d7djwk

PLAN_HASH_VALUEAVG_ET_SECS
-------------------- --------------------
313848035 .001

Parameter3:
PLAN_HASH_VALUE(required)

Entervalue for 3: 313848035

Valuespassed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID:"aak402j1r6zy3"
MODIFIED_SQL_ID:"6rbnw92d7djwk"
PLAN_HASH_VALUE:"313848035"
…

再次执行原始语句，可以看到，绑定执行计划成功，已经走了索引和 NESTED LOOPS。

SQLT 的快速绑定执行计划，在处理突发 SQL 性能问题中使用广泛，的确是一个非常好的工具，犹如宝剑出鞘，削铁如泥。

3、XPLORE 快速诊断参数设置问题

某天晚上某系统一重要语句，迁移到新库后执行 1 小时都没有结果，原先很快（1s 左右），业务人员焦急万分。对应的语句如下：

 复制代码

SELECT
*
FROM(SELECTA.ID, A.TEL_ID, A.PRE_CATE_ID, A.INSERT_TIME, A.REMARK1
FROMTAB_BN_TEST_LOG A,
(SELECTTEL_ID,MIN(INSERT_TIME)ASINSERT_TIME
FROMTAB_BN_TEST_LOG
WHEREINSERT_TIME >'08-APR-19'
ANDIDNOTIN
(SELECTIMEIFROMTX_MM_LOG_201907WHERETID ='10')
GROUPBYTEL_ID) B
WHEREA.TEL_ID = B.TEL_ID
ANDA.INSERT_TIME = B.INSERT_TIME
ANDA.IDNOTIN
(SELECTIMEIFROMTX_MM_LOG_201907WHERETID ='10')
ORDERBYINSERT_TIME)
WHEREROWNUM<200

查看执行计划:

执行计划中出现 FILTER，也就是子查询无法 unnest, 由于使用的是 NOT IN，但是回头一想，这是 11g, 有 null aware 特性，应该不会出现 FILTER 才对，而且使用 hints 也无效。那么首先想到的就是检查 null aware 参数是否设置，经过检查：

完全没有问题，那么在收集统计信息、SQL PROFILE、可以想到的参数设置都没有问题情况下，如何解决呢？

由于查询转换受众多参数设置影响，虽然 null aware 已经开启，但是可能受其它参数或 fix control 设置影响，因此，这里可以使用 SQLT 的神器 XPLORE 分析，它会将已知参数、已知 bug 对应的 fix control 逐一重新设置一遍，然后生成对应的执行计划，最后生成一个 html 文件，通过查看执行计划，找到对应的参数或者 BUG。

SQLT XPLORE 中有 XEXCUTE、XPLAIN 等众多方法，对于慢的语句，建议使用 XPLAIN 方法。然后查看分析结果与目标计划匹配的设置，从而找出问题。

使用 XPLORE，可以参考 sqlt/utl/xplore 中的 readme.txt。这里需要将对应的 SQL 内容里加上：/* ^^unique_id */。

最终，生成的 XPLORE 文件内容如下：

有 8 个执行计划的 PLAN_HASH_VALUE，对应的点进去，找到正确的执行计划对应的参数设置：

最终找到，原来和 _optimizer_squ_bottomup 参数有关，这个参数，系统设置成 FALSE，导致此子查询无法进行 null aware 查询转换，重新设置后语句执行恢复到正常时间。

针对这样的情况，如果一个个参数去对比分析，必然耗时很长，使用 SQLT 的 XPLORE 神器，可以快速找到对应的参数设置或已知 BUG 问题，比如一些新特性导致的 SQL 性能问题、SQL 产生错误的结果等，都可以通过 XPLORE 分析，快速找到对应的参数，然后重新设置。

最后做个总结：SQLT 里还有很多其他的功能，可以通过 MOS 查看对应的文章，SQLT 在解决棘手的 SQL 性能问题时，的确是一把利器，犹如宝剑出鞘，SQL 性能问题无所遁形。

作者介绍：

丁俊，新炬网络首席性能优化专家，SQL 审核产品经理。《剑破冰山 -Oracle 开发艺术》副主编，ITPUB 开发版资深版主，十余年电信行业从业经验。

原文链接：

https://mp.weixin.qq.com/s?__biz=MzI4NTA1MDEwNg==&mid=2650786311&idx=1&sn=07a741b69239da1ae9d52467a56199b5&chksm=f3f97f92c48ef684a98127f71e79102317fe49c65a1c47943d91be0c6beef493dc1266ceb429&scene=27#wechat_redirect