一次臨時表空間大量佔用問題的處理

2008081036發表於2015-07-22

文章來源：熊軍的個人部落格【老熊的三分地-Oracle及資料恢復】
Address: 

一個電信運營商客戶的核心交易系統，臨時表空間大量被佔用，臨時表空間被撐到了600GB。這樣的問題複雜嗎？取決於很多因素，不過今天所要講的案例，並不複雜，如果我們對臨時表空間在何種情況下使用有足夠了解。

首先，我們要去檢查是什麼會話佔用了臨時表空間，具體佔用了多少，臨時段的具體型別是什麼。正如我們要想知道這個月的花費過大，去分析原因時就要去看是哪些開銷過大、開銷了多少金額、開銷的用途等。

這個步驟比較簡單，查詢v$sort_usage就可以了：

view plaincopy to clipboardprint?
  1. select * from   
  2. (select username,session_addr,sql_id,contents,segtype,blocks*8/1024/1024 gb   
  3. from v$sort_usage order by blocks desc)   
  4. where rownum<=200;  
  5.   
  6. USERNAME    SESSION_ADDR     SQL_ID        CONTENTS  SEGTYPE            GB  
  7. ----------  ---------------- ------------- --------- --------- -----------  
  8. XXXX        0700002949BCD8A0 291nk7db4bwdh TEMPORARY SORT      .9677734375  
  9. XXXX        070000294BD99628 291nk7db4bwdh TEMPORARY SORT      .9677734375  
  10. XXXX        070000294CD10480 291nk7db4bwdh TEMPORARY SORT      .9677734375  
  11. XXXX        070000294DD1AC88 291nk7db4bwdh TEMPORARY SORT      .9677734375  
  12. XXXX        070000294CD68D70 291nk7db4bwdh TEMPORARY SORT      .9677734375  
  13. XXXX        070000294DBDF760 291nk7db4bwdh TEMPORARY SORT      .9677734375  
  14. XXXX        070000294EDB5D10 291nk7db4bwdh TEMPORARY SORT      .9677734375  
  15. XXXX        070000294FD7D818 291nk7db4bwdh TEMPORARY SORT      .9677734375  
  16. ...結果較多，忽略部分輸出...  

SQL_ID都是一樣的，那這個SQL是否有其特殊性呢？SEGTYPE為SORT表明這個臨時段是“排序段”，用於SQL排序，大小居然也是一樣，會話佔用的臨時段大小將近1GB，幾百個會話加在一起，想不讓臨時表空間不撐大都難。

看看這個相同的SQL ID代表的SQL是什麼：

view plaincopy to clipboardprint?
  1. SQL> @sqlbyid 291nk7db4bwdh  
  2.   
  3. SQL_FULLTEXT  
  4. --------------------------------------------------------------------------------------------------------------  
  5.  SELECT  A.LLEVEL,  A.LMODE  FROM TABLE_XXX A  WHERE A.SERVICE_NAME = :SERVICE_NAME AND STATE='Y'  

很明顯，這是一條非常簡單的SQL，沒有ORDER BY ，也沒有GROUP BY、UNION、DISTINCT等需要排序的，TABLE_XXX是一張普通的表，而不是檢視。出現了什麼問題？會不會是v$sort_usage的SQL_ID列有錯誤？我們檢視其中一個會話正在執行的SQL：

view plaincopy to clipboardprint?
  1. select sid,prev_sql_id, sql_id from v$session where saddr='070000294AC0D050';  
  2.   
  3.         SID PREV_SQL_ID   SQL_ID  
  4. ----------- ------------- -------------  
  5.        3163 291nk7db4bwdh  

v$sort_usage中看到某個會話當前沒有執行任何SQL，v$sort_usage中的SQL_ID是該會話前一條執行的SQL。為什麼這裡顯示的是會話前一條執行的SQL，關於這個問題後面再詳述，但至少有一點是可以判斷的：如果大量的臨時段都是由會話當前正在執行的SQL所產生的，那說明同時有幾百個會話在執行需要大量臨時空間的SQL，那系統早就崩潰了。所以這些臨時表空間的佔用不應該是由當前在執行的SQL所產生的，至少大部分不是。

大部分人的一個錯誤觀點是，臨時表空間中當前佔用的空間是由會話當前正在執行的SQL所產生的。上面的一個簡單的分析判斷，情況不應該是這樣。我們可以基於查詢類SQL的執行過程來分析：

  1. 解析SQL語句(Parse)，生成一個遊標(Open Cursor)。
  2. 執行SQL語句(Execute)，嚴格說就是執行新產生的遊標。
  3. 在遊標中取資料(Fetch)。
  4. 關閉遊標(Close Cursor)。

