Pavan DBA's Blog

I have explained about temp segments residing in permanent tablespace in my earlier post https://pavandba.com/2013/03/20/temporary-segments-in-permanent-tablespace/
Today I thought to give some details about “How sorting takes place”.
First of all what statements will cause sorting in the database? I have seen so many DBA’s assuming ORDER BY or GROUP BY clauses alone will cause sorting. This is not correct. Sorting will take place for many statements and some of them are as below
1. Index creation / rebuild
2. using ORDER BY or GROUP BY clauses in SELECT statements
3. SELECT statement with DISTINCT keyword (oracle will do sorting to avoid duplicates)
4. UNION, INTERSECTION or MINUS operations
5. sort-Merge joins
6. Table Analyze (gather stats)
7. CREATE TABLE AS SELECT
8. CREATE PRIMARY KEY constraint or enable or disable that constraint etc

So, whenever we perform above operations in the database, oracle will allocate memory from RAM. We already know that the size of that memory allocated is dependent on parameter SORT_AREA_SIZE.
When using 9i or above versions, we don’t use this parameter rather we use PGA_AGGREGATE_TARGET which automates memory allocation to SORT AREA. That means SORT AREA will reside in PGA if it is a dedicated server architecture, and will reside in user golbal area (UGA) if we use shared server architecture (Multi-threaded server). This UGA is part of shared pool.

If any sorting operation completes within this SORT AREA, then we call it as IN-MEMORY SORT. This is possible when the size of data which needs to be sorted is <= SORT AREA SIZE. For example, if sort area size is 10MB and you are sorting 8MB of data, then it can be completed within PGA.
But what happens if data size is more than sort area size?
To understand, I am assuming that we have a sort area of 20MB and the data which I need to sort is 50MB.

In this case, Oracle will first picks up 20MB of data (out of 50MB) and will sort it in PGA and will send that to temporary tablespace (this is a disk I/O because tempfile will exist on disk). Then for second term, it will take another 20MB and will do the same. In third attempt, it will take final 10MB and will send to temp tablespace.
So, to sort 50MB of data, oracle will perform 3 I/O’s (frankly, there will be more than 3 I/O’s, but just for convinience, I am restricting it to 3). We know that I/O is a bad and costly operation and it can cause performance degradation.
With this we can understand that having right size for SORT AREA will avoid performance issues caused by sorting.

HAPPY LEARNING 🙂