sqlt宝剑出鞘，sql性能问题无所遁形-凯发app官方网站

sqltxplain（简称sqlt）是oracle coe提供的一款sql性能诊断工具，sqlt主要方法是通过输入的一个sql语句，从而生成一组诊断文件，这些文件用于诊断性能较差的或产生错误结果(wrong results)的sql。

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

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

 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）。

3.1 宝剑出鞘之sqlt生成诊断文件

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

sql text:

select *

  from test1

 where test1.status in (select test2.status from test2

                  where object_name like 'prc_test%');

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

dingjun123@oradb> select status,count(*)

  2  from test1

  3  group by status;

status    count(*)

------- ----------

invalid          6

valid        76679

--子查询结果是invalid

dingjun123@oradb> select test2.status from test2

  2                    where object_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/sql procedure successfully completed.

elapsed: 00:00:00.00

parameter 1:

sql_id or hash_value of the sql to be extracted (required)

enter value for 1: aak402j1r6zy3

paremeter 2:

sqltxplain password (required)

enter value for 2: xxxxxx

pl/sql procedure successfully completed.

elapsed: 00:00:00.00

value passed to sqltxtrxec:

sql_id_or_hash_value: "aak402j1r6zy3"

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

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

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

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

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

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

1）  cbo的缺陷，无法准确估算对应的结果集的cardinality。

2）  cbo的bug或参数设置原因。

针对以上两种情况，后面会介绍解决方法，这里先说下，为什么这里走了hash join，test1走了full table scan，结果集的cardinality估算的结果正好是test1的行数呢，原因在于：

1）test1的status有直方图

2）子查询结果查询出status，但是查询结果的status值在没有执行之前是未知的，也就是可能是invalid也可能是valid。

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

         既然知道是这个原因，那么，就采用sql profile绑定就可以了，详细内容见
下一篇：sqlt宝剑出鞘，sql性能问题无所遁形_part2