..experience, Working case

3小时的sql调优到3分钟。

9条评论

今天接到这样一个问题，某省的报表系统的一个某个处理进程在前几天处理的速度突然变慢，而且从应用的log上还看到1555的报错：

当时第一个反应就是加大undo表空间大小和undo retention参数。但是，之前的程序跑的还比较正常，没经常性的报1555，现在突然执行很久都不出结果来，很大程度我就怀疑是执行计划发生了改变。

问了应用的同学，该程序是一个etl程序，先到报表系统取去时间值，根据时间到核心数据库查订购关系的历史，做完之后，update一个config表。大致的程序如下：

而在alertlog中，报错1555的正是中间的那段sql：

从时间上，该sql从0点开始，跑到了3点45，而undo retention是设置了3小时，已经超过了retention的值。除了这次执行时间在3小时，在应用的log中还发现从2月26日开始就有多次出现执行时间在3小时以上的。开始检查这个表的相关结构和sql的执行计划：
这个表是分区表，按照actiondate进行rang分区，每月一个分区，用于存放历史记录，目前已经有95G多。
sql的执行计划为：

我们发现是走索引的index full scan。

尝试带入变量，查看其执行计划：

发现根据程序带入的变量，是走full table scan的！！

看上去，走tsc的cost比走ifs的cost小很多，实际效果如何，我们再来通过set timing on 和autotrace来看看：

我们发现无论使用绑定变量走ifs还是不使用绑定变量走tsc，二者在时间上消耗都是类似的，基本都在三分半钟。为什么会跑3个小时还没出来呢？难道是etl程序的问题？

开始通过oradebug开始trace，一边让应用开始跑etl的压缩进程，一边在数据库端查trace。发现trace中的等待基本都是：

从trace来看，etl确实是走了ifs。等了15分钟左右吧，该等待事件仍然在继续，看来，走ifs，可能是要3个小时以上了，而不是我在sqlplus中测试的3分半钟。

于是叫应用把etl进程先停掉了，为sql加上full的hints，再开始跑，开始trace……

时间，3分钟跑完！！

在这里，sql优化的对策基本已经找到，就是走tsc。但是，为什么在2月26日之后会突然执行计划变差，变成ifs？为什么我在sqlplus上测试的走ifs也只要3分班钟，而etl跑的走ifs的却要超过3个小时？

为了找个答案，我再做了一次10053的trace，去看看oracle是怎么样选择执行路径的？

令我感到惊奇的是：这个明明在sqlplus中显示走ifs的语句，在10053trace出来的文件中竟然看到是tsc的cost最小，就是还是走tsc的！！

于是这应该能够解释，为什么我用etl走ifs要3小时以上，而我在sqlplus中看到也是走ifs，但是只要3分半钟。——因为其实它也是走了tsc，不过在执行计划中看到走ifs了。不过，这样的情况之前都没遇到过，难道是个bug？

相关文章

9条评论

  2. 你这表和索引的统计信息应该都不准确吧,看cost是没有意义的,你最后那个真实的执行计划应该是重用了share pool中的,而你的set autot trace看到的执行计划是这时候生成的,并不是真实的执行计划

    回复

  4. To 作者小荷,

    提一些意见,不要不爱听. 良药苦口. 嘻嘻.

    其中 棉花糖ONE 已经提到了一些,

    * 没有表/分区统计信息, 分析10053 trace 没有指导意义.
    * 数据仓库/批处理环境, 大的查询使用常量好一些, 可以帮助优化器得到有效SQL执行路径; case by case, 仍需分析应用逻辑后使用.
    * 文章组织有些乱, 建议先写结果,再写测试案例
    * 无法重现 测试结果, 给出完整测试案例
    * test SQL和 production SQL运行环境不一样, 使用下面的查询显示实际运行的production SQL, 可能不支持 9.2, 赶紧升级吧.

    SELECT t.*
    FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
    WHERE sql_text LIKE ‘%my_sql_text%’ and sql_text NOT LIKE ‘%v$sql%’;

    To 棉花糖ONE,

    你的内功深厚呀, 到处留下真知灼见. ^_^

    How to contact you? e.g. email, MSN.

    Thanks,
    -MJ

    回复

  5. re 木匠：
    谢谢你的意见，以后在写的时候，我会注意理清文章的组织。这次写文章的时候确实思路有点乱了，写这个文章的本意是抛砖引玉（还当了一回标题党，嘿嘿），希望大家能讨论这个结果是否是bug，我在处理的过程中哪里还分析的不对。
    统计信息是比较早，05年7月之后就没有被分析过了。
    要使用常量，作为一名维护人员来说比较困难的，涉及到修改代码甚至是业务逻辑。
    上述的所有sql和执行计划是生产环境上的。
    DISPLAY_CURSOR在9206上是没有的。数据库版本的升级，目前已经纳入考虑范围内了，一方面测试部已经开始测试，另一方面要等集团公司下文通知。

    回复

  6. 有容乃大,分享知识,共同进步.

    TOP(Christian Antognini的Troubleshooting Oracle Performance)里面讲了如何显示Oracle 9.2 v$sql_plan里面的执行计划, 你自己看看, 201~203页.
    Display execution plan in the library cache.
    (我眼下主要在Oracle 11.1.0.7做应用开发, 所以对9.2已经提不起兴趣了).

    回复

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据。