Is sorting happens in the DB Buffer cache or PGA in Oracle?

Question

When the data is read from the discs or buffer cache and a sort is needed, is this sort done in the buffer cache(SGA) or PGA? If it is done in both, what is the difference between these sorts? And when & why it is done in the buffer cache or PGA? Thanks in advance

Answer 1

Sorting is done in the SQL work areas, which are mostly stored in the PGA. (Here's a guide to tuning the work areas; see also the SORT_AREA_SIZE parameter.)

The buffer cache (tuning guide) only really holds copies of data on disk (or data waiting to be written to disk) - no processing or work is done on the data in the buffer cache. It wouldn't be a very good cache if the data in it could be altered.

The main distinction in sorting behavior is whether you do it on-the-fly or beforehand (by creating an index - also done in PGA). If your data is already sorted, Oracle might read the index into the buffer cache - I'm not clear on that part - but the query will obviously be much faster.