技术开发 频道

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

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

  案例(图1,正式库上该报表的运行时长)

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

  案例(图2,正式库上该报表的SQL语句)

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

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

  案例(图3,正式库上该报表的执行计划)

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

  定位问题:

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

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

  运行时观察其执行计划,如:图2

  案例(图2,测试库上的执行计划)

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

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

        比较、分析寻找原因:

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

  现在回顾,那次处理该问题,前后尝试了好几种方法:

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

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

IDX_IC_GENERAL_B_TYIT_03,
|*40| TABLE ACCESS BY INDEX ROWID | IC_GENERAL_B | 55344 |  4323K | | 8794 (2) |
|*41|  INDEX RANGE SCAN | IDX_IC_GENERAL_B_TYIT_03 | 111K| | | 314 (3) | 00:00:01 |

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

  3 既然是11号才出现的问题,那说明10号或之前的情况还是OK的,于是考虑,把这些表的统计信息,往回恢复(dbms_stats.restore_table_stats)到9号后再试(默认情况下,统计信息保存时长为:dbms_stats.get_stats_history_retention=31),无效;恢复到5号,无效;恢复到1号,还是无效。

  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 事件命令跟踪:

alter session set events '10053 trace name context forever, level 3';
run sql.
alter session set events '10053 trace name context off';

  但这种获取方式有个前提,需要先在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 字眼很明显

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

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

NUM_ROWS   BLOCKS  EMPTY_BLOCKS   AVG_SPACE   CHAIN_CNT  
AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
    
--------- -------- ------------- ----------- ----------- ------------- ------------------------- -------------------
               0          0                0             0              0             0                      0                0

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

| 17 | NESTED LOOPS              |                                             | 1 |    43 | 1 (100)| 00:00:01  |
| 18 |  SORT UNIQUE                |                                             | 1 |    22 | 0  (0)|                    |
| 19 |   INDEX FULL SCAN        | I_TYBBSALEDAILYBD21  | 1 |    22 | 0  (0)|                       |
|*20 |  INDEX UNIQUE SCAN   | PK_BD_SALESTRU          | 1 |    21 | 0  (0)|                       |  

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

  总结:

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

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

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

  总结2:

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

  补充:

  1 其实除了alter session set events ‘10053 ‘方法外,还可以使用dbms_system包的方法。如:

  开启:sys.dbms_system.set_ev (, , 10053, {1|2}, '');

  关闭:sys.dbms_system.set_ev (, , 10053,0, '');

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

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

  3 Dbms_SqlDiag.Dump_trace 的用法:

PROCEDURE DUMP_TRACE
Argument Name                  Type                    
In/Out Default?
------------------------------ ----------------------- ------ --------
P_SQL_ID                       VARCHAR2                IN
P_CHILD_NUMBER                
NUMBER                  IN     DEFAULT
P_COMPONENT                    
VARCHAR2                IN     DEFAULT
P_FILE_ID                      
VARCHAR2                IN     DEFAULT

P_SQL_ID
= SQL_ID,
P_CHILD_NUMBER
= 子游标
P_COMPONENT
= 注释
P_FILE_ID
= dump file 的文件名。

  如下案例:

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

0
相关文章