Pavan DBA's Blog

The DBA Knowledge Store

How and When SORTING takes palce?

Posted by Pavan DBA on March 21, 2013


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 🙂

Advertisements

7 Responses to “How and When SORTING takes palce?”

  1. mahesh reddy said

    if we work in hospital domain as oracle dba it is difficult to get a job in MNC companies

  2. khaleel ahmed said

    Very Nice explanation.

    Thanks & Regards
    Khaleel

  3. Harry said

    Excellent explanation Pavan sir 🙂

  4. Prashe said

    Sir,

    Thanks for the information… abt sort_area_size…….

    On Thu, Mar 21, 2013 at 1:53 PM, Pavan DBA’s Blog wrote:

    > ** > Pavan DBA posted: “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 s”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: