單條SQL造成某核心BOSS系統雪崩-記一次現場Oracle資料庫異常分析恢復-orastar

orastar發表於2017-05-04

1、故障描述

4月11450-1605 某核心BOSS系統資料庫服務異常, 部分業務無法對外提供服務。

2、問題分析



2.1    資料庫線上活動程式分析

用統計學的方法分析資料庫每個時間點的線上活動程式數(資料庫伺服器為16C ORACLE 2結點RAC 版本11.2.0.4)

331資料庫線上活動程式: 4個,低負載,4個程式共享32CPU

41資料庫線上程式: 60個線上活動程式, 2個程式爭用1CPU(以前是1個程式共享8CPU!!!),資料庫服務異常。

 

2.1.1     分析結論

由上面兩圖分析,41日資料庫負載為正常情況的15倍,資料庫服務異常。

2.1.2     提出疑問

哪些程式及SQL造成資料庫活動程式大幅增加?(下章分析)

 

2.2    資料庫活動SQL分析

對資料庫執行SQL進行統計分析,發現資料庫後臺大批次程式在執行SQL: 07ht123456789,資料庫事件為:全表掃描,且無其它事務阻塞,統計該SQL在同一時間點的執行情況。

331SQL: 07ht123456789同時執行活動程式數均值:3個(日常活動程式4個,它佔3個,平時就有效能隱患)

41SQL: 07ht123456789同時執行活動程式數均值:60個

 

2.2.1     結論

SQL_ID:07ht123456789在同一時間點的執行數量為平時的20倍,可以確認該SQL是造成資料庫服務異常的“病灶” (下章對此異常SQL分析)

2.3    異常SQL分析

2.3.1     SQL內容

SQL_id: 07ht123456789

Sql語句:

select count(distinct p.cn)

  from ht_idx p

 where p.loginstate = 'true'

    and p.logindate = :3

2.3.2     執行計劃


2.3.3     業務表資訊

檢視錶PORTAL_IMS_IDX資料量為: 4492744

檢視此表資料保留天資料:31

 

3、為什麼以前都正常



為什麼以前都正常,今天突然異常呢?這條語句是新程式嗎?見下文分析。

3.1    問題一、SQL是新程式嗎?


1月份此SQL都在執行,證明不是新程式。

3.2    問題二、SQL每小時執行多少次?

系統名稱

SQL_ID

時間

執行次數

平均執行時間()

核心系統

07ht123456789

3288-9

3106

1.481

核心系統

07ht123456789

3298-9

2966

1.514

核心系統

07ht123456789

3318-9

2855

1.561

核心系統

07ht123456789

418-9

1141

108.441

單條SQL 每小時執行3000!!!

3.3    問題三、SQL今天比平時慢100倍?

檢視28SQL統計資訊

檢視31SQL統計資訊

檢視41SQL統計資訊

Buffer gets: 從資料庫快取讀資料。(邏輯讀)

Disk reads: 從資料庫儲存讀資料。(物理讀)

原來大批次的資料都是從儲存讀資料,所以造成資料庫服務異常。

3.4    檢視AWR是否與上述一致?

檢視331AWR報告,物理讀正常。

檢視41日的AWR顯示有大量的物理讀。

 

結論:大批次的物理讀造成SQL語句異常緩慢,造成資料庫服務異常。

3.5    41日有如此大的物理讀?

4月1日凌晨系統升級重啟過資料庫,資料庫重啟後快取會被清空,第二天大批次存在效能問題的語句執行時,大量物理讀,造成資料庫服務異常。

3.6    總結

    400W資料量全表掃描查詢每小時執行3000次,在資料庫重啟快取被清空後,大量物理讀造成資料庫服務異常。

4、資料庫恢復



將業務表進行歷史資料歸檔後,資料庫恢復正常。

5、故障原因



1、      SQL(SQL_ID: 07ht123456789) 效能問題,400W資料全表掃描查詢,造成後臺大批次程式同時執行此語句,造成資料庫異常。

2、      ht_idx(業務表)故障時儲存了31天曆史資料,未及時進行歷史資料歸檔(已與業務溝通該表應該每天進行歷史資料歸檔)

3、      資料庫重啟後快取被清空,業務高峰期大量物理讀造成資料庫異常。

6、最佳化建議



1、      建議開發廠商最佳化語句(SQL_ID: 07ht123456789)

2、      建議及時對錶ht_idx進行歷史資料歸檔。

3、      建議加強對資訊系統資料庫監控,及時發現資料庫異常,提前消除安全隱患。

4、      加強資訊系統常態SQL稽核最佳化,提前發現並最佳化TOP SQL

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31442014/viewspace-2138413/,如需轉載,請註明出處,否則將追究法律責任。

相關文章