【恩墨學院】超實用運維經驗：TEMP表空間不足、熱塊競爭經典案例

超實用運維經驗：TEMP表空間不足、熱塊競爭經典案例

系統執行過程中可能遇見各種各樣的效能問題，如果僅僅是當前系統的效能問題，我們可以透過查詢Oracle的資料字典檢視、hanganalyze、systemdump、event等方法來分析效能問題。

而如果是歷史的效能問題，則可以透過awr、ash、addm等診斷工具。但是有些瞬間出現的效能問題可能在awr、addm的報告中並不明顯或者時間太短導致取樣粒度無法獲取，此時則可以透過歷史的ash相關的資料來獲取。

下面以歷史temp表空間不足和熱塊故障的兩個案例來展示如何對歷史和當前的效能問題來分析。

案例之temp表空間不足

一、 故障現象和描述

客戶一套核心的系統，在執行某個核心業務時總是報出temp表空間不足問題，相關業務也會因為該異常導致無法執行完畢，經和相關人員溝通了解到該業務是包含儲存過程、匿名塊還有某些單獨執行的SQL，而他也無法知道是哪個部分執行導致temp表空間消耗殆盡而丟擲錯誤。

二、 故障分析—警告日誌

對於該問題我們的常規思路就是登陸系統檢視temp表空間使用率，可是登陸系統後查詢temp表空間使用率非常低，消耗temp的相關會話和SQL_ID如下：

總共消耗temp才有6MB，而這個資料庫的temp容量有32GB，難不成業務反饋的是有問題的，系統沒有出現temp不足的問題，既然出現了temp問題，那麼資料庫的alert日誌肯定是會記錄的。

業務反饋屬實，系統確實曾經出現過ORA-1652: unable to extend temp segment by 256 in tablespace TEMP的錯誤，接下來就是如何去抓取歷史的TEMP問題了。

三、 ASH抓取瞬間出現的TEMP問題

這個故障能否重現了，推測只要這個業務執行了就會出現，但是如果要業務程式再次失敗對於業務是有影響的，並且這個ORA-1652: unable to extend temp segment by 256 in tablespace TEMP持續時間也不會特別長，因為一旦丟擲這個錯誤，相關SQL就會執行失敗，已經被使用的Temp就會釋放掉，也不一定能夠能夠抓取相關的問題SQL。

此時回到了在上面介紹的如何對歷史問題進行診斷，這裡要推出oracle的歷史效能問題診斷利器ASH，注意這個AWR取樣是無發獲取到相關資訊的，並且AWR其實針對的是資料庫整理效能問題，對於這種個別會話出現ORA-1652錯誤並不一定能夠抓取到。

v$active_session_history檢視是每秒鐘對活動的會話取樣一次，而dba_hist_active_sess_history檢視是每十秒對活動會話取樣一次。此時由於告警日誌中記錄的ORA-1652錯誤時間段的v$acitve_session_history檢視資料還存在，這裡就直接擷取了其中故障時段臨近幾分鐘的資料插入到ash0322 臨時構造的表中

這裡我們使用了v$active_session_history檢視中的temp_space_allocated欄位來獲取了系統每個sample_time下相關SQL_ID的temp表空間資源消耗。

透過上面的查詢已經得知就是SQL_ID 9q1kqvzvnzbjc單次消耗就需要16GB的temp表空間，而資料庫的Temp表空間容量只有30G，這個SQL只要有併發或者節點之間一起使用則馬上就會出現 ORA-1652: unable to extend temp segment by 256 in tablespace TEMP的報錯。

四、 剖析SQL 效能、最佳化改善

temp消耗在這個SQL執行計劃中有兩部分，一部分是sort order by stopkey；另一個部分則是hash join，Sort order by stopkey是排序時的分頁操作，消耗的temp空間是有限的，hash join才是消耗temp的根本原因。

從執行計劃ID 6得知表es_order_items ot作為驅動表返回的結果集是87M，也就是8千多萬的資料行，而我們知道在Oracle的hash join運算時，由於PGA空間有限，如果驅動表返回的資料行較多，則構造hash table可能會在temp表空間也就是磁碟上執行；對於87M的資料量在構造hash table時必須是需要大量使用temp表空間，正是這個hash join導致了資料庫的temp表空間消耗殆盡。

sort order by stopkey

這個雖然是排序，也需要將所有的資料取出來後來排序，但是消耗的記憶體或者temp空間是有限的，因為Oracle不需要將所有資料取出來全部一起排序，而是可以分批進行，每次保證在排序區域只需要有固定行數的資料。

這個我們可以認為這個排序區域只有10行資料，然後每次新的資料進入到排序後就進行比較，發現這條資料是否最小，如果不是最小就將排序區原來10條資料中的create_time最小那條資料踢出去，這個排序演算法是我個人認為理解。

Oracle的演算法應該更加科學點，不過可以先這麼理解，總之排序分頁環境下temp消耗是有限的，大家可以手動建立一個非常小的temp tablespace關閉自動擴充套件，構造一個億數量級的大表來排序，但是隻取其中的前10行資料，看是否temp報出空間不足，也可以用gather_plan_statistics來檢視SQL實際的執行的temp資源消耗等方法都可以驗證。

如何最佳化改善：

• 由於該SQL有rownum限制，其實相當於一個分頁取資料的方式，如果能夠去掉order by o.create_time desc的排序,則該SQL能夠走nested loop的方式，並且在nested loop階段就走counter stopkey，既不用排序也不用掃描所有表段或者索引段後來做表關聯。

