Oracle 11g新特性direct path read引發的系統停運故障診斷處理

黎俊傑 | 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,學習最實用的資料庫技術。