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

    最近，遇到一客户，反馈业务响应慢，经过分析后最后锁定到平时执行不到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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29357786/viewspace-2218303/