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

1.2   宝剑出鞘之sqlt快速绑定执行计划

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

对于coe工具sql profile绑定有两类：

1）直接绑定：针对执行计划经常突变的，历史中有好的执行计划，当前走的执行计划差，直接绑定即可。 

2）替换绑定：针对执行计划一直较差，没有好的执行计划作为参考，可通过添加hints让其走好的执行计划，然后通过coe工具手动修改文件或coe_load_sql_profile或者编写存储过程绑定到好的执行计划上。

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

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

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

3.2.1使用coe_xfr_sql_profile脚本直接绑定

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

sql> @coe_xfr_sql_profile.sql

parameter 1:

sql_id (required)

enter value for 1: 0hzkb6xf08jhw

plan_hash_value avg_et_secs

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

     3071332600        .006   --效率高的计划

       40103161        653

parameter 2:         ---------------次数输入需要绑定的plan_hash_value,显然我们输入3071332600 

plan_hash_value (required)

enter value for 2:

最后生成文件，执行。

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

3.2.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

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

                  where object_name like 'prc_test%');

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

dingjun123@oradb> @coe_load_sql_profile

parameter 1:

original_sql_id (required)

enter value for 1: aak402j1r6zy3

parameter 2:

modified_sql_id (required)

enter value for 2: 6rbnw92d7djwk

     plan_hash_value          avg_et_secs

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

           313848035                 .001

parameter 3:

plan_hash_value (required)

enter value for 3: 313848035

values passed to coe_load_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

original_sql_id: "aak402j1r6zy3"

modified_sql_id: "6rbnw92d7djwk"

plan_hash_value: "313848035"

…

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

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

1.3   宝剑出鞘之xplore快速诊断参数设置问题

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

select

    *

     from (select a.id, a.tel_id, a.pre_cate_id, a.insert_time, a.remark1

             from tab_bn_test_log a,

                  (select tel_id, min(insert_time) as insert_time

                     from tab_bn_test_log

                    where insert_time > '08-apr-19'

                      and id not in

                          (select imei from tx_mm_log_201907 where tid = '10')

                    group by tel_id) b

            where a.tel_id = b.tel_id

              and a.insert_time = b.insert_time

              and a.id not in

                  (select imei from tx_mm_log_201907 where tid = '10')

            order by insert_time)

    where rownum < 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，对应的点进去，找到正确的执行计划对应的参数设置：

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

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