关于查询排序的空间使用问题

在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%  ，若超过则使用 临时表空间