蔣健，雲趣網絡科技聯合創始人，Oracle ACE，11g OCM，多年Oracle設計、管理及實施經驗，精通數據庫優化，Oracle CBO及並行原理。雲趣鷹眼監控核心設計和開發者，資深Python Web開發者。
春節前某天下午4點的樣子客戶打來電話，說近期的某個關鍵系統經常HIGH CPU，業務頻頻反饋說數據庫運行極慢，CPU使用率頻頻100%，而且主機無法ssh登錄，年底業務高峰期，系統性能故障對業務影響極大。
甲方DBA已經自己優化了差不多兩天了，效果不明顯，需要到場優化。
過來之前問客戶瞭解了些大概信息，這個庫歷史上CPU使用率並不高，年底業務高峰期加上可能要拉報表所以負載會高一些。主機為AIX系統，虛擬化後CPU有48個，內存96G單機的數據庫，版本為10.2.0.5的。
一、應急處理
到場後情況不樂觀，客戶那邊忙成一團，監控中看了下歷史CPU情況，如下：
剛準備上機操作，發現數據庫主機又開始又登錄不進去了，客戶DBA嘗試了幾分鐘後，終於登錄進去，一查果然CPU 100%了，為了應急保證業務，臨時決定先殺一批沒有事務的會話。
HIGH CPU的時候觀察了下，主機層面user大約92%，sys的約7，幾乎沒有io wait的CPU，也沒有頁交換出現，CPU消耗高的進程基本上都是Oracle用戶的。
反覆運行以上命令後，CPU使用率勉強穩定到了90%，基本上可以開始分析性能故障了。
二、AWR報告信息
AWR報告開始生成不出來，一些常規操作後，拿到了故障時間段的AWR報告。先看下大概的信息，採樣的一個半小時內數據庫AAS（平均活躍會話數）已達84，redo量比較大，硬解析、登錄、排序等指標沒嚴重的問題。
其實大部分情況到這步了很多DBA基本上就直接搜索`SQL ordered by CPU Time`或是`SQL ordered by Gets`去定位TOPSQL了。
那麼這裡也貼下對應的部分，並沒有明顯的TOPSQL，甚至top1也就佔1.6%。那麼這時候怎麼辦呢？
三、是硬解析導致TOPSQL不明顯麼
通過`force_matching_signature`這個標誌位可以去判斷下是否其實有那種高佔比的TOPSQL由於應用沒有使用綁定變量而被掩蓋，由於監控中帶功能識別這種問題，我沒寫SQL去判斷。
通過監控的識別快速打破了這種美好的願景，哪怕強制綁定變量後TOP 1的SQL數據庫時間佔比也不超過3%，top10加起來目測也不超過20%。顯然找到TOPSQL再實施優化，目前看起來行不通，畢竟找不到TOPSQL。
四、換個角度能否突破
其實在之前的文章中也提到過，SQL層面的定位難以突破時，可能需要聚合到對象層面（一般涉及訪問路徑優化），也就是對象層面。
通過搜索`Segments by Logical Reads`可以發現上帝在為你關上一扇的門的時候，順便也把窗戶關上了......
沒錯，聚合到了對象層面，top1只佔16%，這幾乎就說明想優化兩三個SQL就解決問題的想法不現實。
五、真相其實...
其實現場，我並沒有太多的困惑，畢竟寫文章需要，上面的思路基本是按照客戶DBA處理的思路延伸了下。
真相是上來我就已經看過了以下信息，基本換了大方向了。在看了這個信息後大家有別的思路了麼。
沒錯，top5等待事件比較有趣。
正常CPU-bound的數據庫系統，latch、mutex相關的等待伴隨也比較多，會出現在top5/10裡，而這個100% CPU的系統竟然是top5，等待事件卻是`User I/O`類，常規指標`User I/O`與`db file scattered read`的平均IO延遲是2ms，似乎IO沒成為瓶頸，與之對應的是開始主機層面CPU的io wait很少。
雖然看似IO也有一定消耗的，但並不合適直接去看`SQL ordered by Reads`再深入去看TOPSQL，畢竟要解決的問題是系統100%CPU。當然這裡也貼圖看下：
依然沒有大的TOPSQL。
六、另幾個坑？
在看到了top5的等待事件後，我馬上搜索了下log file出現瞭如下信息`log file sync`39ms`log file parallel write`也達到了20ms。
10g沒`redo synch poll writes`，不用檢查`_use_adaptive_log_file_sync`參數。
看到這裡是否感覺可能問題其實是出在存儲上呢？
常見的存儲IO性能故障引起日誌同步變慢，AAS大幅提升最終引發故障的場景屢見不鮮。
一般來說從數據庫層面會以後臺等待事件`log file parallel write`的延遲來衡量存儲順序寫的性能，那是否我們應該把關注重心轉移到存儲IO性能上呢。
其實以個人的優化經驗看來，`log file parallel write`的高延遲最終能確認是存儲問題比例其實不高，剛好客戶這邊XIV的軟件不知是有異常還是什麼原因，查不到過去一小時的IO寫延遲。
關於`log file parallel write`，雖然從事件的定義上它可作為衡量存儲性能的重要指標，但由於事件的登記都是由LGWR進程去做的，也就是受到進程調度的影響，在CPU使用率高的時候（或是cpu_count超過128的時候），這個指標其實並不準確，更多信息可以去參考Oracle support的文檔（34583.1）。
是繼續糾結在IO性能這裡呢，還是按照客戶DBA的建議優化那些他們抓出來的幾頁長的報表SQL呢？客戶的CTO在線等你給建議。
存儲這邊也可以讓存儲維保廠商繼續過來確認存儲性能問題，哪怕是加內存，進而調整SGA，甚至是keep一些關鍵表都是可操作的。
而報表SQL語句這邊呢，要改寫，要優化，要協調開發，也可以商量，畢竟業務影響實在太大。
但有一點，在做之前需要明確的就是，這些接來下打算做的操作能否將CPU降低一個合理的比例保障業務的正常運行，這是CTO肯定會問的問題，白費力氣忙活一場，沒解決問題那肯定很尷尬的。
七、決策
不知不覺，時間已經到了快下班的時候，客戶CTO表示今天晚上做些操作，希望明天能看到效果。這時候如果是你，接下來打算怎麼處理，又打算給客戶一個降低CPU多少的期望呢？
我當時給出的保守期望是降低20%，不改參數，只是在三張表上建立四條索引。哪三張表？參考下圖前三張表：
四條索引的依據則三張表的數據訪問路徑的聚合，其中一條對應的SQL當天運行情況如下：
可以發現這種SQL瞬間的併發非常高，ASS能達到40+，但過了峰值可能又是別的SQL上來，並且這種SQL通過`force_matching_signature`聚合後，一個小時的採樣數據都進步了前20，這種SQL在AWR也不一定能進AWR各個維度的top10SQL，這類SQL的發現不太適合使用AWR報告。
當然定位了top segment後，通過表名的關鍵字兩邊加上空格，也可以嘗試下搜索相關SQL。
如果手工寫腳本分析，建議聚合分析`dba_hist_active_sess_history`,`dba_hist_sqlstat` 等視圖。
本次優化最終選擇的是優化SQL的方式處理，而且是優化物理讀消耗多的SQL入手，原因是對系統故障的猜測：
部分高IO消耗的SQL，影響了redo的刷新性能（自動巡檢報告中，系統redo未隔離數據文件），系統併發性上去後，CPU消耗過高，LGWR進程CPU調度不能保證，（巡檢報告中，常見參數`_high_priority_processes`沒有設置LGWR），又進一步加劇DML慢的問題，系統併發從而更高，不斷惡化。
`_high_priority_processes`這個參數並未修改，因為該參數修改需要重啟數據庫，而通過臨時處理時renice可以達到相似效果，而不用重啟。所以我計劃第二天現場發現CPU高過80%時，再手工調整。這樣也方便自己對單個調整能取得的收益有個更好的認識。
八、開獎
第二天的情況出乎意料的好，幾乎腰斬，CPU偶爾達到60%，以至於根本不用renice操作，我都忍不住去群裡確認了下當天的業務確實沒有任何變更。
做了份AWR報告，我關心的`log file sync`，`log file parallel write`如圖勉強還行，但跟昨天比好太多了。
系統AAS也降到了30左右。
九、總結
這次故障處理的過程反思可以發現，其實不少的隱患，在日常的巡檢，SQL審核中都可以被發現，但到了故障現場，可能由於環境的複雜，被放大後出很多衍生故障，分析定位起來則麻煩不少。日常的巡檢，SQL審核多花些精力，定期更新自己的知識庫，這些時間最終都是高回報的。
1、100% HIGH CPU風險
100% CPU使用率的風險是大部分系統都存在的，最難受的是發生了HIGH CPU後，可能主機SSH都無法訪問。這種風險建議在CPU_COUNT層面做調整，該參數默認值是使用全部的CPU資源，考慮對其進行調整為邏輯CPU-2（單機數據庫）。
RAC架構下同一集群有多個數據庫的結合具體情況考慮實例CPU隔離，參考support文檔（1362445.1）。
resource manager在處理整體使用率上不太好用，業務上AP、TP混合的話，可以通過RM調整業務的優先級，保障重點業務。報表拖垮了交易系統的問題，可以通過RM來實施保障。
2、LGWR日誌同步性能隱患
高CPU使用率（一般超過80%）或CPU個數過多情況下，LGWR性能可能受CPU調度影響較大，建議`_high_priority_processes` 設置LGWR。該參數調整需要重啟數據庫，建議規劃後集中調整。
掌握數據庫新思維，獲得更優操作啟發
不妨來Gdevops北京站學點獨家技能
2019 Gdevops全球敏捷運維峰會-北京站
閱讀更多 dbaplus社群 的文章
