Oracle 10053跟踪诊断SQL

通过session级别跟踪：

对特定session启用跟踪：

          某客户核心系统新增一条update sql，该sql执行异常缓慢（超过几个小时）导致失败回滚。Sql相对简单具体文本如下：

    经过开发人员确认该sql在测试库（LINUX+ 12.2.0.1 单机环境）执行只需要几秒即可（数据量相差不大）完成，其中bs_loan_card_addition、bs_loan_card、bs_loan_contract_addition三张表的数据量都在200万行左右。

获取到生产环境该sql执行计划如下：

    测试环境该sql执行计划如下：

   首先怀疑统计信息不准确导致CBO在访问BS_LOAN_CARD表的时候选择错误，本应该选择BS_LOAN_CARD_I3索引（因为CUSTOMER_NO"='1000193229’的选择性很好）而这也是开发设计这个索引的原因。但结果是选择了BS_LOAN_CARD_I0索引（对应的是BS_LOAN_CARD.LOAN_CARD_NO,该列唯一的），而过滤条件根本没有这个列，他只是作为关联条件与bs_loan_card_addition表进行连接。

    因此首先检查统计信息，意外发现BS_LOAN_CARD.LOAN_CARD_NO,列上居然存在一个HYBIRD类型的直方图，理论上来说该值的唯一性非常好，不应该收集直方图，因此直接删除了该列上的直方图，再次检查发现执行计划仍未改变。

  尝试使用10053对该SQL执行计划的产生过程进行跟踪，发现如下信息：

  而且结合执行计划

    这个执行计划简单理解来说就是首先对BS_LOAN_CARD_ADDITION进行全表扫描，然后在这个所得的结果集里面每一行的LOAN_CARD_NO列拿出来到BS_LOAN_CARD里面匹配，这就是第6部里面出现了一个"B"."LOAN_CARD_NO"=:B1原因，而这个就是oracle改写后的结果。而从其选择对BS_LOAN_CARD_ADDITION进行全表扫描就不可避免的导致了效率会很低。而且其后的rows 估算为2444K，这个是贴合实际的，所以之前的删除直方图不会有结果。而测试上的改写结果明显跟这个不一样，排除统计信息的影响，那么就开始怀疑cbo内部的算法选择问题，再结合那个可疑 的 “CBQT bypassed forquery block UPD$1 (#0): Disabled by parameter. ”提示，怀疑优化器参数在两个环境中有区别，

于是将生产和测试的跟踪trace中的优化器参数进行对比：

将以上参数同样在测试上设置（会话级别），再次测试发现执行也变慢了，且执行计划跟生产一致，继续缩小范围，最终定位到_optimizer_squ_bottomup 参数

    根据资料得知，CBQT（cost-basedquery transformation，也就是CBO的sql改写功能）其受到以下两个参数的影响

一：_optimizer_cost_based_transformation设为linear（默认值），其有如下值：

"exhaustive", "iterative","linear", "on", "off"。

本例中该参数就是默认值，该参数可控制是否允许CBO进行改写

二：_optimizer_squ_bottomup 参数值为true（默认值）.

而生产环境中恰好相反为false，所以生产的trace中会有Disabled by parameter 字眼

查找资料

_optimizer_squ_bottomup  enables unnesting of subquery in a bottom-upmanner；

该参数默认为true，即开启子查询自底向上的展开功能（也就是类似unnest hint的功能），unnest称之为对子查询展开，顾名思义，就是不让子查询孤单地嵌套(nest)在里面。

Mos 上搜索该参数可以发现很多相关bug

     本次故障的直接原因是生产上修改了部分优化器相关的参数导致，而该参数导致了cbo的部分优化功能无法实施，进而选择了并不算最优的执行计划。而鉴于该参数是上线就已经设置了的，为了避免修改该参数引起已有的sql执行计划发生紊乱，最保险的方案是针对该sql单独调整参数，即通过以下hint来实现。