Re: Help on Sort_Area_Size and Direct_Sort_Writes

On Mon, 1 Jun 1998 23:15:32 -0400, "aa" <aau_at_interlog.com> wrote:

>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.
>

I'm making this up as I go along but what it looks like is:

During a sort operation, oracle fetches data into memory of size sort_area_retained size. If the entire sort data set (it is not clear if this refers to all the rows or just the sort keys) fits into this area of memory, a normal (probably quick sort ) in-memory sort occurs.

If a single query requires multiple sort areas (unusual), then many sort_area_retained size blocks of memory may be allocated.

The total memory allocated to sorting will not exceed sort_area_size.

If the data fetched exceeds the sort_area_retained_size, data is fetched until sort_area_size is reached and sorted in memory. This sorted subset of the total result set is written to a temporary segment and memory area cleared to sort the next batch. At the end of the sort, the (sorted) temporary segments which were written to disk are merged.

If sort_direct_writes is set to true, the temporary segments are written to disk via sort_write_buffers (default 1) I/O buffers of size sort_write_buffer_size (default 32k). This is interesting because it implies that the sorted buffer previously populated is not written to disk as-is.

If sort_direct_writes is set to false, writes take place via the normal mechanism of updating data blocks in the SGA which are eventually written to disk by the database writer process DBWR.

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

book 3

>2. What is an in-memory sort as described in Book 3?
A sort where all the data to sorted fits in a memory block of size sort_area_retained_size.

>3. What is Sort Extent Pool and where is it located?
Don't know. Maybe the pool of sort extents written in a temporary tablespace.

>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.

The oracle server reference says to use direct writes when memory is plentiful. Received on Wed Jun 03 1998 - 04:51:20 CDT