本文為墨天輪數據庫管理服務團隊第128期技術分享，內容原創，作者為技術顧問龔旭明，如需轉載請聯繫小墨（VX：modb666）並註明來源。如需查看更多文章可關注【墨天輪】公眾號。

問題概述

數據庫在執行批處理SQL：2j1fr6dwysvww 時間過長，應用方反饋超時。

問題原因

1、ASH及AWR方面

通過分析問題時間OEM中的ASH Analytics，發現該條SQL的等待事件均為db file sequential read，如下圖：

AWR中在一個snapshot週期中db file sequential read 的平均等待時間達到6ms，總共等待時間為2218s，等待383297次，該等待事件是個非常常見的 I/O 相關的等待事件，通常顯示與單個數據塊相關的讀取相關的等待，在大多數的情況下，讀取一個索引塊或者通過不合適的索引讀取一個數據塊時，或者可能在多表連接中，表的連接順序存在問題，沒有正確地使用驅動表，都會記錄這個等待。

查看對應時間AWR中buffer的命中率僅為51.83%，代表database buffer cache命中率極低，正常應大於95%，因為該SQL每天只執行一次，執行時會先檢查databbase buffer cache中是否存在該條SQL需要的數據，如不存在會將數據從磁盤中讀取到數據庫中的database buffer cache，該過程就會產生大量的物理讀同時產生db file sequential read，消耗大量的I/O資源。

AWR中TOP SQL ORDER BY I/O WAIT TIME也將I/O資源的高佔用指向該條SQL。

2、SQL方面

查看SQL 2j1fr6dwysvww，發現本次問題時間該條SQL的執行時間為1893.403秒。

查看歷史snapshot中該類型SQL，發現SQL中無綁定變量，且執行週期為每天凌晨一次。

SQL:2j1fr6dwysvww文本：

分析當前2j1fr6dwysvww執行計劃

發現SQL執行計劃中存在TABLE ACCESS FULL 及INDEX FAST FULL SCAN，表示SQL執行效率低，存在使用不合適的索引會出現該種情況。

查看錶信息，發現存在大表tbl\_tem\_log,當前大小為13G， 表中存在的索引對於SQL: 2j1fr6dwysvww並未有適用於該條SQL的有效索引，隨着表中的數據量越來越大，該條SQL執行效率會越來越低。

當前基於ORACLE的CBO優化規則中在索引中挑選最適用於該條SQL的索引也會掃描表中超過100W行的數據，該過程產生大量的物理讀，消耗I/O資源；並且在該條SQL的select條件中還存在子查詢where條件中還嵌套查詢，都為大表tbl\_tem\_log  hash join了表tbl\_mer\_info，在該執行計劃可以看出大表tbl\_tem\_log做為連接的驅動表，在根據驅動表返回的行數去匹配被驅動表的數據，超過100W行的數據再去匹配表tbl\_mer\_info中的數據，產生大量的物理讀，再次消耗I/O資源。

3、數據庫主機

通過IO監控腳本，在未執行該條SQL期間，數據庫磁盤也存在響應時間過長的現象。

4、分析結論

通過以上分析，該問題產生的原因有以下：

• 表tbl\_tem\_log和表tbl\_mer\_info數據量逐漸增大。
• 表tbl\_tem\_log和表tbl\_mer\_info中未有適用於SQL: 2j1fr6dwysvww合適的索引。
• SQL語句中存在不規範的寫法，未使用綁定變量，存在不合適的hashjoin。
• 磁盤存在異常的I/O響應緩慢現象。

解決方案

• 對錶tbl\_tem\_log和表tbl\_mer\_info制定合理的數據備份及清理策略，或將大表改造為分區表。
• 在表tbl\_tem\_log和表tbl\_mer\_info中建立適用於報告中這類型SQL的索引。（不是優先選擇，表tbl\_tem\_log中已有5條索引，建立過多的索引會加大該表的開銷）。
• 對該條SQL進行改造，使用綁定變量，並且使用正確的連接方式，降低SQL所產生的物理讀。（在建議1或2完成的情況下）
• 排查數據庫服務器磁盤I/O異常現象。

墨天輪從樂知樂享的數據庫技術社區蓄勢出發，全面升級，提供多類型數據庫管理服務。墨天輪數據庫管理服務旨在為用户構建信賴可託付的數據庫環境，併為數據庫廠商提供中立的生態支持。
墨天輪數據庫服務官網：https://www.modb.pro/service