一、SQLT背景介绍SQLTXPLAIN（简称SQLT）是ORACLECOE提供的SQL性能诊断工具。SQLT的主要方法是生成一组诊断文件，用于诊断性能不佳或产生错误结果（WRONGRESULTS）的SQL。SQLT生成的诊断文件包括执行计划、统计信息、CBO参数、10053文件、性能变化历史等一系列需要诊断SQL性能的文件，SQLT也提供了一系列工具，如快速绑定SQL执行计划工具。SQLT的主要用途是在需要快速绑定SQL执行计划，或者一些与参数、BUG等相关的高难度SQL分析。2.SQLT家族介绍SQLT主要包括以下方法：SQLT为一条SQL语句提供了以下7种主要的方法来生成详细的诊断信息XTRACT、XECUTE、XTRXEC、XTRSBY、XPLAIN、XPREXT和XPREXC。XTRACT、XECUTE、XTRXEC、XTRSBY、XPREXT和XPREXC处理绑定变量并将进行绑定查看，但XPLAIN不会。这是因为XPLAIN是基于EXPLAINPLANFOR命令执行的，该命令不执行绑定查看。因此，如果可能，请避免使用XPLAIN，除了XPLAIN的绑定窥视限制外，所有7种主要方法都可以提供足够的诊断详细信息，以对性能不佳或生成不正确结果集的SQL进行初步评估。如果SQL仍在内存中或自动工作负载存储库(AWR)中，请使用XTRACT或XTRXEC，否则使用XECUTE。对于DataGuard或备用只读数据库，请使用XTRSBY。仅当所有其他方法都失败时才考虑使用XPLAIN。XPREXT和XPREXC类似于XTRACT和XECUTE，但它们禁用了一些SQLT功能以提高SQLT的性能。几个主要方法的关系如下：其中，XTRXEC包括XTRACT和XECUTE方法，实际上它会同时执行这两个方法，生成相应的文件。使用这些方法后，会自动生成文件并打包。SQLT的详细介绍请参考MOS文档：SQLTUserGuide(DocID1677588.1)。本文主要介绍SQLT中比较有用的方法（本文环境为11.2.0.3）。三、SQLT利剑出鞘1、SQLT生成诊断文件生成的诊断文件使用sqlt/run目录下的文件，该目录下有SQLHC健康检查的脚本。下面是一个示例：SQLtext:select*fromtest1wheretest1.statusin(selecttest2.statusfromtest2whereobject_namelike'PRC_TEST%');这是一个简单的子查询SQL，其中test1的状态有一个索引，状态呈偏态分布如下：dingjun123@ORADB>selectstatus,count(*)2fromtest13groupbystatus;STATUSCOUNT(*)-----------------INVALID6VALID76679--子查询结果为INVALIDdingjun123@ORADB>selecttest2.statusfromtest22whereobject_namelike'PRC_TEST%'3;STATUS-------INVALIDINVALID子查询中的语句恰好返回INVALID，所以可以预见，这条语句应该使用子查询结果来驱动表test1，并使用test.status列的索引。通常，它应该使用嵌套循环。OK，那我们再看看执行计划：执行计划百思不得其解。要知道表的统计数据是最新的，采样率是100%，STATUS列的直方图也是收集的。为什么还是用HASHJOIN，和TEST1全表呢？先用SQLT诊断，在sqlt/run目录下找到对应的脚本，然后输入SQLID，生成的文件就会被打包。dingjun123@ORADB>@sqltxtrxecPL/SQLproceduresuccessfullycompleted.Elapsed:00:00:00.00Parameter1:SQL_IDorHASH_VALUEoftheSQLtobeextracted(required)Entervaluefor1:aak402j1r6zy3Paremeter2:SQLTXPLAINpassword(required)Entervaluefor2:XXXXXXPL/SQLproceduresuccessfullycompleted.Elapsed:00:00:00.00Valuepassedtosqltxtrxec:SQL_ID_OR_HASH_VALUE:"aak402j1r6zy3"解压文件，可以看到如下内容：这里我们主要看main文件，就是主要内容和10053。首先打开main文件，可以看到主要的诊断内容：可以看到，包括CBO环境、执行计划和历史执行信息、表、索引等对象的统计信息都在这个主文件中，大部分时候可以通过这个文件，了解SQL效率低下的原因，比如在执行期间收集统计信息plangoesbad，可以快速定位统计信息收集错误的可能原因。一般先看执行计划，通过Plans目录找到ExecutionPlans，点击那些+，会显示相应的统计信息等内容：当统计信息正确时，CBO估计的返回结果行数为76685rows，实际结果是6行，估计是实际值的12781倍，显然是有问题的。可以点击对应的+查看统计信息：TEST1的STATUS栏采集直方图，100%采样，没有问题。此时，这条简单的SQL可能出现的情况是：CBO缺陷，无法准确估计对应结果集的基数；CBOBUG或参数设置原因。对于以上两种情况，后面会介绍解决方法。先说一下这里为什么要用HASHJOIN，而TEST1用的是FULLTABLESCAN。结果集基数估计的结果就是TEST1的行数。原因是：TEST1的STATUS有直方图；子查询结果查询STATUS，但是查询结果的STATUS值在执行前是未知的，即可能是INVALID也可能是VALID。综合以上因素，CBO无法在运行前预测结果的具体值，导致优化器缺陷和执行计划不佳（12C的apative计划可以解决这个问题）。既然你知道是这个原因，那么你就可以使用SQLPROFILE绑定，详见下一节。2、SQLT快速绑定执行计划SQLPROFILE可以用SQLT工具快速绑定。SQLPROFILE向SQL添加一系列HINTS。优点是不需要重写SQL，可以直接在数据库中管理。COE工具的SQLPROFILE绑定有两种：直接绑定：对于那些执行计划经常变异的，如果历史上有好的执行计划，而当前的执行计划很差，直接绑定即可。替换绑定：执行计划一直很差，没有好的执行计划作为参考。可以添加提示使其成为一个好的执行计划，然后使用coe工具手动修改文件或者coe_load_sql_profile或者写一个存储过程绑定到一个好的执行计划上。注意：如果SQL没有绑定变量，coe_xfr_sql_profile生成的文件需要修改force_match=>true，手动写存储过程或者替换绑定的coe_load_sql_profile也需要修改force_match=>true，这样所有的SQL结构都是一样的（literal不同条件）SQL都绑定了一个好的执行计划。（对应绑定方案的脚本在sqlt/utl目录下）先说一下两种绑定方式：1）直接使用coe_xfr_sql_profile脚本绑定。SQL执行计划经常发生变化。当计划变得更糟时，快速绑定成一个高效的执行计划。以下示例：运行code_xfr_sql_profile并输入sql_id：SQL>@coe_xfr_sql_profile.sqlParameter1：SQL_ID（必填）Entervaluefor1:0hzkb6xf08jhwPLAN_HASH_VALUEAVG_ET_SECS--------------------------3071332600.006--Efficientplan40103161653参数2:----------------输入需要绑定的PLAN_HASH_VALUE的次数，显然我们输入3071332600PLAN_HASH_VALUE(required)Entervaluefor2:最后生成文件并执行。注意：如果SQL不使用绑定变量，需要将生成文件的force_match=>FALSE中的FALSE改为TRUE。2)使用coe_load_sql_profile替换绑定。3.1中的例子是由于CBO的缺陷导致子查询的结果无法确定，导致执行计划错误。这里在12c之前需要绑定执行计划，因为没有现成的执行计划，所以需要自己写hints来构造一条执行计划正确的SQL，然后把正确的执行计划绑定到原来的SQL上通过SQLT替换绑定。先给原来的SQL加上提示，让它的执行计划正确。修改后的SQL如下：select/*+BEGIN_OUTLINE_DATAUSE_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_ROWSDB_VERSION('11.2.0.3')OPTIMIZER_FEATURES_ENABLE('11.2.0.3')IGNORE_OPTIM_EMBEDDED_HINTSEND_OUTLINE_DATA*/*fromtest1wheretest1.statusin(selecttest2.statusfromtest2whereobject_namelike'PRC_sqlfileTEST%')load;然后用co_sqlproe把原来的输入脚本替换成sqlproesql_id:dingjun123@ORADB>@coe_load_sql_profileParameter1:ORIGINAL_SQL_ID(required)Entervaluefor1:aak402j1r6zy3Parameter2:MODIFIED_SQL_ID(required)Entervaluefor2:6rbnw92d7djwkPLAN_HASH_VALUEAVG_ET_SECS----------------------------------------313848035.001参数3：PLAN_HASH_VALUE（必需）Entervaluefor3：313848035Valuespassedtocoe_load_sql_profile：~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ORIGINAL_SQL_ID：“aak402j1r6zy3"MODIFIED_SQL_ID:"6rbnw92d7djwk"PLAN_HASH_VALUE:"313848035"...再次执行原语句，可以看到绑定执行计划成功，索引和NESTEDLOOPSSQLT的快速绑定执行计划已经去掉，应用广泛在处理突发的SQL性能问题时，它确实是一个非常好的利器，犹如宝剑出鞘，削铁如泥。3.XPLORE快速诊断参数设置问题。一天晚上，某系统的一条重要语句迁移到新数据库，执行了一个小时，没有任何结果。以前很快（1s左右），业务人员急死了。select*select*select*from（selecta.id，a.tel_id，a.pre_cate_id，a.insert_time，a.remark1fromtab_bn_bn_test_loga，（selecttel_id，min（insert_time）10')GROUPBYTEL_ID)BWHEREA.TEL_ID=B.TEL_IDANDA.INSERT_TIME=B.INSERT_TIMEANDA.IDNOTIN(SELECTIMEIFROMTX_MM_LOG_201907WHERETID='10')ORDERBYINSERT_TIME)WHEREROWNUM<200查看执行计划：unnest导致执行计划无法执行，即子查询使用了NOTIN，但是仔细想想，这是11g，有nullaware特性，所以应该没有FILTER，使用hints也是无效的。然后首先想到的是检查是否设置了nullaware参数。检查后：完全没有问题，那么在收集统计信息、SQLPROFILE、可以想到的参数设置都没有问题的情况下如何解决呢？由于query的转换受很多参数设置的影响，虽然开启了nullaware，但可能会受到其他参数或fixcontrolsettings的影响。所以，这里可以使用SQLT的神器XPLORE分析，它会把已知参数和已知bug对应的修复控件一一重置，然后生成对应的执行计划，最后生成一个html文件，找到对应的参数或者通过查看执行计划发现错误。SQLTXPLORE中有XEXCUTE、XPLAIN等多种方法。对于慢语句，建议使用XPLAIN方法。然后查看分析结果与目标计划匹配的设置，以确定问题所在。XPLORE的使用可以参考sqlt/utl/xplore中的readme.txt。这里需要添加相应的SQL内容：/*^^unique_id*/。最终生成的XPLORE文件内容如下：共有8个PLAN_HASH_VALUE执行计划，点击相应的，找到正确执行计划对应的参数设置：最后发现与_optimizer_squ_bottomup参数。系统将此参数设置为FALSE。结果，该子查询无法转换为nullawarequery，语句执行在reset后恢复到正常时间。鉴于这种情况，要对参数一一对比分析，需要很长时间。使用SQLT的XPLORE神器，可以快速找到对应的参数设置或者已知的bug，比如一些新特性导致的SQL性能问题，SQL生成错误的结果等，都可以通过XPLORE进行分析，快速找到对应的参数和然后重置它们。最后总结一下：SQLT还有很多其他的功能。可以通过MOS查看相应的文章。SQLT在解决棘手的SQL性能问题时确实是一把利器。SQL性能问题犹如出鞘的利剑，是藏不住的。