關鍵在第3步。大家都知道取資料有一個array size的概念，表示一次從遊標中取多少條資料，這是一個迴圈的過程。如果SQL查詢得到的資料有1000條，每次取100條，則需要取10次。對於Fetch Cursor，有兩點：

  1. 一個遊標，或者說一條SQL語句，並不要求客戶端把所有資料取完，只取了一部分資料就關閉遊標也是可以的。
  2. 只要還沒有關閉遊標，資料庫就要維護該遊標的狀態，如果是排序的SQL，也需要維持該SQL已經排好序的資料。

很顯然，從上述第2點可以知道，如果一條SQL使用了臨時段來排序，在SQL對應的遊標沒關閉的情況下，Oracle資料庫不會去釋放臨時段，因為對於Oracle資料庫來說，它不會知道客戶端是否還要繼續取遊標的資料。

基於這樣的分析，我們只需要隨便選擇一個佔用了接近1GB的會話，查詢v$open_cursor，檢視其開啟的遊標中是否有大資料量排序的SQL：

view plaincopy to clipboardprint?
  1. SQL> select sql_id,sorts,rows_processed/executions from v$sql  
  2.   2  where parsing_schema_name='ACCT' and executions>0 and sorts>0  
  3.   3  and sql_id in (select sql_id from v$open_cursor where sid=4505)  
  4.   4  order by 3;  
  5.     
  6.   SQL_ID              SORTS ROWS_PROCESSED/EXECUTIONS  
  7. ------------- ----------- -------------------------  
  8. ...省略部分輸出結果...  
  9. 86vp997jbz7s6       63283                       593  
  10. cfpdpb526ad43         592               35859.79899  
  11. cfpdpb526ad43         188               55893.61702  
  12. cfpdpb526ad43         443                     71000  

最後三個遊標，實際上都是同一條SQL語句，排序的資料量最大，我們來看看這條SQL是什麼：

view plaincopy to clipboardprint?
  1. @sqlbyid cfpdpb526ad43  
  2.   
  3. SQL_FULLTEXT  
  4. ---------------------------------------------------------------------------------------------------  
  5. select ... from  c, b, a, d, e where ... order by d.billing_cycle_id desc,e.offer_name,a.acc_name  

基於為客戶保密的原因，SQL做了處理，能知道這條SQL的確是排了序就行，不過在SQL中看不出來的是，這條SQL沒有任何實質性的能夠過濾大量資料的條件。那麼我們count(*)這條SQL語句看看：

view plaincopy to clipboardprint?
  1. COUNT(*)  
  2. --------  
  3. 12122698  

出來的結果居然有1200多萬條資料，一個前臺應用，不知道取1200多萬條資料幹嘛。但是從rows_processed/executions只有幾萬的結果來看，應用在取了幾萬條資料之後，由於某些原因（最大的可能就是不能再處理更多的資料），不再繼續取資料，但是遊標也一直沒有關閉。

比較容易就能進行演示sort by時臨時表空間的佔用。

view plaincopy to clipboardprint?
  1. 根據dba_objects建一個測試表T1，使其資料量達到2000萬行。  
  2.  select count(*) from t1;  
  3.   
  4.    COUNT(*)  
  5. -----------  
  6.    20171200  
  7.    
  8. 然後將SQL工作區設定為手動模式，設定sort記憶體大小限制為200M：  
  9.  alter session set workarea_size_policy=manual;  
  10.  alter session set sort_area_size=209715200;  
  11.   
  12. 查詢得到當前的會話sid：  
  13.  select sid from v$mystat where rownum< =1;  
  14.   
  15.         SID  
  16. -----------  
  17.        2111  
  18.   
  19. 執行這下面的程式碼：  
  20.  declare  
  21.   2     v_object_name varchar2(100);  
  22.   3     v_dummy varchar2(100);  
  23.   4  begin  
  24.   5    for rec in (select * from t1 order by object_id,object_name) loop  
  25.   6       select object_type into v_dummy from t1 where rownum<=1;  
  26.   7       select object_name into v_object_name from dba_objects where object_id=rec.object_id;  
  27.   8       dbms_lock.sleep(60*10);  
  28.   9       exit;  
  29.  10    end loop;  
  30.  11  end;  
  31.  12  /  
  32. 這段程式碼會開啟一個遊標，對2000萬的資料量進行排序，然後在迴圈中只取一條資料，然後就進入sleep。在另一個視窗中監控到2111這個會話的event變成了PL/SQL lock timer，就去查詢v$sort_usage:  
  33. select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, contents,segtype,blocks*8/1024/1024 gb   
  34.   2  from v$sort_usage a,v$session b   
  35.   3  where a.session_addr=b.saddr  
  36.   4  and b.sid=2111;  
  37.   
  38. SORT_SQL_ID   SQL_ID        PREV_SQL_ID   CONTENTS  SEGTYPE            GB  
  39. ------------- ------------- ------------- --------- --------- -----------  
  40. fabh24prgk2sj bhzf316mdc07w fabh24prgk2sj TEMPORARY SORT      1.444824219  
  41. 可以看到v$sort_usage中的SQL_ID（即上述結果中SORT_SQL_ID）與v$session中的pre_sql_id一致，這條SQL是：  
  42.   
  43. @sqlbyid fabh24prgk2sj  
  44. SQL_FULLTEXT  
  45. --------------------------------------------------------  
  46. SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID=:B1  
  47.   
  48. 而實際上當前正在執行的SQL是：  
  49.  @sqlbyid bhzf316mdc07w  
  50.   
  51. SQL_FULLTEXT  
  52. ---------------------------------------------------------------------------  
  53. declare  
  54.    v_object_name varchar2(100);  
  55.    v_dummy varchar2(100);  
  56. begin  
  57.   for rec in (select * from t1 order by object_id,object_name) loop  
  58.      select object_type into v_dummy from t1 where rownum<=1;  
  59.      select object_name into v_object_name from dba_objects where object_id=rec.object_id;  
  60.      dbms_lock.sleep(60*10);  
  61.      exit;  
  62.   end loop;  
  63. end;  

