ORACLE 11.2.0.4 for HPUNIX 業務SQL處理資料量變化導致的CPU使用率超標觸發告警

    某客戶oracle 11.2.0.4 rac for HPUNIX系統，由於業務SQL處理的資料量變化導致的CPU使用率超標，引起告警。

一、問題描述

     2018 年 9 月 22 日， 2:50:00 核心庫的 CPU 使用率達到 87% 觸發告警。

二、提取的分析日誌

 提取資料庫問題時段資料庫告警日誌； 取資料庫 2018 年 9 月 22 日 1:00:00~2:00:00 、 2:00:00~3:00:00 的 awr 及其對比；取資料庫 2018 年 9 月 22 日 1:00:00~2:00:00 、 2:00:00~3:00:00 的的 OSW 並生成圖表觀察 2 個時間點的 CPU 使用情況進行對比。

三、問題分析

  1、oswatch 提取 CPU 使用率相關對比

2018-9-21 1:00:00~3:00:00 2018-9-22 1:00:00~3:00:00

根據 2018-9-21~22 1:00:00~3:00:00 系統使用率對比，確定 2018-9-22 1:30:00~2:00:00 ，系統的 CPU 使用率接近 90% 。

 2、對比 2018-9-21~22 1:00:00~2:00:00 兩個時間段的 awr

    2.1 資料庫 2018-9-21~22 1:00:00~2:00:00 兩個時間的 DBTIME 對比

 從資料庫 dbtime 對比， 2018-9-21 1:00:00~2:00:00 dbtime 指標值是 3923 ， 2018-9-22 1:00:00~2:00:00 dbtime 指標值是 4230 ，相差 300 並不算太多。

    2.2 2018-9-21~22 1:00:00~2:00:00 兩個時間的 LOAD PROFILE 對比

 

2018-9-21 1:00:00~2:00:00 loadprofile parses 指標值高達 337 每秒， 2018-9-22 1:00:00~2:00:00 loadprofile parses 指標值只有 21.2 ； 2018-9-21 1:00:00~2:00:00 loadprofile Hard parses 指標值高達 154 每秒， 2018-9-22 1:00:00~2:00:00 loadprofile parses 指標值只有 10.7 。 從 loadprofile 對比可知， 2018-9-22 1:00:00~2:00:00 ，硬解析比較嚴重，硬解析嚴重會導致 latch 爭用事件，引起資料庫伺服器 CPU 飆高。

  2018-9-21 1:00:00~2:00:00 資料庫頂級等待事件對比， 2018-9-22 1:00:00~2:00:00 等待事件 latch:row cache objects 、 latch:cache buffers chans 嚴重，吻合 loadproile 中硬解析相關指標 Hard parses 、 parses 高的情況。

  2.3 資料庫頂級等待事件對比

  2018-9-21~22 1:00:00~2:00:00 資料庫 TOP SQL ordered by Elapsed Time 對比， 2018-9-22 1:00:00~2:00:00 資料庫自動任務 SQL tunning Advisor 啟用並佔用比較多的 CPU 資源。 

   2.4 2018-9-21~22 1:00:00~2:00:00 兩個時間對比發現的問題 SQL

    2018-9-22 1:00:00~2:00:00 時段有一條 SQL 語句佔用 CPU 比較突出： 1 小時內執行 5645 次，每次執行平均耗時 14.56 秒 。

 

select * from adkmx where ((((faredm=:b0 and jiluzt='0') and yngyjg=:b1) and jioyrq=:b2) and joyisj='LN_JIEX') order by HUOBDH, DAIKZH, MXXHAO

    

      對比 的執行歷史，發現 2018-9-22 日，該 sql 語句 fetch 的資料量 394304 比平時的 6300 多了近 70 倍，行處理 776277 比平時 1400 多了近 554 倍， cpu 消耗 54548.460 比平時的 700 多近 80 倍，這是 在執行時 CPU 消耗過高的真正原因。

四、問題總結

     1 、 在 2018-9-22 日 fetch 的資料量 394304 比平時的 6300 多了近 70 倍，行處理 776277 比平時 1400 多了近 554 倍， cpu 消耗 54548.460 比平時的 700 多近 80 倍，是 在執行時 CPU 消耗過高的真正原因。

2 、另外， 2018-9-22 1:00:00~2:00:00 相比正常時段 2018-9-21 1:00:00~2:00:00 ，資料庫的硬解析比較突出。導致硬解析嚴重的原因是，應用的部分 sql 語句沒有使用繫結變數，資料庫沒有關閉 sql 自適應遊標共享。

       3 、 2018-9-22 1:00:00~2:00:00 相比正常時段 2018-9-21 1:00:00~2:00:00 ，資料庫有 SQL TUNNING ADVISOR 自動維護任務執行，並且消耗比較高的 CPU 資源。

五、建議

  1、   請甲方老師聯絡業務評估 SQL( ) 處理資料量的合理性，避免資料庫伺服器夯死；

  2、   資料庫關閉 sql 遊標共享；

  3、   調整應用 sql 語句使用繫結變數；

 4、關閉資料庫 STA （ SQL Tunning advisor ）；