關於查詢排序的空間使用問題

在9i之前的版本

    針對每個session,排序首先會使用sort_area_size ，如果不足則會使用暫存資料表空間。但這裡面又到底是怎麼一個過程呢？下面闡述一下，也許對大家有用處（如果有什麼不清楚或者不恰當的地方歡迎大家探討）

    假設sort_area_size = 100k,正好能盛下100條記錄進行排序,當排序記錄小於等於100條，ok,所有排序在記憶體中進行，很快,但若超過100條，則會使用暫存資料表空間（利用磁碟進行）我們選取一個臨界值來說明，假設需要排序的記錄有10010條,這個時候我們進行的排序會分為101組進行,每讀100條進行一次小組排序，然後寫入磁碟，第101組只有10條，排序後也寫入磁碟.

    這是進行第二次排序，這次排序將在前100小組裡面各抽取一條進行排序。《按照我個人的猜測，應該是排好後每寫入一條入磁碟則將該記錄所在小組重新抽取一條出來進行排序（這時是有序記錄組裡面所以很快）》。當這個過程完成後，這時所需要的磁碟空間大約為 實際記錄儲存空間的2倍（這也是多數書上提到的排序空間大約是記錄空間的2倍的原因）,由於還剩下10條記錄，於是這10條記錄需要跟前面排序的10000條記錄進行排序合并，這個代價也是相當大的！

    所以，我們通常推薦，假如你需要排序的記錄最大為100萬條，則sort_area_size最小要能裝下1000條，否則如上面的例子，那多餘的10條，僅僅10條將會帶來巨大的代價！如果，設定的極度不合理的情況下，排序記錄達到了 sort_area_size所能容納的三次方以上，比如上面例子中排序需要100萬記錄,那麼同樣的，重複這個過程，當每一萬條記錄如上排序後，再如上從這100小組（每組10000條記錄）各抽一條進行排序…… 在這個過程中，磁碟的消耗和時間的代價大家都應該有個感性認識了。所以，我們建議： sprt_area_size 所能容納記錄數至少大於排序記錄數的 平方根。

    從 9i開始oracle用  PGA_AGGREGATE_TARGET  來取代了 關於 session 層級的一些設定，也就是說，當 workarea_size_policy  = AUTO 的時候，所有 *_area_size 都不在session層級設定了（僅僅針對dedicated模式的session而不針對 shared 模式），這些 session 共用資料庫instance層級共同設定的  PGA_AGGREGATE_TARGET 

show parameters area

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size               integer     1048576
create_bitmap_area_size              integer     8388608
hash_area_size                       integer     248576
sort_area_retained_size              integer     0
sort_area_size                       integer     124288
workarea_size_policy                 string      AUTO

並且各 session 使用 PGA_AGGREGATE_TARGET 遵循一個規則，那就是每個 session 使用空間不能超過 PGA_AGGREGATE_TARGET * 5%  ，若超過則使用 暫存資料表空間