Oracle數據庫排序基礎

Oracle會話首先在內存中進行排序，當Oracle需要存儲數據到臨時表或者為哈西排序建立哈希表時，并且也會首先在內存中進行操作，雖然這兩個操作不需要排序操作，但是它們在Oracle中的處理方式是相同的。

如果操作使用內存超過了閾值，Oracle會將操作分為多個較小的操作以使每個可以在內存中操作。部分結果將會被寫入磁盤的臨時表空間，任何一個會話可以使用的內存數依賴于初始化參數的設置，如果workarea_size_policy為auto，則由pga_aggregate_target控制，否則由sort_area_size, hash_area_size，和bitmap_merge_area_size控制內存的使用。

當排序操作太大以至于不能在內存中執行時，Oracle將在臨時表空間中分配空間以執行操作。臨時段在臨時表空間中—也稱為“排序段”，sys擁有，而不是執行排序操作的用戶。通常每個表空間中只有一個排序段，因為多個會話可以共享排序段，用戶使用臨時表空間不需要在其上有quota，事實上會被Oracle忽略。

臨時表空間中只能包含臨時段，因此臨時段上的操作不會產生undo和redo，同時分配臨時段給用戶也不需要記錄在dd或位圖塊上。因為臨時表空間不會超過創建它的會話的生命周期。

一個SQL可以有多個排序操作，一個數據庫會話同時可以有多個活動的SQL，當到磁盤上的排序結果不再需要時，其在排序段中的塊會標記為不再使用并可以被分配給其他排序操作。

如果發生以下情況排序操作將會失敗：排序段中沒有不再使用的塊；臨時表空間中沒有空間可以供排序段分配額外的分區。這在大多數情況下會導致語句發生以下錯誤：“ORA-1652: unable to extend temp segment.”并記錄在實例的alert log中。

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

識別由于缺少臨時表空間失敗的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進行會話級跟蹤。

監控臨時表空間

可以在發生錯誤前實時監控數據庫中臨時表空間的使用情況，以避免出現錯誤。任何時候，Oracle都可以告訴dba數據庫中的臨時表空間，會話使用的排序空間，以及語句使用的排序空間。所有這些信息都可以通過v$得到。

臨時段

Oracle會在第一次執行磁盤排序時創建排序段，并且根據需要擴展，但是不會收縮。

SELECT A.tablespace_name tablespace,

D.mb_total,

SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts# = C.ts#

GROUP BY B.name, C.block_size) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

會話使用的排序空間

SELECT S.sid || ',' || S.serial# sid_serial,

S.username,

S.osuser,

P.spid,

S.module,

S.program,

SUM(T.blocks) * TBS.block_size / 1024 / 1024 mb_used,

T.tablespace,

COUNT(*) sort_ops

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid,

S.serial#,

S.username,

S.osuser,

P.spid,

S.module,

S.program,

TBS.block_size,

T.tablespace

ORDER BY sid_serial;

語句使用的臨時空間

SELECT S.sid || ',' || S.serial# sid_serial,

S.username,

T.blocks * TBS.block_size / 1024 / 1024 mb_used,

T.tablespace,

T.sqladdr address,

Q.hash_value,

Q.sql_text

FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = Q.address(+)

AND T.tablespace = TBS.tablespace_name

ORDER BY S.sid;