問題分析到這裡，很明顯確認的是，應用存在問題，也許是業務邏輯問題；也許是根據前臺選擇的條件拼接的SQL，但是沒有任何條件時就查詢了所有資料。接下來就是找來開發人員，至於後面的事就跟這個主題沒有太大關係。我們可以根據這個案例來進一步展開，去探尋臨時表空間的更多知識點。

這裡要展開的第1點是，v$sort_usage中的sql_id是不是會話正在執行的SQL，我們去看看檢視fixed_View_definition就知道了：

view plaincopy to clipboardprint?
  1. select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value,  
  2. prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'), decode(ktssosegt, 1,  
  3. 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno,  
  4. ktssobno, ktssoexts, ktssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr  
  5. and ktssosno = v$session.serial#  

原來在v$sort_usage的定義中，就明確地說明了SQL_ID列是v$session中的prev_sql_id列，而不是當前的SQL。至於為什麼這樣定義，老實說，現在還不知道。

不過從11.2.0.2這個版本開始，v$sort_usage的基表x$ktsso中增加了一個欄位ktssosqlid，表示該臨時段真正關聯的SQL，以上述的測試結果為例，查詢這個基表的結果如下：

view plaincopy to clipboardprint?
  1. select ktssosqlid from x$ktsso, v$session where ktssoses = v$session.saddr  
  2.   2  and ktssosno = v$session.serial#  
  3.   3  and v$session.sid=2111;  
  4.   
  5. KTSSOSQLID  
  6. -------------  
  7. 60t6fmjsw6v8y  
  8.   
  9. @sqlbyid 60t6fmjsw6v8y  
  10.   
  11. SQL_FULLTEXT  
  12. ---------------------------------------------------------------------------  
  13. SELECT * FROM T1 ORDER BY OBJECT_ID,OBJECT_NAME  

可以看到的是我們查詢到了真正產生臨時段的SQL。

一直以來，v$sort_usage中的SQL_ID誤導了很多人。所幸的是Oracle從11.2.0.2開始進行了彌補，MOS中有文件：

Bug 17834663 - Include SQL ID for statement that created a temporary segment in GV$SORT_USAGE (文件 ID 17834663.8)
In previous versions, it was not possible to identify the SQL ID
of the statement that created a given temporary segment in
eg. (G)V$SORT_USAGE.

@ Via the fix for bug:8806817 we added the SQL ID to the X$KTSSO
@ table (ktssosqlid), but it was not exposed in the GV$SORT_USAGE
@ view until now.

The SQL ID of the statement is in column SQL_ID_TEMPSEG

Note that this fix cannot be provided as an interim patch.

我們改良一下v$sort_usage，使用如下的查詢來代替：