• 如果業務上必須排序，也可以在表es_order上建立create_time索引，不過需要注意的是必須建立global級別索引才能保證該SQL返回的資料是有序的（因為這裡沒有走分割槽裁剪，是掃描了多個分割槽，如果local索引在跨分割槽索引範圍掃描時並不能保證取出的資料是有序的，Oracle不會走nested loop的執行計劃），這樣該SQL在表關聯階段就會走nested loop的方式。

• 新增更高效的過濾條件，控制驅動表返回的資料量。

綜述：

• 當遇見歷史問題時，需要結合警告日誌、程式跟蹤檔案、業務日誌等綜合分析，在資料庫層面上ASH提供豐富的Session歷史資料。

• ASH中的兩個檢視v$active_session_history、dba_hist_active_sess_history，透過對ASH資料探勘，找到相關的等待事件、SQL_ID等資訊深入分析。

案例之熱塊競爭

一、故障現象和描述

客戶反饋核心系統的cpu使用率達到了90%以上，業務超時嚴重，需要儘快介入處理。

二、故障分析介入等待事件

對於實時的效能問題，我們優先考慮Oracle的V系動態檢視，實時登陸系統發現資料庫出現大量的latch：cache buffer chains、latch free等待。

• 資料庫主要等待事件為latch：cache buffer chains和latch free兩種等待，等待相關的SQL不相同，但是實際都是同一業務型別的SQL。

• Latch用於保護SGA中共享記憶體結構。Latch就像是一種快速被獲取和釋放的記憶體鎖，用於防止共享記憶體結構被多個使用者同時訪問。

• latch：cache buffer chains等待是一個熱塊現象，該等待根本原因是在高併發環境下的高邏輯讀請求導致。

• Latch free需要根據具體的latch#編號來分析，在Oracle 10G之前所有的latch等待都被記錄為latch free等待。

三、迴歸SQL最佳化

出現latch：cache buffer chains、latch free等待的SQL都是同一業務型別發起的，那麼最終還是要回歸到SQL最佳化。

latch：cache buffer chains是熱塊競爭，高併發模式下的高邏輯讀導致，那麼就需要對該SQL剖析，到底是執行計劃的哪部分消耗了較多的邏輯讀，然後進行最佳化。

分析執行計劃，根據cost成本消耗來看cost消耗最大的在執行計劃id 14到18這個nested loop outer上面，cost核算這部分執行計劃成本是8238K Cost。

分析執行計劃14到18：

• SQL語句中使用了hint的 /*+use_nl(a b) index(a IDX_PM_PROM_RANK_REWARD_3)*/，而IDX_PM_PROM_RANK_REWARD_3索引對應列是的a.REWARD_OFFERING_ID，這個地方是全索引掃描IDX_PM_PROM_RANK_REWARD_3然後回表。

• PM_PROM_RANK_REWARD A表驅動表返回的資料最佳化器估算是2954K Rows，然後做Nested loops連結，被驅動表PM_OFFERING b走唯一索引掃描然後回表，最佳化器估算這個Nested loops成本最後達到了8239K Cost，正式由於驅動表返回資料較多，導致被驅動表需要進行多次唯一索引範圍掃描然後回表，從而導致這個NL連線Cost較高。

首先這裡先明確一個要點：Oracle在處理where 條件A or 條件B and 條件C是選擇條件A or （條件B and 條件C)的處理方式。

那麼這裡的PM_PROM_RANK_REWARD a表為什麼會估算返回2945K了，這個地方是因為紅色部分字型的where條件：

對於處理條件A：

IDX_PM_PROM_RANK_REWARD_3的全索引掃描後回表，由於A表沒有其他where條件，只能返回2954K Rows，也就造成了Nested loop模式下被驅動表由於掃描次數較多消耗較多的邏輯讀，從而出現熱塊爭用latch：cache buffer chains等待（latch free部分先沒有關注，應該先解決大比例的latch：cache buffer chains）

四、核實業務邏輯

• 業務核實程式碼業務邏輯存在問題，對於b.EXPRIED_DATE時間欄位的處理條件是(b.EXPRIED_DATE IS NULL OR b.EXPRIED_DATE > SYSDATE) 

• 業務重新修改SQL後執行計劃、資源消耗迴歸合理範圍，latch：cache buffer chains熱塊等待現象消失，latch free等待也沒有再出現。

• 那麼這個SQL的正確業務邏輯和寫法應該是：

綜述：

• 當我們遇見實時效能問題時，我們可以直接去查詢系統的動態效能檢視，找到相關的等待事件、SQL_ID等，然後對相關SQL進行分析。

• SQL程式碼要嚴格稽核，儘量簡潔化，而且也要儘可能的少用hint，除非對該SQL的業務邏輯非常清晰，因為如果SQL出現變化，而原先的hint可能就不一定適用這個SQL，這個case中如果沒有NL連線的hint，則即使SQL寫法出現問題也不會出現熱塊爭用。

恩墨學院隸屬於雲和恩墨（北京）資訊科技有限公司，致力於提供專業高水準的與大資料培訓服務，挖掘培養大資料與資料庫人才。恩墨學院提供包括個人實戰技能培訓、個人認證培訓、企業內訓在內的全方位大資料和資料庫技術培訓。ACE級別超強師資，配備專業實驗室，沉浸式學習與訓練，專業實驗室、配備專業助教指導訓練。能迅速融入專家圈子，業內資源豐富，迅速積累職場人脈。課程包括：班、Oracle 、Oracle OCP考試等。