Help on Sort_Area_Size and Direct_Sort

From: aa <aau_at_interlog.com>
Date: Mon, 1 Jun 1998 23:15:32 -0400
Message-ID: <6kvngn$opq$1@news.interlog.com>

I am trying to understand the implications of Sort Area Size, Sort Area Retained Size and Direct Sort Writes. I have the following definitions from three different books.

Book 1

Sort_Area_Size
The maximum amount of space in bytes that a user process has avilable to perform a sort.

Sort_Area_Retained_Size
The minimum amount of space in bytes that a user process will ever have available.

Book 2

When a sort occurs, PGA memory is used up to the size of the Sort_Area_Retained_Size parameter and then it is placed into the user temporary tablespace until the size of the Sort_Area_Size is reached.

Book 3

Sort_Area_Size parameter specifies the maximum amount of memory, in bytes, the user has available for sorting. Sort_Area_Retained_Size sets the maximum amount of memory, in bytes, that will be used for an in-memory sort.

I have ran a sort query with varying Sort_Area_Size and look at the sorts (disk/memory) in V$Sesstat. So I do know that if the Sort_Area_Size is below a certain threshold it'll sort in disk and write to the TEMP segment.

Questions:
1. Which of this definition best describes the parameters Sort_Area_Size/Retained_size?

2. What is an in-memory sort as described in Book 3?

3. What is Sort Extent Pool and where is it located?

4. I know Sort_Direct_Writes bypasses the SGA buffers by assigning it's own sort buffers. When do I use it? Do I perform Direct_Writes when I have lots of RAM or limited RAM? Is it true that Direct_write is only benificial when you are sorting to a tempory segment? Are Direct_writes and in-memory sorts mutually exclusive? Please clarify. Received on Mon Jun 01 1998 - 22:15:32 CDT

Help on Sort_Area_Size and Direct_Sort_Writes