9426黄大仙内部资料

带姑且表的SQL查询语句的优化方式

发布日期:【2019-04-14】 [返回上一页]

  其实也是我正在一起头时,没有深切阐发比力才搞得这么辛苦:为何CBO会选择第1个要读取的是姑且表?我们晓得,CBO正在选择最佳的施行打算时,选择读取的第1个数据集/表,根基上是前往数据量起码的数据集。正式库上第1个被读取的是姑且表,此时CBO预估其前往值为1,而正在测试库上,CBO预估该姑且表的前往值为25,为何差别如斯大(算倍数)?一些经验丰硕的DBA,看到这些不同,就能当即揣度出姑且表的统计消息有问题,再看看其t_analzyzed 字段,发觉其刚被采集过,这就申明了为何该报表今天没出问题,今天才爆出问题?就是由于刚对姑且表做了统计消息采集,因而,此时能够考虑测验考试删除姑且表的统计消息,看看结果,,,如果一起头时能从这一思出发,后面也不消搞得这么辛苦,,,

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

  CBO正在计较施行打算时,发觉此环境后,认为该表的记实很是少(=1),于是采用了最快速的嵌套轮回(NL)来读取数据;

  正式库的施行打算为何不从此步起头?莫非是该索引的 CLUSTERING_FACTOR 值过高?比力两库的环境,发觉正式库的只比测试库的高一点,但测试库只到9月份的数据,而正式库则是10月份的数据,莫非刚好是超出跨越的这一点导致非常?虽然不大相信,但意外验考试心有不甘,然而,点窜(dbms_stats.set_index_stats)此参数值后再测,仍是无效。

  【IT168 原创】毛病突发:11号上午,收到系统使用人员的反馈:“发卖日报”无法查询出成果,曾经期待一个小时,屏幕仍然是灰的,而以往该报表2分钟内即可出成果,IT部的同事看一下怎样回事,带领正在等报表。我登录数据库,查询当前系统的历程环境下图(2),发觉确实有些历程曾经运转3600+秒,捕获出其施行打算,如:下图(3)

  但这种获取体例有个前提,需要先正在SQLPLUS里号令,运转SQL语句后,再封闭号令,但这种方式对我的环境行欠亨,由于报表语句中涉及到一姑且表,姑且表的数据是正在使用系统发出报表查询指令后姑且生成,而我并不清晰此过程中这些姑且数据是若何生成,没有姑且表的数据,解析出来的施行打算过程必定取系统现实的环境分歧,那就没意义了,,,但正在11G,ORACLE新推出了包DBMS_SQLDIAG,利用该包中的DUMP_TRACE过程,能够获取正正在运转的SQL的施行打算的生成过程。如许,就能达到我的需求(后面有申明)。

  何为正式库的施行打算会变成如许?印象中,近期我没对正式库做过任何改动, 并且,报表正在今天查询(10号)时,仍是一般的,到了今天就一下子俄然非常起来,这种变化给人一种异常的感受:该报表涉及到的数据量,到了一个量变惹起量变的程度,导致了施行打算突变非常。但非论是怎样变,能够确定的是,是表的统计消息导致了施行打算非常,以致报表无法查询出成果。现正在要思虑的,是若何恢复回本来的施行打算?

  存一些环节数据,数据量不会太大,如许,CBO正在采样预估时(LEVEL=2、3),凡是是比力精确。

  颠末几天的优化工做,心里对姑且表的优化方式印象很是深刻了。此前,我总头疼带有姑且表的查询SQL,认为因为不清晰其数据量,无法判断语句的施行打算能否最佳?但这番下来,对这类语句的优化方式,有了个总体全面的认识:方式很简单,就是把所有的姑且表的统计消息都清空,CBO发觉姑且表的统计消息为空时,将动态采样。为避免误采集姑且表,能够正在清空姑且表统计消息后,把姑且表的统计消息给锁住,如许,该表就不会再被采集,除为地(dbms_stats.gather_table_stats)设置采集参数force=true。

  为什么说,对姑且表采用动态采集的方式是靠得住的?我思虑,缘由有二:一方面,ORACLE的姑且表有两品种型,要么事务竣事时断根姑且的数据;要么历程/会话竣事时清空姑且表的数据,这必定了日常对姑且表的采集统计是无意义,只能正在使用时姑且采集。另一方面,9I当前,ORACLE采用了当地办理这一新的磁盘办理方式:采用当地办理方式后,ORACLE正在存储数据的块里记实了其具有哪些块,哪些BLOCK块被利用等这些METADATA,如许,优化器正在采样部门BLOCK块里的数据后,就能大致估算出整个段/表有几多数据,以及某个字段的某个值的比例。而雷同上述问题中SQL语句带的姑且表,这类姑且表凡是只是姑且保

  5 想来想去,只要最初一招了,既然报表正在测试库查询是OK的,而正在正式库的非常,那就比力该语句正在两库中的施行打算,看看有哪些分歧,但愿能从中找出眉目。正在这里,要表彰一下11G的新特征,据我领会,正在11G之前,要获取SQL语句施行打算生成过程的方式只要一种,就是利用 10053 事务号令:

  别离正在正式库和测试库发出号令(Dbms_Sqldiag.Dump_Trace)后,获取了两库对该SQL的施行打算的文本,再利用文本比力东西WinMerge来比力两文件,成果发觉,明明是不异的内容,WinMerge 东西却显示为分歧,估量是该软件的算法有问题(1.7 版本,10年前的软件),比力了几十处后,没发觉什么非常,此时,曾经是13号的下战书(前面列的1,2,3,4点思和测试环境,是前2天的测试成果),对这一次的阐发工做曾经是,上很疲倦,很想放弃了,但一想,问题没处理,下周一开工时问题再现,系统使用人员又要嗷嗷急叫,各类邮件,德律风催个不断,这种排场一看我就头大,希望ORACLE手艺支撑吧,说不定被拖上几个礼拜,于是咬咬牙:既然是该版本的算法有问题,都过了这么久了,该当有新的WinMerge版本了吧,于是正在网上查找并下载了个 2.3 版本的安拆,利用时发觉,新版公然比老版本好用,标出来的都是分歧点,不只如斯,新版还把两者分歧之处列出来,让人一目了然,比力过几处后,来到SQL语句中,姑且表的统计消息处,发觉有些分歧,正式库里对该姑且表做了统计,而测试库则没有,莫非,,,如下图:NO statistics 字眼很较着

  印象中,该报表以前优化过,耗时也就几秒到几十秒,效率算得上常高的。察看此施行打算,CBO预估的查到数据很是少,但因为报表的SQL语句中利用上了姑且表(TYBBSALEDAILYBD21 ),而姑且表的数据正在库中是无法查看到的,我也不清晰姑且表的数据是若何生成的,因而,无法判断CBO预估的姑且表的行数能否精确,该若何下手?

  能够看到,测试库上的施行打算,取正式库的完全纷歧样,并且正在测试库上的查询的效率一般,和以往的一样,几十秒即出成果,明显,问题出正在正式库的施行打算上,

  1 从头采集该SQL语句中涉及到的表的统计消息,但不包罗姑且表(因为是正在别的的历程里做的采集操做,而此时的姑且表是没无数据的,采集了也没意义),采集完后从头运转报表,发觉施行打算不变,申明方式无效。

  2 若是会话级的姑且表,数据量大时,能够考虑正在插入数据后,姑且采集姑且表。这凡是正在存储过程中,借姑且表来做数据过度时利用。

  6 删除掉(dbms_stats.delete_table_stats)正式库里该姑且表的统计消息,再测试,GOOD,施行打算公然变回和测试库的一样了,,,一霎那,茅塞顿开,大白了根源所正在,缘由很简单,正式库中的姑且表不何时被采集过,这是个事务级的姑且表,非论是正在本历程,仍是正在此外历程采集该表,其统计消息必定都是0,如下:

  4 此时,又不由思疑,可能不是统计消息导致的施行打算非常,但再一想,除了统计消息外,似乎没有此外缘由了,既然测试库的施行打算是OK的,那就把测试库中的这些表的统计消息,导入(dbms_stats.export_table_stats/import)到正式库中再测试看看,就测试适才正在测试库上查询的时间段;此外,为稳妥起见,又对比了两库的系统参数值 aux_stats$(dbms_stats.gather_system_stats 采集),以及其时这两报表的sesion的参数环境(v$ses_optimizer_env),对比成果,系统,历程的变量值一模一样,但测试成果,照旧无效!

  此时,想起测试上,有上月对该库进行RMAN恢复测试后留下的测试库。于是启动测试两头件,让测试两头件指向该测试库,测验考试正在查询该报表,看看运转环境若何?

  相关链接: