带临时表的SQL查询语句的优化方法

  【IT168 原创】故障突发:11号上午,收到系统应用人员的反馈:“销售日报”无法查询出结果,已经等待一个小时,屏幕依然是灰的,而以往该报表2分钟内即可出结果,IT部的同事看一下怎么回事,领导在等报表。我登录数据库,查询当前系统的进程情况下图(2),发现确实有些进程已经运行3600+秒,捕捉出其执行计划,如:下图(3)

  印象中,该报表以前优化过,耗时也就几秒到几十秒,效率算得上是非常高的。观察此执行计划,CBO预估的查到数据非常少,但由于报表的SQL语句中使用上了临时表(TYBBSALEDAILYBD21 ),而临时表的数据在库中是无法查看到的,我也不清楚临时表的数据是如何生成的,因此,无法判断CBO预估的临时表的行数是否准确,该如何下手?

  此时,想起测试环境上,有上月对该库进行RMAN恢复测试后留下的测试库。于是启动测试中间件,让测试中间件指向该测试库,尝试在上面查询该报表,看看运行情况如何?

  可以看到,测试库上的执行计划,与正式库的完全不一样,而且在测试库上的查询的效率正常,和以往的一样,几十秒即出结果,显然,问题出在正式库的执行计划上,

  何为正式库的执行计划会变成这样?印象中,近期我没对正式库作过任何改动, 而且,报表在昨天查询(10号)时,还是正常的,到了今天就一下子突然异常起来,这种变化给人一种异样的感觉:该报表涉及到的数据量,到了一个量变引起质变的程度,导致了执行计划突变异常。但不论是怎么变,可以确定的是,是表的统计信息导致了执行计划异常,致使报表无法查询出结果。现在要思考的,是如何恢复回原来的执行计划?

  1 重新采集该SQL语句中涉及到的表的统计信息,但不包括临时表(由于是在另外的进程里作的采集操作,而此时的临时表是没有数据的,采集了也没意义),采集完后重新运行报表,发现执行计划不变,说明方法无效。

  2 分析测试库的执行计划,第1步执行的是,查询IC_GENERAL_B的索引

  正式库的执行计划为何不从此步开始?难道是该索引的 CLUSTERING_FACTOR 值过高?比较两库的情况,发现正式库的只比测试库的高一点,但测试库只到9月份的数据,而正式库则是10月份的数据,难道恰好是高出的这一点导致异常?虽然不大相信,但不尝试心有不甘,然而,修改(dbms_stats.set_index_stats)此参数值后再测,还是无效。

  4 此时,又不禁怀疑,可能不是统计信息导致的执行计划异常,但再一想,除了统计信息外,似乎没有别的原因了,既然测试库的执行计划是OK的,那就把测试库中的这些表的统计信息,导入(dbms_stats.export_table_stats/import)到正式库中再测试看看,就测试刚才在测试库上查询的时间段;此外,为稳妥起见,又对比了两库的系统参数值 aux_stats$(dbms_stats.gather_system_stats 采集),以及当时这两报表的sesion的参数情况(v$ses_optimizer_env),对比结果,系统,进程的环境变量值一模一样,但测试结果,依旧无效!

  5 想来想去,只有最后一招了,既然报表在测试库查询是OK的,而在正式库的异常,那就比较该语句在两库中的执行计划,看看有哪些不同,希望能从中找出端倪。在这里,要表扬一下11G的新特性,据我了解,在11G之前,要获取SQL语句执行计划生成过程的方法只有一种,就是使用 10053 事件命令跟踪:

  但这种获取方式有个前提,需要先在SQLPLUS里开启跟踪命令,运行SQL语句后,再关闭跟踪命令,但这种方法对我的情况行不通,因为报表语句中涉及到一临时表,临时表的数据是在应用系统发出报表查询指令后临时生成,而我并不清楚此过程中这些临时数据是如何生成,没有临时表的数据,解析出来的执行计划过程肯定与系统实际的情况不同,那就没意义了,,,但在11G,ORACLE新推出了包DBMS_SQLDIAG,使用该包中的DUMP_TRACE过程,可以获取正在运行的SQL的执行计划的生成过程。这样,就能达到我的需求(后面有说明)。

  分别在正式库和测试库发出跟踪命令(Dbms_Sqldiag.Dump_Trace)后,获取了两库对该SQL的执行计划的跟踪文本,再使用文本比较工具WinMerge来比较两跟踪文件,结果发现,明明是相同的内容,WinMerge 工具却显示为不同,估计是该软件的算法有问题(1.7 版本,10年前的软件),比较了几十处后,没发现什么异常,此时,已经是13号的下午(前面列的1,2,3,4点思路和测试情况,是前2天的测试结果),对这一次的分析工作已经是绞尽脑汁,精神上很疲倦,很想放弃了,但一想,问题没解决,下周一开工时问题再现,系统应用人员又要嗷嗷急叫,各种邮件,电话催个不停,这种场面一看我就头大,指望ORACLE技术支持吧,说不定被拖上几个礼拜,于是咬咬牙:既然是该版本的算法有问题,都过了这么久了,应该有新的WinMerge版本了吧,于是在网上查找并下载了个 2.3 版本的安装,使用时发现,新版果然比老版本好用,标出来的都是不同点,不仅如此,新版还把两者不同之处列出来,让人一目了然,比较过几处后,来到SQL语句中,临时表的统计信息处,发现有些不同,正式库里对该临时表做了统计,而测试库则没有,难道,,,如下图:NO statistics 字眼很明显

  6 删除掉(dbms_stats.delete_table_stats)正式库里该临时表的统计信息,再测试,GOOD,执行计划果然变回和测试库的一样了,,,一霎那,茅塞顿开,明白了根源所在,原因很简单,正式库中的临时表不何时被采集过,这是个事务级的临时表,不论是在本进程,还是在别的进程采集该表,其统计信息肯定都是0,如下:

  CBO在计算执行计划时,发现此情况后,认为该表的记录非常少(=1),于是采用了最快捷的嵌套循环(NL)来读取数据;

  而测试库中的临时表,并没有被采集,此时,优化器对该表使用了动态采集(LEVEL=3),于是,该临时表的数据被准确预估出,由此生成较佳的执行计划,,,熬了3天,终于柳暗花明,苦尽甘来。

  经过几天的优化工作,心里对临时表的优化方法印象非常深刻了。此前,我总头疼带有临时表的查询SQL,认为由于不清楚其数据量,无法判断语句的执行计划是否最佳?但这番折腾下来,对这类语句的优化方法,有了个总体全面的认识:方法很简单,就是把所有的临时表的统计信息都清空,CBO发现临时表的统计信息为空时,将动态采样。为避免误采集临时表,可以在清空临时表统计信息后,把临时表的统计信息给锁住,这样,该表就不会再被采集,除非人为地(dbms_stats.gather_table_stats)设置强行采集参数force=true。

  为什么说,对临时表采用动态采集的方法是可靠的?我思考,原因有二:一方面,ORACLE的临时表有两种类型,要么事务结束时清除临时表里的数据;要么进程/会话结束时清空临时表的数据,这注定了日常对临时表的采集统计是无意义,只能在应用时临时采集。另一方面,9I以后,ORACLE采用了本地管理这一新的磁盘管理方法:采用本地管理方法后,ORACLE在存储数据的块里记录了其拥有哪些块,哪些BLOCK块被使用等这些METADATA,这样,优化器在采样部分BLOCK块里的数据后,就能大致估算出整个段/表有多少数据,以及某个字段的某个值的比例。而类似上述问题中SQL语句带的临时表,这类临时表通常只是临时保

  存一些关键数据,数据量不会太大,这样,CBO在采样预估时(LEVEL=2、3),通常是比较准确。

  其实也是我在一开始时,没有深入分析比较才搞得这么辛苦:为何CBO会选择第1个要读取的是临时表?我们知道,CBO在选择最佳的执行计划时,选择读取的第1个数据集/表,基本上是返回数据量最少的数据集。正式库上第1个被读取的是临时表,此时CBO预估其返回值为1,而在测试库上,CBO预估该临时表的返回值为25,为何差异如此大(算倍数)?一些经验丰富的DBA,看到这些差别,就能立即推断出临时表的统计信息有问题,再看看其t_analzyzed 字段,发现其刚被采集过,这就说明了为何该报表昨天没出问题,今天才爆出问题?就是因为刚对临时表作了统计信息采集,因此,此时可以考虑尝试删除临时表的统计信息,看看效果,,,要是一开始时能从这一思路出发,后面也不用搞得这么辛苦,,,

  但和上面的语句一样,该方法也只能在语句硬解析时才获取其执行计划,终究是不佳。

  2 若是会话级的临时表,数据量大时,可以考虑在插入数据后,临时采集临时表。这通常在存储过程中,借临时表来作数据过度时使用。

相关阅读