Buffer Sort: Reason and Problems

Buffer sort uses the buffering mechanism of a traditional sort, but it does not do the sort itself.

The system simply buffers the data, in the UGA or PGA, to avoid multiple table scans against real data blocks. The whole sort mechanism is reused, including the swap to disk (TEMP tablespace) when not enough sort area memory is available, but without sorting the data.

So it is similar to temporary table but difference is temporary table gets stored in SGA thus buffer cache; so it introduces latching delay. Now comes the reason of buffer sort:-

1. If sql query has sub-query, then the sub-query result will be buffered in PGA using buffer sort. e.g. select * from A where A.c1 in (select c1 from B). The result of select c1 from B will be buffer sorted.
   
2. If sql query get transformed specially in case of star transformation, thus sub-query comes
   into picture and you will see buffer sort.
   
3. If abnormal join takes place, then you will see buffer sort. An example of this abnormal join is joining of two table where parallel degree of one table is one and that of another table is high then buffer sort appears. The less degree table is buffered then joined with high degree table. This is more discussed in BUFFER SORT and Parallel query. In this case it cause long execution of query. Hence it should be removed.

For case 1 & 2, it is unavoidable in most of the case if your query is OK. In first two cases, the main problem of buffer sort is it uses sort_area of PGA. As usual when PGA is filled up it takes place in temp tablespace. In first case, you can remove it by using result_cache hints to save sort_area. E.g. select * from A where A.c1 in (select /*+result_cache*/ c1 from B). Alternatively, if you want to increase sort_area_size, you can set the following
two parameters -

alter session set sort_area_size=100000;

alter session set workarea_size_policy = 'MANUAL';