參考文件: <的研究與測試_20091130>>

臨時表空間的申請與釋放問題學習

1 臨時表空間的申請與釋放的理解

2 識別由於缺少臨時表空間失敗的SQL語句

3 監控臨時表空間

4 結論

5 Notes

臨時表空間有三種型別，這裡所討論的都是以Temporary tablespace為參考的，另外兩種情況不再深入學習，只要知道有這回事。

1 臨時表空間的申請與釋放的理解

資料庫剛剛startup 後，第一個需要進行disk sort的操作會在Temporary Tablespace上建立一個Temporay Segment. Temporary Segment 在Temporary tablespace 中也稱為Sort Segment. 由sys擁有，而不是執行排序操作的使用者。通常每個臨時表空間中只有一個排序段，因為多個會話可以共享排序段，使用者使用臨時表空間不需要在其上有quota，事實上會被Oracle忽略。當語句結束時，分配給這個sort segment並沒有被相應的回收，而是由其它會話共享這個sort segment. 只有shutdown時才被釋放。

All processes performing sorts reuse existing sort extents of the sort segment, rather than allocating a segment (and potentially many extents) for each sort. If an insufficient number of extents exist for the number of sorts currently in operation, then the required extents are added once for each instance startup. They are recycled thereafter.<<官方的說明>

如果發生以下情況排序操作將會失敗：

排序段中沒有不再使用的塊；

臨時表空間中沒有空間可以供排序段分配額外的分割槽。

這在大多數情況下會導致語句發生以下錯誤：

“ORA-1652: unable to extend temp segment.”並記錄在例項的alert log中。

不過需要注意的是ORA-1652並不全部指示臨時表空間問題，ALTER TABLE…MOVE也會發生該錯誤，如果目標表空間沒有足夠的空間容納移動的表空間。

2 識別由於缺少臨時表空間失敗的SQL語句

雖然Oracle logs ORA-1652錯誤到警告日誌中通知dba發生了空間問題，但是Oracle不會識別那條錯誤的語句。可以使用Oracle診斷事件跟蹤ORA-1652事件，該診斷事件的影響很小，僅在發生ORA-1652錯誤時才會寫入資訊。

ALTER SESSION SET EVENTS '1652 trace name errorstack';

在會話範圍內設定；

　　ALTER SYSTEM SET EVENTS '1652 trace name errorstack';

永久性設定：

　　ALTER SYSTEM SET EVENT = '1652 trace name errorstack' SCOPE = SPFILE;

還可以在其他會話內使用“oradebug event”進行跟蹤。

　　可以使用以下語句關閉：

　　ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID = '*';

　　ALTER SYSTEM SET EVENTS '1652 trace name context off';

ALTER SESSION SET EVENTS '1652 trace name context off';

　　如果一個SQL語句由於缺少臨時表空間失敗並且ORA-1652診斷事件已經啟用，那麼

　　Oracle伺服器程式將會在遇到錯誤時在user_dump_dest目錄的跟蹤檔案寫入錯誤資訊，並且警告日誌會指示出相關跟蹤檔案。如：

　　Tue Jan 2 17:21:14 2007

　　Errors in file

　　/u01/app/oracle/admin/rpkprod/udump/rpkprod_ora_10847.trc: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

跟蹤檔案中將包含類似如下的資訊：

　　Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

　　ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_2

　　System name: SunOS

　　Node name: rpk

　　Release: 5.8

　　Version: Generic_108528-27

　　Machine: sun4u

　　Instance name: rpkprod

　　Redo thread mounted by this instance: 1

　　Oracle process number: 18

　　Unix process pid: 10847, image: oracle@rpk (TNS V1-V3)

　　*** ACTION NAME:() 2007-01-02 17:21:14.871

　　*** MODULE NAME:(SQL*Plus) 2007-01-02 17:21:14.871

　　*** SERVICE NAME:(SYS$USERS) 2007-01-02 17:21:14.871

　　*** SESSION ID:(130.13512) 2007-01-02 17:21:14.871

　　*** 2007-01-02 17:21:14.871

　　ksedmp: internal or fatal error

　　ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

　　Current SQL statement for this session:

　　SELECT "A1"."INVOICE_ID", "A1"."INVOICE_NUMBER", "A1"."INVOICE_DAT

　　E", "A1"."CUSTOMER_ID", "A1"."CUSTOMER_NAME", "A1"."INVOICE_AMOUNT",

　　"A1"."PAYMENT_TERMS", "A1"."OPEN_STATUS", "A1"."GL_DATE", "A1"."ITE

　　M_COUNT", "A1"."PAYMENTS_TOTAL"

　　FROM "INVOICE_SUMMARY_VIEW" "A1"

　　ORDER BY "A1"."CUSTOMER_NAME", "A1"."INVOICE_NUMBER"

----- Call Stack Trace -----

雖然使用這種方法可以得到相當詳細的資訊，但是需要注意的是，這種方法捕獲到的語句並不一定是問題的根源，因為有可能前一個語句消耗了99.9%臨時空間，而第二個語句被捕獲到跟蹤檔案中。跟蹤檔案同時還會包含如呼叫棧跟蹤和二進位制棧dump，該資訊通常沒有價值，除非想要了解Oracle內部。通常不應該在例項級別設定該診斷事件。如果經常在批處理期間遇到該錯誤，可以在批處理開始設定alter session進行會話級跟蹤。

3 監控臨時表空間

檢視臨時表空間的使用情況(會重用,只有不夠時才又重新請求分配)V$TEMP_SPACE_HEADER

select TABLESPACE_NAME,file_id,

(bytes_used+bytes_free)/1024/1024 "size_total(mb)",

bytes_used/1024/1024 "size_allocated(mb)",

bytes_free/1024/1024 "size_unallocated(mb)"

from V$TEMP_SPACE_HEADER;

檢視正在使用的sort_segment,V$SORT_SEGMENT

select tablespace_name, CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS

from v$sort_segment;

檢視哪個使用者的哪個語句在使用sort_segment,V$SORT_USAGE=v$tempseg_usage

select a.username, a.SESSION_NUM,b.sql_text, a.TABLESPACE

from v$tempseg_usage a, v$sqlarea b

where a.SQLHASH = b.hash_value;

4 結論

關於臨時表空間的重用與共享：

假定使用者test使用temp作為臨時表空間，臨時表空間大小2G，當test使用者發起的一次操作需要的臨時表空間超過2G時(當然這裡可能沒到2G就報錯了)，就會報類似錯誤。說明臨時表空間不夠用。

ORA-01652: unable to extend temp segment by 2048 in tablespace TEMP_TEST

如不超過2G，則沒問題。下一次的操作又重新使用所有空間。

而透過

select TABLESPACE_NAME,file_id, bytes_used/1024/1024 "size used(mb)", bytes_free/1024/1024 "size free(mb)" from V$TEMP_SPACE_HEADER;

看到的是已分配的空間，但不是已使用的空間。

假定多個使用者共用temp作為臨時表空間，當幾個使用者同時發起排序動作，則一旦所需的臨時表空間總和超過2G，同樣報

ORA-01652: unable to extend temp segment by 2048 in tablespace TEMP_TEST

.

所有的會話共享sort segment裡面的extents，一個會話從sort segment中得到合適的extent個數，當會話完成後，分配給會話的的extents被回收到sort segment.

不回收的是sort segment,而回收的是extents.（這裡理解可能有問題）

5 Notes

1 Temporary segment 與 sort segment是包含的關係。

2 做測試時要有一個乾淨的環境，不能與其它應用共享，影響結果

臨時表空間的申請與釋放問題學習_20091203

相關文章