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

今天接到这样一个问题,某省的报表系统的一个某个处理进程在前几天处理的速度突然变慢,而且从应用的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条评论

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

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

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

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

    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来减少垃圾评论。了解我们如何处理您的评论数据