view plaincopy to clipboardprint?
  1. select k.inst_id "INST_ID",  
  2.        ktssoses "SADDR",  
  3.        sid,  
  4.        ktssosno "SERIAL#",  
  5.        username "USERNAME",  
  6.        osuser "OSUSER",   
  7.        ktssosqlid "SQL_ID",  
  8.        ktssotsn "TABLESPACE",  
  9.        decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",  
  10.        --注意在12c的v$sort_usage定義中TABLESPACE和CONTENTS已經發生變化了。  
  11.        decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',   
  12.           5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",  
  13.        ktssofno "SEGFILE#",  
  14.        ktssobno "SEGBLK#",  
  15.        ktssoexts "EXTENTS",  
  16.        ktssoblks "BLOCKS",  
  17.        round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",  
  18.        ktssorfno "SEGRFNO#"  
  19. from x$ktsso k, v$session s,   
  20.      (select value from v$parameter where name='db_block_size') p   
  21. where ktssoses = s.saddr  
  22.   and ktssosno = s.serial#;  

要展開的第2點是，v$sort_usage中的SEGTYPE列的不同的值各有什麼意義：

  1. SORT：SQL排序使用的臨時段，包括order by、group by、union、distinct、視窗函式(window function)、建索引等產生的排序。
  2. DATA：臨時表(Global Temporary Table)儲存資料使有的段。
  3. INDEX：臨時表上建的索引使用的段。
  4. HASH：hash演算法，如hash連線所使用的臨時段。
  5. LOB_DATA和LOB_INDEX：臨時LOB使用的臨時段。

根據上述的段型別，大體可以分為三類佔用：

  1. SQL語句排序、HASH JOIN佔用
  2. 臨時表佔用
  3. 臨時LOB物件佔用

臨時表空間的異常佔用，一種緩步增長的，另一種情況：一下撐滿的通常是一個極大資料量的排序或極大的索引的建立。緩步增長的情況，跟系統的記憶體被逐漸佔用類似，存在“洩露”。比如排序的SQL遊標沒有關閉，比如本文的案例；比如會話級臨時表產生了資料後一直沒有清除；臨時LOB物件沒有清理或洩露。前兩種比較好去分析處理，但是臨時LOB的洩露問題就複雜很多。

來看一個測試：

view plaincopy to clipboardprint?
  1.  select sid from v$mystat where rownum<=1;  
  2.   
  3.         SID  
  4. -----------  
  5.        1773  
  6.  declare  
  7.   2    v_lob clob;  
  8.   3  begin  
  9.   4    dbms_lob.createtemporary(v_lob,true);  
  10.   5    dbms_lob.writeappend(v_lob,1000,lpad('a',1000,'a'));  
  11.   6  end;  
  12.   7  /  

上述的程式碼執行完之後，在另一個視窗中，我們查詢v$sort_usage：

view plaincopy to clipboardprint?
  1. select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, contents,segtype,blocks*8/1024/1024 gb   
  2.   2  from v$sort_usage a,v$session b   
  3.   3  where a.session_addr=b.saddr  
  4.   4  and b.sid=1773;  
  5.   
  6. SORT_SQL_ID   SQL_ID        PREV_SQL_ID   CONTENTS  SEGTYPE            GB  
  7. ------------- ------------- ------------- --------- --------- -----------  
  8. 9babjv8yq8ru3               9babjv8yq8ru3 TEMPORARY LOB_DATA  .0004882813  
  9.   
  10. @sqlbyid 9babjv8yq8ru3  
  11.   
  12. SQL_FULLTEXT  
  13. ---------------------------------------------------------------------------  
  14. BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;  

可以看到，這個會話已經產生了型別為LOB_DA他的臨時段。雖然SQL程式碼已經執行完成，會話已經處於空閒狀態，但是臨時段仍然存在著。

Oracle中的LOB變數，類似於C語句中的指標，或者類似於JAVA程式碼中的資料庫連線Connection，是需要釋放的。上述有問題的程式碼，缺少了釋放LOB的程式碼：dbms_log.freetemporary(v_lob)。好在對於這種情況，Oracle提供了一個補救措施，就是設定60025事件可以自動清理掉不活動的LOB，只需要在引數檔案中加上event='60025 trace name context forever'。

在Oracle資料庫中，xmltype型別內部也實際上是LOB型別，xmltype型別的資料操作可能會產生較多的LOB臨時段。lob型別的欄位上的更改操作，比如lob拼接等，同樣會產生LOB臨時段。如果在v$sort_usage中發現大量的LOB型別的臨時段，那麼通常是由於程式碼存在問題，沒有釋放LOB，或者是由於Oracle本身的BUG。在MOS上，如果以lob temporary關鍵字搜尋，會發現相當多的關於lob臨時段的洩露或臨時段沒有釋放相關的文件。

最後，不管是什麼情況導致的臨時表空間被過多佔用，通常重啟應用能夠釋放掉臨時段，因為會話退出後，相對應的臨時段就會被釋放。看來，“重啟”大法在這種情況下就很有用。

--The END.

Address: 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25465866/viewspace-1744417/，如需轉載，請註明出處，否則將追究法律責任。

相關文章