搬運工：temp表空間被過多佔用處理方法

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：

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是什麼：

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語句看看：

1. COUNT(*)  
2. --------  
3. 12122698  

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

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

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就知道了：

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，以上述的測試結果為例，查詢這個基表的結果如下：

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，使用如下的查詢來代替：

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的洩露問題就複雜很多。

來看一個測試：

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：

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臨時段的洩露或臨時段沒有釋放相關的文件。

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

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

0

0

分享到：

上一篇： Clean WRH$_ACTIVE_SESSION_HISTORY in SYSAUX

下一篇： 好用的metalink文件

請登入後發表評論 登入

全部評論

kisslfcr

註冊時間：2015-01-12

• 博文量
  246
• 訪問量
  402078

最新文章

• greenplum 4.3.33安裝
• 擴充和縮小LV
• NFS導致的目標端檔案系統不可讀
• Incorrect MEMORY_MAX_TARGET (> Available RAM) Can Lead To Database Hangs
• 如何追蹤產生大量REDO的來源
• DATAGUARD監控，保護和自動修復最佳實踐
• impdp導致主鍵索引的變化
• Oracle官方推薦的資料遷移方式評估
• grep的或操作
• awk取文字中兩個字串之間的部分

支援我們 作者招募 使用者協議 FAQ Contact Us

北京盛拓優訊資訊科技有限公司. 版權所有  京ICP備16024965號-8 北京市公安局海淀分局網監中心備案編號：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人舉報專區

廣播電視節目製作經營許可證(京) 字第1234號 中國網際網路協會會員