ITPub博客

首页 > 数据库 > Oracle > ORACLE analyse table方式收集表统计信息导致SQL执行计划不准确而性能下降

ORACLE analyse table方式收集表统计信息导致SQL执行计划不准确而性能下降

原创 Oracle 作者:清风艾艾 时间:2018-10-31 21:50:40 1 删除 编辑

    最近,遇到一客户,反馈业务响应慢,经过分析后最后锁定到平时执行不到1秒的SQL语句,今天突然执行时间变成

半分钟。处理过程如下:

    取问题时段的AWR,查看数据库负载,发现数据库负载不高:

    查看数据库顶级等待事件,发现是文件离散读,基本可以锁定是表扫描相关的问题:

    查看问题SQL,Order by Elapsed Time,发现一条执执行次数不算多,执行耗时特别长的SQL:

      如图SQL ordered by Elanpsed Time所示, 接下来分析数据库awr的TOP SQL消耗时间最多的SQL 是fbh8jvk9fvdkh,平均执

行时长239.54s,经与甲方人员核实是监控到的慢业务SQL语句。

 将问题SQL改造,方便性能测试,改造后的语句如下(sql_tun110是为了方便找SQL_ID):

select /*+sql_tun110*/count(*) from (
select q.vc_profitclass,
       ot.d_date,
       ot.d_cdate,
       ot.c_fundcode,
       q.vc_fundname,
       ot.F_Netvalue,
       ot.F_Incomeunit,
       ot.F_Incomeratio,
       ot.F_Incomeratio_30days
  from datacenter.crm_tfundday ot,
       datacenter.crmmg_tfundtypeset q,
       (select max(t.d_date) as ddate, t.c_fundcode
          from datacenter.crm_tfundday t
         where nvl(t.f_incomeunit, 0) >= 0
         group by t.c_fundcode) it
 where q.vc_fundcode = ot.c_fundcode
   and ot.d_date = it.ddate
   and ot.c_fundcode = it.c_fundcode
   ) t;

    获取上述SQL的执行计划:

     如图上所示,问题SQL执行计划显示其Cost值只有61,但是consistent gets有5274937之多,可以确认是sql语句的

执行计划出现问题导致sql性能下降。经过与甲方人员沟通,得知上午对datacenter以analyse table的方式进行过统

计信息收集,经进一步查询最近只有2018年6月13日执行过统计信息收集如下图所示。

  因此得出结论: 由于datacenter数据库统计信息不准确,使得数据库SQL优化器参考的统计信息不准确,导致数据库优化器产生了性能极差的执行计划,sql执行耗时较长,影响业务响应速度

 重新更新表的统计信息:

execute dbms_stats.gather_index_stats(ownname => 'DATACENTER', indname =>'PK_TFUNDTYPESET', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);
execute dbms_stats.gather_table_stats(ownname => 'DATACENTER', tabname =>'CRMMG_TFUNDTYPESET', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    验证效果,原先SQL的逻辑读5274937降低到6290,SQL原先执行30多秒,现在执行耗时0.6秒:

优化前的执行对比:




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29357786/viewspace-2218303/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
个人喜欢IT行业,目前从事数据库工作,包括Oracle、mysql、mongodb、sqlserver等数据库的维护,喜欢专研开发技术,尤其对java程序的开发感兴趣。工作经历上,在中国联通系统集成公司、中公网医疗信息技术有限公司做过数据库技术支持;目前在海量数据,负责华东区oracle、mysql、mongodb的维护工作。

注册时间:2015-01-30

  • 博文量
    176
  • 访问量
    203596