ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降

    最近，遇到一客戶，反饋業務響應慢，經過分析後最後鎖定到平時執行不到1秒的SQL語句，今天突然執行時間變成

半分鐘。處理過程如下：

    取問題時段的AWR，檢視資料庫負載，發現資料庫負載不高：

    檢視資料庫頂級等待事件，發現是檔案離散讀，基本可以鎖定是表掃描相關的問題：

    檢視問題SQL，Order by Elapsed Time，發現一條執執行次數不算多，執行耗時特別長的SQL：

      如圖SQL ordered by Elanpsed Time所示， 是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日執行過統計資訊收集如下圖所示。

  因此得出結論： 。

 重新更新表的統計資訊：

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秒：

最佳化前的執行對比：