黎俊傑 | 2016-07-28 14:37
聲明：部分表名為了脫敏而用XX代替
1、故障現象
（1）一個業務系統輸入用戶名與密碼後無法進入首頁，表現為一直在運行等待，運行緩慢
（2）整個系統無法正常使用，接近停運狀態
2、故障解決方法
調整數據庫參數alter system setevent='10949 trace name context forever, level 1'來關閉“direct path read”（直接路徑讀）特性,使SQL語句可以從緩存中查詢數據，達到降低I/O讀取量，使全表掃描的數據從緩存中讀取，加快SQL語句運行速度的目的。
3、故障原因總結
（1）由於部分SQL語句設計或編寫效率低下，以及表缺少適應的索引，導致SQL語句需要全表掃描，在表較小時，ORACLE數據庫將數據讀取到緩存後，後續雖然是全表掃描，但均是從緩存中讀取，所以問題未體現出來
（2）在表的大小不斷增大後，根據ORACLE 11g數據庫的算法，在表達到db_cache_size（GB）的2%（默認值）以後，認為采用直接路徑讀（跳過緩存，直接從磁盤文件中全掃描讀取）
（3）DX_T_XXVIATE表大小為1GB，在大量反復以direct pathread磁盤重復讀取的情況下，消耗大量的I/O資源，將服務器I/O幾乎耗盡
（4）在主機I/O耗盡的情況下，系統的讀、寫，均幾乎處於癱瘓狀態
（5）在關閉ORACLE 11G數據庫的direct path read新特性功能後，讀取方式恢復到從緩存中讀取，磁盤讀降到“0”，系統恢復正常
4、改進建議
（1）優化訪問DX_T_XXVIATE 相關的SQL語句與設計合適的索引，避免大表全表掃描。
5、故障原因分析
5.1 7月11日故障時段數據庫服務器I/O等待嚴重
5.2 7月11日故障時段磁盤響應非常緩慢
5.3 對比故障當日（7月11日）與上周的I/O磁盤讀取量，比上周大十倍
故障前、中、後磁盤讀取量對比圖：
上面高的藍色線，是故障當日（2016年7月1日，周一）的磁盤Disk Read KB/s指標線
5.4 高度消耗I/O的SQL語句。
上面SQL_ID為b8m6wy846qgbk的SQL語句，physical reads鶴立雞群，可見此SQL語句的影響最為嚴重。
5.5 全表掃描單次超過6秒的表與其SQL語句統計。
統計匯總時間：08：00—10：00
統計時間：08：00—10：00單次掃描超過6秒的SQL語句及時長詳細清單
上面數據顯示，08：00—10：00統計時間內，所有全表掃描超過6秒的表，全部是DX_T_XXVIATE這一張表，涉及到的SQL語句有60多條，執行次數最多的數SQL_ID為b8m6wy846qgbk的語句。
5.6 全表掃描最嚴重SQL語句故障前、後、故障解決後磁盤讀取數量對比
5.6.1 7月11日以前系統運行正常的情況下SQL_ID為b8m6wy846qgbk的語句執行統計信息
--執行統計信息（buffer get很大，但是disk reads為0，判定數據基本從buffer中讀取）：
--執行計劃（對DT_T_OBVIATE全表掃描，預計時間為5分鐘30秒）：
5.6.2 7月11日故障當日SQL_ID為b8m6wy846qgbk的語句執行統計信息
--執行統計信息（buffer get和disk reads都一樣的巨大，基本判定每次數據全是從磁盤讀取到BUFFER）：
--執行計劃（對DT_T_OBVIATE全表掃描，預計時間為5分鐘30秒，從執行計劃的PHV和plan均看出執行計劃在系統故障時與正常時，是保持一致的）：
5.6.3 故障解決後（取7月12日數據）SQL_ID為b8m6wy846qgbk的語句執行統計信息
--執行統計信息（故障解決後，PVH值不變，Disk Reads又恢復到了故障前的“0”，說明每次執行數據又是從BUFFER中讀取的）：
5.7等待事件變化識別數據讀取方式變化比較
看來，系統實際上在2016年7月10日（周日），SQL語句的數據讀取方式就發生了少量的direct path read，系統實際上已經處於間歇式緩慢狀態，到了2016年7月11日（周一），問題特別嚴重，約99%左右的執行是direct path read，導致I/O耗盡，系統癱瘓。
About Me
.........................................................................................................................................................................................................
● 本文來自於微信公眾號轉載文章，若有侵權，請聯系小麥苗及時刪除，非常感謝原創作者的無私奉獻
● 本文在ITpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/
● 原文地址：http://mp.weixin.qq.com/s?__biz=MzA5MDQ5Nzc0MA==&mid=2247483720&idx=1&sn=646e8c1fc34d7afb13484dd7e483534b&scene=1&srcid=0815syZkJIb1ficYRJVbbc6H#rd
● QQ群： 230161599 微信群：私聊
● 聯系我請加QQ好友(642808185)，注明添加緣由
● 【版權所有，文章允許轉載，但須以鏈接方式注明源地址，否則追究法律責任】
.........................................................................................................................................................................................................
長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,學習最實用的數據庫技術。
